Importing Excel into Access

[katjoe]

Hi, I am new with macro builder and need help with some errors. One, I am trying to import three spreadsheet tabs out of five from Excel into three separate tables in Access that have already been created. The tabs in the workbook are in the order of: Table1, Table2, Table3, Table4, and Table5. I have created three import/export transfer spreadsheet action in one macro. The three import /export action are pointing to Table2, Table3 and Table4. When I run the macro Access looks for the first table (Table1) instead of Table2. I do not know why Access is looking for Table1. I moved the first tab (Table1) in the workbook to the last position after Table5. I ran it again, however, after a few moments I got an error "Unable to find Last Name field in the destination table , Table3. The field Last Name is only in the first tab (Table2) . Table 3 and Table4 do not have the field "Last Name." Reviewing Table2 it looks like all of the data was inserted properly. I am lost with these two problems. can someone please help me understand what I am doing wrong or what I should have done? I did try to create three separate macros for each action but I got the same error. Appreciate any guidance and thank you in advance for your help.

[CJ_London]

Difficult to advise without seeing your code. Unfortunately macros are not very developer friendly for debugging.

most developers here use vba. Suggest convert your macro to vba (see option which I think you will find on the ribbon). Then copy/paste the resulting vba here- and use the code tags to preserve formatting (highlight pasted code and click the # button)

[Bulzie]

Seems like some formatting on the tables you already have created in Access that you are trying to import into? Maybe try to import those 3 tables into brand new tables, let it create the tables in Access maybe just to see what it does and what the fields look like, is the data right, etc.

[katjoe]

Hi CJ_London,
My appologies before I begin. This is the first for me as of posting in a forum. I could not find how to use the code tags.
I converted my import code and ran it. Got the error "Cannot find the object filePath & *.xlsx."
Any help would be very much appreciated.
Code:
<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")


Set objXL = Excel.Application


Do While LEN(fileName) > 0


Set WKB= objXL.Workbooks.open(filePath & fileName)


Set colWorksheets =WKB.Worksheets


For Each WKS in colWorksheets
If (WKS.Name = "Table2") Then
DOCMD.TransferSpreadsheet acImport,10,"Table2","filePath & fileName", True,""


ElseIf
(WKS.Name = "Table3") Then
DOCMD.TransferSpreadsheet acImport,10,"Table3","filePath & fileName", True,""
ElseIf
(WKS.Name = "Table4") Then
DOCMD.TransferSpreadsheet acImport,10,"Table4","filePath & fileName", True,""
End If
Next
Loop


WKB.close SaveChanges: = False
Set WKB = Nothing
objXL Quit
Set objXL = Nothing


End Sub


</code>
.

[CJ_London]

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]

When you are repeating code, generally there is a better way.

DoCmd.TransferSpreadsheet acImport, 10, WKS.Name, filePath & fileName, True

[katjoe]

Thank you so much for your guidance. I will try this!

[katjoe]

Ok, thank you for this guidance

[katjoe]

OK, I will try this, thanks.

[ArviLaanemets]

Generally, it is a bad idea to insert data from Excel table(s) directly into MS Access (or any other relational database program) table, which is used in this database not only for data import. There is no way for you to prevent Excel users messing up with data entry there, and as result probably to broke up your database.

I myself have used 2 approaches:
1. The Excel which contains the table is in some network resource, and the table is linked to your Access database. User works with this Excel. In Access, you use this linked table only to read data from there by procedure. The procedure then processes the read data (checking the validity of data, checking the data being new one, or updated, etc.). When all is OK, then another Access table, which is used as regular Access table, is updated with valid data from linked table;
2. When the linking of Excel Table(s) is not the option, you read Excel table into array variable, and do all imported data processing there. And then add valid data into your Access table.

[katjoe]

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.

		
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

[Welshgasman]

Walk your code line by line with F8 after setting a breakpoint. That will also show you which line is causing the error.
You are adding \ twice to the filepath?

I can get type mismatch on this line

fileName = Dir("C:\temp\", "*.xlsx")

https://learn.microsoft.com/en-us/of...p/dir-function

https://trumpexcel.com/vba-dir-funct...es-in-a-Folder