Importing multiple files from excel into a table and also inserting the file name into a column

[Mary87]

Hi,

Please can you assist. I am trying to import multiple spreadsheets from a folder into an access database. This is working - however when trying to update the table with the file name, I receive an error.

Please see below, the code I am using...

Function DoImport()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' New
Dim SQL As String
' Change this next line to True if the first row in EXCEL worksheet


' has field names
blnHasFieldNames = True
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\QlikView Reports\Importing with file name"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "Import"


strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

'new
DoCmd.RunSQL
"UPDATE [Import] SET Import.[File Name]= & strFile & WHERE Import.[File Name] IS NULL OR Import.[File Name]='';""

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile



strFile = Dir()
Loop
End Function




Thank you v much

[JoeM]

Try this:

DoCmd.RunSQL
"UPDATE [Import] SET Import.[File Name]= '" & strFile & "' WHERE Import.[File Name] IS NULL OR Import.[File Name]='';"

[Mary87]

Thank you for the quick reply.

I have updated the script, however am now receiving the following error:
"Compile error:

Expected: line number or label or statement or end of statement"

[JoeM]

You don't have a carriage return between your DoCmd.RunSQL line and the actual code, do you?
I just copied what you had, thinking it was just a wrapping situation. If that is not the case, it should look like this:

DoCmd.RunSQL "UPDATE [Import] SET Import.[File Name]= '" & strFile & "' WHERE Import.[File Name] IS NULL OR Import.[File Name]='';"

[Mary87]

Thank you JoeM for your quick response.
I have updated the code, however it does not run.... Did it run for you?

[JoeM]

I would have to try to recreate your whole scenario in order to see if it works for your situation.
Note that you have the ability to attach your database here for analysis. Then we could actually work on exactly what you have.
Just be sure to remove all sensitive information before doing so.

[Mary87]

Thank you JoeM.

I have attached.

[JoeM]

OK. I will try to take a look at it tonight (due to corporate security rules, I cannot download files off of the internet from my current location).

Does the database you uploaded already contained imported records that need this field to be updated?

[Mary87]

Thank you very much Joe.
No the database contains no records ...

[JoeM]

Could you include a dummy file of a few records to import so we can test the functionality completely?
Also, in case it is not entirely obvious how you run this process, can you tell us how you run it (is there a button on Form, a Macro you click, etc).

[Mary87]

I have attached some test data.
This is currently saved in the location: C:\QlikView Reports\Importing with file name
I load up Access 2007, click on the "Create tab" => Module (under Macros & Code) => and play the "Import with Name" module.
However it does not run :-(.
Thank you v much for your help

[JoeM]

The issue is that you do not have a field name "File Name" in your table. An Update Query can only update values of existing fields, it cannot create fields that do not already exist.
So you want to add a "File Name" field to your table. Note that you do not have to have a value to import for every field in your table.

[Mary87]

Thank you for checking Joe, I have inserted another column to the table, however can not run the module.
Please see attached screenshot- I highlight the line "Public Function DoImprot ()".
Am I missing a step?

[JoeM]

What kind of error message does it give you?

I think I had to do one other thing, and that was place another backslash at the end of your strPath declaration.

strPath = "C:\QlickView Reports\Importing with file name\"

I didn't bring it up before because in your original post, it sounded like you were getting the import to work, just not the field update. So I thought maybe you just changed the file path for my example. But using the database and file you gave me, I was able to get it to do what you want with those two changes.

Note that I added the new field to the very end of the table.

[Mary87]

The macro runs after adding the extra backslash.
However I am receiving an error
"Run-time error '2391';
Field 'creation_ts' doesnt exist in destination table 'Import'.
I dont know why this is happening, because I dont have a column 'creation_ts'

Thank you v v much for your time and assistance