Importing from Excel - setting the filepath / invalid SQL statement

[rikesh]

Hello,

I have a database which allows the user to generate an Excel 'template', populate the data and then import it back in. The export works fine and generates a spreadsheet in the same folder where the database is stored on the user's machine.

For the import, I decided to set up an append query which works fine when I hard code the file path. The only problem I have is setting the file path for where the Excel file is located. Ideally I would have a file browser popup so the user can select the import file, but failing that, I want it to look for the file in the same location as the database is stored.

The query (which is being run as an event on button click, as VBA code) is:

'Set input file

inputFileName = CurrentProject.Path & "\ImportTemplate.xls"

SQL = "INSERT INTO tblOrgFunction " & _
"SELECT T1.Function, T1.HeadOfFunctionID, T1.FunctionDescription " & _
"FROM [Excel 8.0;HDR=YES;IMEX=1;Database=inputFileName].[tblOrgFunction$A1:U65536] AS T1;"


DoCmd.RunSQL "SQL"

This is producing the error: Invalid SQL statement. Expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE.

I have also tried creating this as a separate SELECT query just to test it, and it works fine with the location hard coded but when using CurrentProject.Path it says it cannot find the file - it thinks CurrentProject.Path is an actual path, which led me to believe you cannot call this variable within a separate query.

Can anyone shed some light as to why this isn't working?

Many thanks

[rikesh]

I found a workaround but had to change it from the current database path to the desktop path so that I could use the DoCmd.TransferSpreadsheet method:

strPathFile = Environ("UserProfile") & "\Desktop\ImportSettings.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblOrgFunction", strPathFile, True, "tblOrgFunction$A1:U65536"

This is only a workaround as it doesn't allow me to run a query and manipulate the data, so a response to the original post would still be very helpful.

Thanks

[June7]

Variable must be concatenated, otherwise you just the variable name as literal text, not the variable value.

"FROM [Excel 8.0;HDR=YES;IMEX=1;Database=" & inputFileName & "].[tblOrgFunction$A1:U65536] AS T1;"

[rikesh]

Thank you - worked great!