Only two options to import Excel files?

[PW]

Convert them to CSV in Excel, then use TransferText (which does not
work correctly and also doesn't accept XLS/Excel files directly) or
create a link to an Excel XLS workbook and do an Append Query (as some
have suggested here)??

I find that hard to believe as Access is part of MS Office and Visual
FoxPro is not. I split my time using both and I can do a simple copy
command to XLS in VFP or an Append command to DBF or use Office
Automation to build workbooks using VFP with ease. I've been doing
this for years.

TransferText is incorrectly transposing some information as blank
fields (seems like a crap shoot). I don't know what the CSV (wish it
could be an XLS) file is until I return the file name and path from a
user dialog box that selects the file.

-pw

[lyle fairfield]

Re: Only two options to import Excel files?

Is there a question here?

On May 19, 11:18 pm, PW <paulremove_wil liamson...@remo vehotmail.com>
wrote:

Convert them to CSV in Excel, then use TransferText (which does not
work correctly and also doesn't accept XLS/Excel files directly) or
create a link to an Excel XLS workbook and do an Append Query (as some
have suggested here)??
>
I find that hard to believe as Access is part of MS Office and Visual
FoxPro is not.  I split my time using both and I can do a simple copy
command to XLS in VFP or an Append command to DBF or use Office
Automation to build workbooks using VFP with ease.  I've been doing
this for years.
>
TransferText is incorrectly transposing some information as blank
fields (seems like a crap shoot).  I don't know what the CSV (wish it
could be an XLS) file is until I return the file name and path from a
user dialog box that selects the file.
>
-pw

[PW]

Re: Only two options to import Excel files?

On Mon, 19 May 2008 20:27:04 -0700 (PDT), lyle fairfield
<lyle.fairfield @gmail.comwrote :

>Is there a question here?

Do I have any other options????

-pw

>
>On May 19, 11:18 pm, PW <paulremove_wil liamson...@remo vehotmail.com>
>wrote:

>Convert them to CSV in Excel, then use TransferText (which does not
>work correctly and also doesn't accept XLS/Excel files directly) or
>create a link to an Excel XLS workbook and do an Append Query (as some
>have suggested here)??
>>
>I find that hard to believe as Access is part of MS Office and Visual
>FoxPro is not.  I split my time using both and I can do a simple copy
>command to XLS in VFP or an Append command to DBF or use Office
>Automation to build workbooks using VFP with ease.  I've been doing
>this for years.
>>
>TransferText is incorrectly transposing some information as blank
>fields (seems like a crap shoot).  I don't know what the CSV (wish it
>could be an XLS) file is until I return the file name and path from a
>user dialog box that selects the file.
>>
>-pw

[Sky]

Re: Only two options to import Excel files?


"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
news:7ji434han1 dl09b390c71u9n2 guoigj8q2@4ax.c om...

>
For instance, is there any command like this in Access 2003. This is
from Visual FoxPro8 which has been bashed for years but yet as not
part of the MS Office suite and not part of Visual Studio can still
transfer data to and from Excel with ease (and to a DBF file)!:
>
Adds records to the end of the currently selected table from another
file.
>

Here is some sample SQL for a query to insert records into an Access table
named MyAccessTable from an Excel file named C:\Sample\MyExc elFile.xls:

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM [Excel
8.0;HDR=YES;IME X=2;DATABASE=C: \Sample\MyExcel File.xls].[MyExcelWorkshee t];

You can do similar things with text files or dBase files.

- Steve

[PW]

Re: Only two options to import Excel files?

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:

>
>"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
>news:7ji434han 1dl09b390c71u9n 2guoigj8q2@4ax. com...

>>
>For instance, is there any command like this in Access 2003. This is
>from Visual FoxPro8 which has been bashed for years but yet as not
>part of the MS Office suite and not part of Visual Studio can still
>transfer data to and from Excel with ease (and to a DBF file)!:
>>
>Adds records to the end of the currently selected table from another
>file.
>>

>
>Here is some sample SQL for a query to insert records into an Access table
>named MyAccessTable from an Excel file named C:\Sample\MyExc elFile.xls:
>
>INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM [Excel
>8.0;HDR=YES;IM EX=2;DATABASE=C :\Sample\MyExce lFile.xls].[MyExcelWorkshee t];
>
>You can do similar things with text files or dBase files.
>
>- Steve
>

Nice Steve! I will try that tomorrow. I assume I create an Access
table first so I can have field names?

-paul

[Sky]

Re: Only two options to import Excel files?

"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
news:ea7634tbl6 6mgmoum15v3d3gr 727d96met@4ax.c om...

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:
>

>>
>>"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
>>news:7ji434ha n1dl09b390c71u9 n2guoigj8q2@4ax .com...

>>>
>>For instance, is there any command like this in Access 2003. This is
>>from Visual FoxPro8 which has been bashed for years but yet as not
>>part of the MS Office suite and not part of Visual Studio can still
>>transfer data to and from Excel with ease (and to a DBF file)!:
>>>
>>Adds records to the end of the currently selected table from another
>>file.
>>>

>>
>>Here is some sample SQL for a query to insert records into an Access table
>>named MyAccessTable from an Excel file named C:\Sample\MyExc elFile.xls:
>>
>>INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
>>[Excel
>>8.0;HDR=YES;I MEX=2;DATABASE= C:\Sample\MyExc elFile.xls].[MyExcelWorkshee t];
>>
>>You can do similar things with text files or dBase files.
>>
>>- Steve
>>

>
Nice Steve! I will try that tomorrow. I assume I create an Access
table first so I can have field names?
>
-paul

You can append to an existing table, or create a make-table query directly
from Excel, just like from any other data source. Of course, I never
recommend make-table queries in production, since you do not get precisely
defined field specifications, key fields, indexes, etc.

- Steve

[PW]

Re: Only two options to import Excel files?

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:

>
>"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
>news:7ji434han 1dl09b390c71u9n 2guoigj8q2@4ax. com...

>>
>For instance, is there any command like this in Access 2003. This is
>from Visual FoxPro8 which has been bashed for years but yet as not
>part of the MS Office suite and not part of Visual Studio can still
>transfer data to and from Excel with ease (and to a DBF file)!:
>>
>Adds records to the end of the currently selected table from another
>file.
>>

>
>Here is some sample SQL for a query to insert records into an Access table
>named MyAccessTable from an Excel file named C:\Sample\MyExc elFile.xls:
>
>INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM [Excel
>8.0;HDR=YES;IM EX=2;DATABASE=C :\Sample\MyExce lFile.xls].[MyExcelWorkshee t];
>
>You can do similar things with text files or dBase files.
>
>- Steve
>

Not working for me. Access doesn't like tblTempCashReg. Syntax
error:

Insert into
rstTempCashReg( manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail)
Select
manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail
From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE= strReturnVal].[Sheet1]

[lyle fairfield]

Re: Only two options to import Excel files?

I don't like using recordsets but in cases where the import is gnarly,
they can let one proceed a step at a time, first establishing a
connection, then populating the recordset, then modifying the data,
and finally, inserting it into a table. The code can be written and
tested bit by bit.

I had three ways of doing what this code does, and can actually
remember one of the others, which was to link to the Excel file
through SQL-Server.

Option Base 0
Option Compare Database
Option Explicit

Sub UploadDataFromO fficialProjecte dEnrolment(ByVa l ExcelFilePath$,
ByVal ExclusivelyFren chImmersion$, ByVal LateFrenchImmer sion$)
Dim LocalConnection As ADODB.Connectio n
Dim Enrolment&
Dim PreviousSchoolN ame$
Dim ProgramID
Dim SchoolsAndEnrol ment As ADODB.Recordset
Dim SchoolID&
Dim SchoolNameField As ADODB.Field
Dim SchoolName$
Dim SQL$
Dim Areas$(0 To 2)
Dim y&
Dim z&

'-----------------

ExclusivelyFren chImmersion = "," & Trim(Exclusivel yFrenchImmersio n) &
","
LateFrenchImmer sion = "," & Trim(LateFrench Immersion) & ","

'-----------------

Set LocalConnection = New ADODB.Connectio n
With LocalConnection
.Provider = "Microsoft.Jet. OLEDB.4.0"
.Properties.Ite m("Data Source") = ExcelFilePath
.Properties.Ite m("Extended Properties") = "Excel 8.0"
.Open
End With

'-----------------

Areas(0) = "EAST$"
Areas(1) = "NORTH$"
Areas(2) = "WEST$"

SQL = "DELETE FROM Schools"
CurrentProject. Connection.Exec ute (SQL)

SQL = "SELECT * FROM [AREA]"
SQL = SQL & vbNewLine
SQL = SQL & "WHERE [School Name] IS NOT NULL"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [School Name] <'Grand Total'"
SQL = SQL & vbNewLine
SQL = SQL & "AND [F14] IS NOT NULL"

For y = 0 To 2
Set SchoolsAndEnrol ment = LocalConnection .Execute(Replac e(SQL,
"AREA", Areas(y)))
With SchoolsAndEnrol ment
Set SchoolNameField = .Fields(0)
While Not .EOF
SchoolName = StrConv(Replace (Trim(SchoolNam eField.Value),
"'", ""), vbUpperCase)
If Len(SchoolName) 0 And InStr(SchoolNam e, "TOTAL") = 0
Then
ProgramID = 2
If (PreviousSchool Name = SchoolName) And
(InStr(LateFren chImmersion, "," & SchoolName & ",") <0) Then
ProgramID = 3
If PreviousSchoolN ame <SchoolName Then
CurrentProject. Connection.Exec ute "INSERT INTO
Schools ([Name]) VALUES ('" & Replace(SchoolN ame, "'", "") & "')"
SchoolID =
CurrentProject. Connection.Exec ute("SELECT @@Identity")(0)
If InStr(Exclusive lyFrenchImmersi on, "," &
SchoolName & ",") = 0 Then ProgramID = 1
PreviousSchoolN ame = SchoolName
End If
For z = 1 To 11
Enrolment = Nz(.Fields(z).V alue, 0)
If Enrolment 0 Then
CurrentProject. Connection.Exec ute _
"INSERT INTO Enrolments (School, Program,
Class, Enrolment) VALUES (" _
& SchoolID & "," & ProgramID & "," & z & "," &
Enrolment & ")"
End If
Next z
End If
.MoveNext
Wend
End With
Next y

MsgBox "All Done Uploading Schools and Enrolment", vbInformation,
"ffdba"
On Error Resume Next
DoCmd.Close acForm, "UploadNewSchoo lData"

End Sub




On May 21, 6:14 pm, PW <paulremove_wil liamson...@remo vehotmail.com>
wrote:

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
Not working for me.  Access doesn't like tblTempCashReg.  Syntax

[Sky]

Re: Only two options to import Excel files?

"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
news:9g7934l96g 6jr9drdlc5oql5i akljd2oj1@4ax.c om...

On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
comwrote:
>

>>
>>"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
>>news:7ji434ha n1dl09b390c71u9 n2guoigj8q2@4ax .com...

>>>
>>For instance, is there any command like this in Access 2003. This is
>>from Visual FoxPro8 which has been bashed for years but yet as not
>>part of the MS Office suite and not part of Visual Studio can still
>>transfer data to and from Excel with ease (and to a DBF file)!:
>>>
>>Adds records to the end of the currently selected table from another
>>file.
>>>

>>
>>Here is some sample SQL for a query to insert records into an Access table
>>named MyAccessTable from an Excel file named C:\Sample\MyExc elFile.xls:
>>
>>INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
>>[Excel
>>8.0;HDR=YES;I MEX=2;DATABASE= C:\Sample\MyExc elFile.xls].[MyExcelWorkshee t];
>>
>>You can do similar things with text files or dBase files.
>>
>>- Steve
>>

>
Not working for me. Access doesn't like tblTempCashReg. Syntax
error:
>
Insert into
rstTempCashReg( manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail)
Select
manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail
From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE= strReturnVal].[Sheet1]

Is rstTempCashReg the name of a table? The prefix indicates a recordset
name.

Also there is no space after rstTempCashReg and before the parenthesis.

And is "strReturnV al" the literal name of an Excel spreadsheet file? It
should end with .xls. If it is a string, then you need to concatenate it
with the SQL as a string.

Can you make a simple Select query from Excel, such as the following SQL?:

Select
manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail
From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE= YourSpreadsheet Name.xls].[Sheet1]

If you CAN see the Select query datasheet, then you know you have an error
in the "Insert Into" part of the query.

If you CANNOT see the Select query datasheet, then you need to check the
Select part with your Excel file name.

But offhand, it looks like none of the names are correct, and you have some
debugging to do.

- Steve

[PW]

Re: Only two options to import Excel files?

On Thu, 22 May 2008 01:32:55 GMT, "Sky" <sky @ stanley associates .
comwrote:

>"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
>news:9g7934l96 g6jr9drdlc5oql5 iakljd2oj1@4ax. com...

>On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
>comwrote:
>>

>>>
>>>"PW" <paulremove_wil liamson858@remo vehotmail.comwr ote in message
>>>news:7ji434h an1dl09b390c71u 9n2guoigj8q2@4a x.com...
>>>>
>>>For instance, is there any command like this in Access 2003. This is
>>>from Visual FoxPro8 which has been bashed for years but yet as not
>>>part of the MS Office suite and not part of Visual Studio can still
>>>transfer data to and from Excel with ease (and to a DBF file)!:
>>>>
>>>Adds records to the end of the currently selected table from another
>>>file.
>>>>
>>>
>>>Here is some sample SQL for a query to insert records into an Access table
>>>named MyAccessTable from an Excel file named C:\Sample\MyExc elFile.xls:
>>>
>>>INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
>>>[Excel
>>>8.0;HDR=YES; IMEX=2;DATABASE =C:\Sample\MyEx celFile.xls].[MyExcelWorkshee t];
>>>
>>>You can do similar things with text files or dBase files.
>>>
>>>- Steve
>>>

>>
>Not working for me. Access doesn't like tblTempCashReg. Syntax
>error:
>>
>Insert into
>rstTempCashReg (manufacturer,d escription,barc ode,color,style ormodel,size,qt y,oldplu,cost,r etail)
>Select
>manufacturer,d escription,barc ode,color,style ormodel,size,qt y,oldplu,cost,r etail
>From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE= strReturnVal].[Sheet1]

>
>Is rstTempCashReg the name of a table? The prefix indicates a recordset
>name.
>
>Also there is no space after rstTempCashReg and before the parenthesis.
>
>And is "strReturnV al" the literal name of an Excel spreadsheet file? It
>should end with .xls. If it is a string, then you need to concatenate it
>with the SQL as a string.
>
>Can you make a simple Select query from Excel, such as the following SQL?:
>
>Select
>manufacturer,d escription,barc ode,color,style ormodel,size,qt y,oldplu,cost,r etail
>From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE= YourSpreadsheet Name.xls].[Sheet1]
>
>If you CAN see the Select query datasheet, then you know you have an error
>in the "Insert Into" part of the query.
>
>If you CANNOT see the Select query datasheet, then you need to check the
>Select part with your Excel file name.
>
>But offhand, it looks like none of the names are correct, and you have some
>debugging to do.
>
>- Steve
>

I am getting a "Cannot find installable ISAM" message in Access with
this code in SQL view when I try to run it:

Select
manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail
From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE=
C:\SD\Access200 3\Lodgeical\cli entversions\Jos huaCreek\cashre gister20080501. xls].[Sheet1]

The code will not save in the editor to begin with. It is in Red.

Thanks,

-pw

[PW]

Re: Only two options to import Excel files?

What do the $ and & mean Lyle?

I am not using SQL Server, just an Access front and backend.

Thanks,

-paul


On Wed, 21 May 2008 16:56:14 -0700 (PDT), lyle fairfield
<lyle.fairfield @gmail.comwrote :

>I don't like using recordsets but in cases where the import is gnarly,
>they can let one proceed a step at a time, first establishing a
>connection, then populating the recordset, then modifying the data,
>and finally, inserting it into a table. The code can be written and
>tested bit by bit.
>
>I had three ways of doing what this code does, and can actually
>remember one of the others, which was to link to the Excel file
>through SQL-Server.
>
>Option Base 0
>Option Compare Database
>Option Explicit
>
>Sub UploadDataFromO fficialProjecte dEnrolment(ByVa l ExcelFilePath$,
>ByVal ExclusivelyFren chImmersion$, ByVal LateFrenchImmer sion$)
>Dim LocalConnection As ADODB.Connectio n
>Dim Enrolment&
>Dim PreviousSchoolN ame$
>Dim ProgramID
>Dim SchoolsAndEnrol ment As ADODB.Recordset
>Dim SchoolID&
>Dim SchoolNameField As ADODB.Field
>Dim SchoolName$
>Dim SQL$
>Dim Areas$(0 To 2)
>Dim y&
>Dim z&
>
>'-----------------
>
>ExclusivelyFre nchImmersion = "," & Trim(Exclusivel yFrenchImmersio n) &
>","
>LateFrenchImme rsion = "," & Trim(LateFrench Immersion) & ","
>
>'-----------------
>
>Set LocalConnection = New ADODB.Connectio n
>With LocalConnection
.Provider = "Microsoft.Jet. OLEDB.4.0"
.Properties.Ite m("Data Source") = ExcelFilePath
.Properties.Ite m("Extended Properties") = "Excel 8.0"
.Open
>End With
>
>'-----------------
>
>Areas(0) = "EAST$"
>Areas(1) = "NORTH$"
>Areas(2) = "WEST$"
>
>SQL = "DELETE FROM Schools"
>CurrentProject .Connection.Exe cute (SQL)
>
>SQL = "SELECT * FROM [AREA]"
>SQL = SQL & vbNewLine
>SQL = SQL & "WHERE [School Name] IS NOT NULL"
>SQL = SQL & vbNewLine
>SQL = SQL & "AND [School Name] <'Total'"
>SQL = SQL & vbNewLine
>SQL = SQL & "AND [School Name] <'Grand Total'"
>SQL = SQL & vbNewLine
>SQL = SQL & "AND [F14] IS NOT NULL"
>
>For y = 0 To 2
Set SchoolsAndEnrol ment = LocalConnection .Execute(Replac e(SQL,
>"AREA", Areas(y)))
With SchoolsAndEnrol ment
Set SchoolNameField = .Fields(0)
While Not .EOF
SchoolName = StrConv(Replace (Trim(SchoolNam eField.Value),
>"'", ""), vbUpperCase)
If Len(SchoolName) 0 And InStr(SchoolNam e, "TOTAL") = 0
>Then
ProgramID = 2
If (PreviousSchool Name = SchoolName) And
>(InStr(LateFre nchImmersion, "," & SchoolName & ",") <0) Then
>ProgramID = 3
If PreviousSchoolN ame <SchoolName Then
CurrentProject. Connection.Exec ute "INSERT INTO
>Schools ([Name]) VALUES ('" & Replace(SchoolN ame, "'", "") & "')"
SchoolID =
>CurrentProject .Connection.Exe cute("SELECT @@Identity")(0)
If InStr(Exclusive lyFrenchImmersi on, "," &
>SchoolName & ",") = 0 Then ProgramID = 1
PreviousSchoolN ame = SchoolName
End If
For z = 1 To 11
Enrolment = Nz(.Fields(z).V alue, 0)
If Enrolment 0 Then
CurrentProject. Connection.Exec ute _
"INSERT INTO Enrolments (School, Program,
>Class, Enrolment) VALUES (" _
& SchoolID & "," & ProgramID & "," & z & "," &
>Enrolment & ")"
End If
Next z
End If
.MoveNext
Wend
End With
>Next y
>
>MsgBox "All Done Uploading Schools and Enrolment", vbInformation,
>"ffdba"
>On Error Resume Next
>DoCmd.Close acForm, "UploadNewSchoo lData"
>
>End Sub
>
>
>
>
>On May 21, 6:14 pm, PW <paulremove_wil liamson...@remo vehotmail.com>
>wrote:

>On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
>Not working for me.  Access doesn't like tblTempCashReg.  Syntax

[PW]

Re: Only two options to import Excel files?

Lyle and Steve, I love your code!. And thanks so much for your help!

Guys, all I am trying to do is to come up with an equivalent in VBA
for this simple line of code that I do in Visual FoxPro about every
day (you should see the stuff I do with Office Automation and Excel,
Word,...with Visual FoxPro! I guess I just bragged - sorry about
that!;-):

Use tblImpData
Select tblImpData_pw
Append From k:\AttributionD ata\IMPORTDATA\ chase.xls type XLS

Bingo! Batta Bing! That's all it takes. Show me that it can happen
in Access!! Don't mean to start a war here between Access and Visual
FoxPro. I love them both, but come on now!

VFP is not part of MS Office or Visual Studio so how come VFP can do
this so easily and appears impossible in Access (which is part of
Office)???????? ???????

The app's front end/mde is in Access 2003 and the backend mdb is
separated also in Access 2003. I need this for a distrubited mde so
we are not on site and do not have Access running.

-paulw

On Wed, 21 May 2008 16:56:14 -0700 (PDT), lyle fairfield
<lyle.fairfield @gmail.comwrote :

>I don't like using recordsets but in cases where the import is gnarly,
>they can let one proceed a step at a time, first establishing a
>connection, then populating the recordset, then modifying the data,
>and finally, inserting it into a table. The code can be written and
>tested bit by bit.
>
>I had three ways of doing what this code does, and can actually
>remember one of the others, which was to link to the Excel file
>through SQL-Server.
>
>Option Base 0
>Option Compare Database
>Option Explicit
>
>Sub UploadDataFromO fficialProjecte dEnrolment(ByVa l ExcelFilePath$,
>ByVal ExclusivelyFren chImmersion$, ByVal LateFrenchImmer sion$)
>Dim LocalConnection As ADODB.Connectio n
>Dim Enrolment&
>Dim PreviousSchoolN ame$
>Dim ProgramID
>Dim SchoolsAndEnrol ment As ADODB.Recordset
>Dim SchoolID&
>Dim SchoolNameField As ADODB.Field
>Dim SchoolName$
>Dim SQL$
>Dim Areas$(0 To 2)
>Dim y&
>Dim z&
>
>'-----------------
>
>ExclusivelyFre nchImmersion = "," & Trim(Exclusivel yFrenchImmersio n) &
>","
>LateFrenchImme rsion = "," & Trim(LateFrench Immersion) & ","
>
>'-----------------
>
>Set LocalConnection = New ADODB.Connectio n
>With LocalConnection
.Provider = "Microsoft.Jet. OLEDB.4.0"
.Properties.Ite m("Data Source") = ExcelFilePath
.Properties.Ite m("Extended Properties") = "Excel 8.0"
.Open
>End With
>
>'-----------------
>
>Areas(0) = "EAST$"
>Areas(1) = "NORTH$"
>Areas(2) = "WEST$"
>
>SQL = "DELETE FROM Schools"
>CurrentProject .Connection.Exe cute (SQL)
>
>SQL = "SELECT * FROM [AREA]"
>SQL = SQL & vbNewLine
>SQL = SQL & "WHERE [School Name] IS NOT NULL"
>SQL = SQL & vbNewLine
>SQL = SQL & "AND [School Name] <'Total'"
>SQL = SQL & vbNewLine
>SQL = SQL & "AND [School Name] <'Grand Total'"
>SQL = SQL & vbNewLine
>SQL = SQL & "AND [F14] IS NOT NULL"
>
>For y = 0 To 2
Set SchoolsAndEnrol ment = LocalConnection .Execute(Replac e(SQL,
>"AREA", Areas(y)))
With SchoolsAndEnrol ment
Set SchoolNameField = .Fields(0)
While Not .EOF
SchoolName = StrConv(Replace (Trim(SchoolNam eField.Value),
>"'", ""), vbUpperCase)
If Len(SchoolName) 0 And InStr(SchoolNam e, "TOTAL") = 0
>Then
ProgramID = 2
If (PreviousSchool Name = SchoolName) And
>(InStr(LateFre nchImmersion, "," & SchoolName & ",") <0) Then
>ProgramID = 3
If PreviousSchoolN ame <SchoolName Then
CurrentProject. Connection.Exec ute "INSERT INTO
>Schools ([Name]) VALUES ('" & Replace(SchoolN ame, "'", "") & "')"
SchoolID =
>CurrentProject .Connection.Exe cute("SELECT @@Identity")(0)
If InStr(Exclusive lyFrenchImmersi on, "," &
>SchoolName & ",") = 0 Then ProgramID = 1
PreviousSchoolN ame = SchoolName
End If
For z = 1 To 11
Enrolment = Nz(.Fields(z).V alue, 0)
If Enrolment 0 Then
CurrentProject. Connection.Exec ute _
"INSERT INTO Enrolments (School, Program,
>Class, Enrolment) VALUES (" _
& SchoolID & "," & ProgramID & "," & z & "," &
>Enrolment & ")"
End If
Next z
End If
.MoveNext
Wend
End With
>Next y
>
>MsgBox "All Done Uploading Schools and Enrolment", vbInformation,
>"ffdba"
>On Error Resume Next
>DoCmd.Close acForm, "UploadNewSchoo lData"
>
>End Sub
>
>
>
>
>On May 21, 6:14 pm, PW <paulremove_wil liamson...@remo vehotmail.com>
>wrote:

>On Tue, 20 May 2008 14:47:31 GMT, "Sky" <sky @ stanley associates .
>Not working for me.  Access doesn't like tblTempCashReg.  Syntax

[PW]

Re: Only two options to import Excel files?

The syntax you supplied (I copied and pasted it on one line):

INSERT INTO MyAccessTable (FieldA, FieldB) SELECT FieldA, FieldB FROM
[Excel
8.0;HDR=YES;IME X=2;DATABASE=C: \Sample\MyExcel File.xls].[MyExcelWorkshee t]

generates "compile error: expected end of statement" when I move the
cursor out of that line.

-pw

[PW]

Re: Only two options to import Excel files?

strSQL = "Insert into tblTempCashReg
(manufacturer,d escription,barc ode,color,style ormodel,size,qt y,oldplu,cost,r etail)
Select
manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail
From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE= strReturnVal].[Sheet1]"

DoCmd.RunSQL strSQL

Generates "could not find installable ISAM"

-pw

[Salad]

Re: Only two options to import Excel files?

PW wrote:

strSQL = "Insert into tblTempCashReg
(manufacturer,d escription,barc ode,color,style ormodel,size,qt y,oldplu,cost,r etail)
Select
manufacturer,de scription,barco de,color,styleo rmodel,size,qty ,oldplu,cost,re tail
From [Excel8.0;HDR=YE S;IMEX=2;DATABA SE= strReturnVal].[Sheet1]"
>
DoCmd.RunSQL strSQL
>
Generates "could not find installable ISAM"
>
-pw

What is ;Database= strReturnVal? Is strReturnVal a new Access keyword?
Does Access like spaces in the string defining the source?

Before you even get into Inserting why don't you simply attempt to get
the Select query working first?

Get into the QBE, (Query/Design/New/Cancel) and select View/SQL from the
menu and paste the Select SQL into it and run and debug.

Last Dance

http://www.youtube.com/watch?v=WdTYcnUBADw