Query for importing data from Excel

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

At the moment I use the following query:

INSERT INTO Table1 ( Field1, Field2)
SELECT Field1, Field2
FROM [Sheet$] IN 'C:\File.xls'[EXCEL 5.0;];

The above works fine. But I need the path to be taken from
a field on a form (e.g. from [Forms]![frmMyForm]!
[FilePath] )

What will be the syntax instead of " 'C:\File.xls' "?

I tried to create the sql statement dynamically by using
code in the form:

dim strSQL as string
strSQL = "INSERT INTO Table1 ( Field1, Field2) " & _
"SELECT Field1, Field2 " & _
"FROM [Sheet$] IN " & Me.FilePath
CurrentDB.Execute strSQL, dbFailOnError

But the debugger claims that there is a syntax error in
FROM statement.
 
Mike said:
At the moment I use the following query:

INSERT INTO Table1 ( Field1, Field2)
SELECT Field1, Field2
FROM [Sheet$] IN 'C:\File.xls'[EXCEL 5.0;];

The above works fine. But I need the path to be taken from
a field on a form (e.g. from [Forms]![frmMyForm]!
[FilePath] )

What will be the syntax instead of " 'C:\File.xls' "?

I tried to create the sql statement dynamically by using
code in the form:

dim strSQL as string
strSQL = "INSERT INTO Table1 ( Field1, Field2) " & _
"SELECT Field1, Field2 " & _
"FROM [Sheet$] IN " & Me.FilePath
CurrentDB.Execute strSQL, dbFailOnError

But the debugger claims that there is a syntax error in
FROM statement.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In my last post I forgot to include the require quote delimiters on the
path name. Here is how to correct that:

const Q = """" ' 1 double-quote
.... etc. ...
"FROM [Sheet$] IN " & Q & Me.FilePath & Q

This will put a double-quote delimiter around the filepath, which is
required.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQF9FuoechKqOuFEgEQKD4gCgx2aglPEOp1B28JeiLO3FlGVOEwMAoMAq
GKBYoySIdINw1H45vJaKFhES
=AqE7
-----END PGP SIGNATURE-----
 
Back
Top