Importing Excel into Access

[katjoe]

Hi,


I have made some progress but I am getting the 3144 error in my SQL statement. At this point I am trying to use the variables “filename” and “fLocation” to update two columns in an Access table.
The “filename” is a variable that points to the Excel file and the “fLocation” holds the value of the name of the folder where the file is stored.
I have tried several variations as well as the debugger to fix this but I just can’t get it right and I am hoping someone can help me.
My code:

Dim fileName as string
Dim fLocation as string
Dim strSQL as string
fileName = Dir(filePath, "*.xlsx")


fLocation = Test2


Without the SQL statement the script imports the data . I am doing one step at a time and as each part of the script works I am adding the next step.
In the loop I have the DoCmd.TransferSpreadsheet, next I am updating the table by using the SQL UPDATE.


     strSQL = "UPDATE [TBLTable 2] set[File Name] = ' " & fileName & " ', SET [File Location] = ' " & fLocation & " ' "Where [File Name] and [File Location] IS NULL";"


DoCmd.RunSQL strSQL
'I did try to put this statement before the query, did not make a difference.

The field name in the table has a space so I used [] around it.
Thank you in advance!!

I've tidied up your code - Note if you are going to type the tags rather than simply using the # button you need to use [ and ] rather than < and >

Public Sub ImportData()
Dim fileName As String
Dim filePath As String
Dim WKS As Object

    filePath = "C:\Employee_Group\Reports\Employee_Report"
    fileName = Dir(filePath, "*.xlsx")
    filepath=filepath & "\" 'you need to add the \


    Set objXL = Excel.Application
    
    Do While Len(fileName) > 0
    
        Set WKB = objXL.Workbooks.Open(filePath & fileName) 'my guess this is where the error is generated
        Set colWorksheets = WKB.Worksheets
        
        For Each WKS In colWorksheets
        
            If (WKS.Name = "Table2") Then
            
                DoCmd.TransferSpreadsheet acImport, 10, "Table2", "filePath & fileName", True, "" 'this need to be just filePath & fileName, not "filePath & fileName"
            
            ElseIf (WKS.Name = "Table3") Then
            
                DoCmd.TransferSpreadsheet acImport, 10, "Table3", "filePath & fileName", True, "" 'this need to be just filePath & fileName, not "filePath & fileName"
                
            ElseIf (WKS.Name = "Table4") Then
            
                DoCmd.TransferSpreadsheet acImport, 10, "Table4", "filePath & fileName", True, "" 'this need to be just filePath & fileName, not "filePath & fileName"
                
            End If
        Next

        filename=Dir() 'new line required
    Loop
       
    WKB.Close SaveChanges:=False
    Set WKB = Nothing
    objXL Quit
    Set objXL = Nothing


End Sub

learn to use the debug window and debug.print code. Also when the code stops, it highlights what line the code is stopped on

if you enter the code

debug.print filePath & fileName

you will see something like

C:\Employee_Group\Reports\Employee_Reportsomefile.xlsx

which tells you you are missing a \

you will get errors further down because you have encapsulated the concatenation into a string.

You are also creating an infinite loop since you don't have any code to update the filename in the loop. So between the Next line and the Loop line you need FileName=Dir()

I've annotated your code where the fixes need to be

edit: when using vba, at the top of each module will be Option Compare Database. Below this you should add Option Explicit. This means you can hit the compile button and will check you don't have any missing or wrongly spelt code before you run the code. Otherwise these errors will get mixed up with runtime errors when you run the code. You can automate this for new modules by going to tools>options>editor on the vba menu and ticking the Require Variable declaration option (don't know why it is not the default). For existing modules you will need to add Option Explicit manually. In your code you have not declared objXL for example

[Welshgasman]

You need to start to learn on how to concatenate.
If you are using variables for path and filename, then why not for the respective sheet?

I put stuff like this into a string variable, so I can debug.print it to make sure it is correct, then I can use it in the function.

However using just what you have.....

filePath = "C:\Employee_Group\Reports\Employee_Report\" ' Why not do it in one go?
...
.....
.......

            If (WKS.Name = "Table2" or WKS.Name = "Table3" OR WKS.Name = "Table4") Then
                DoCmd.TransferSpreadsheet acImport, 10, WS.Name, filePath & fileName, True
          End If

Any time you are repeating code, there is likely a much better way?

I get Type Mismatch on your fileName = Dir(filePath, "*.xlsx")

so check your values.

This works in the immediate window ?

? dir("F:\temp\*.xlsx")
DailySummary_SubmittalsOverdueDataLeadChart_202405 06-DEMO.xlsx