Hi JC_London,
I made the corrections in my scripts. Added updated comments
1. Recieving a new error.
ERROR 13 Type Mix Match
In regards to the debug tool, I tried this by using the "compile test", "Step Into", "Run to cursor" and even put break points through the script to see if I can find the error.
I have no red lines and it does not seem to locate the location of the error.
I read something on the variable "colWorksheet" in that this sometimes can cause the ERROR 13 due to the reason that the Excel workbook could have other objects such as charts, our worksheet do not have charts, They are made up of table name, office name and Y/N answers as data.
Cannot figure where the error is. Do you have an Idea of what I can do?
Thanks so much!
Below is the updated script and the correction you recommended.
Code:
Option Compare Database
Option Explicit
Public Sub ImportData()
Dim fileName As String
Dim filePath As String
Dim WKS As Object
Dim WKB as object
Dim colWorksheet as object
Dim objXL as object
'Access kept telling me that WKB, colWorksheet and objXL needs to be declared.
filePath = "C:\Employee_Group\Reports\Employee_Report\" 'Added the "\"
'Added the "\"
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
'I think this is part of the problem. When I put the whole path plus one file name it at least recognized file but when I change it to "*.XLS" it then cannot find the file.
'After changes the error 13 displays
Set colWorksheets = WKB.Worksheets
'could "colWorksheets" be a problem? if so, not sure what to use as a variable.
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"
'Made this correction.
ElseIf (WKS.Name = "Table3") Then
DoCmd.TransferSpreadsheet acImport, 10, "Table3", filePath & fileName, True, "" 'this need to be just filePath & fileName, not "filePath & fileName"
'Made this correction
ElseIf (WKS.Name = "Table4") Then
DoCmd.TransferSpreadsheet acImport, 10, "Table4", filePath & fileName, True, "" 'this need to be just filePath & fileName, not "filePath & fileName"
'I made this correction.
End If
Next
filename=Dir() 'new line required
'Added this
Loop
WKB.Close SaveChanges:=False
Set WKB = Nothing
objXL Quit
Set objXL = Nothing
End Sub