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 >
Code:
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