none
Excel VBA Import excel file into access RRS feed

  • Question

  • Hi All,

    I am using below access vba code to import excel file into access database but it throws error " Field other do not exist in access table". Excel file has proper headers from A to Z.

    Sub ImportXL()
    Const cstrFolder As String = "C:\Users\Desktop\DB\"
    Dim strFile As String
    Dim i As Long, j As Long
     Dim var As String
    strFile = Dir(cstrFolder & "*.xls")
    If Len(strFile) = 0 Then
        MsgBox "No Files Found"
    Else
        Do While Len(strFile) > 0
        Workbooks.Open (cstrFolder & strFile)
      'var = ActiveWorkbook.Sheets("request").Range("B10").Value
         
    j = ActiveWorkbook.Sheets("request").Range("A30000").End(xlUp).Row
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
           "tblMaterialReq", cstrFolder & strFile, True, "A5:Z" & j
            'there may be a better type to use here
    Workbooks(strFile).Close SaveChanges:=False
            i = i + 1
            strFile = Dir()
        Loop
        MsgBox i & " Files are imported"
    End If
    End Sub

    My excel file has headers in row 5 and data starts from row6. one of the field has data in row 6 which is other. If i remove this field than it throws error field F2 do not exist in access table.


    • Edited by zaveri cc Thursday, September 1, 2016 7:37 PM
    Thursday, September 1, 2016 4:59 PM

Answers

  • Hi Zaveri CC,

    The problem is that when there Is a field that is not Table but it is available in the Excel file.

    so in this situation Access give this type of Errors. it is not specifically only "F2".

    it can be F1,F2,F3,F4 and so on.

    so you can try to make all the fields same as you have in the Table and then try to import the data.

    for more information regarding this error please visit the link below.

    "Field 'F1' Doesn't Exist in Destination Table" Error Message

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by zaveri cc Friday, September 2, 2016 2:48 AM
    • Unmarked as answer by zaveri cc Friday, September 2, 2016 2:44 PM
    • Marked as answer by zaveri cc Friday, September 2, 2016 4:07 PM
    Friday, September 2, 2016 2:37 AM
    Moderator

All replies

  • Hi Zaveri CC,

    The problem is that when there Is a field that is not Table but it is available in the Excel file.

    so in this situation Access give this type of Errors. it is not specifically only "F2".

    it can be F1,F2,F3,F4 and so on.

    so you can try to make all the fields same as you have in the Table and then try to import the data.

    for more information regarding this error please visit the link below.

    "Field 'F1' Doesn't Exist in Destination Table" Error Message

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by zaveri cc Friday, September 2, 2016 2:48 AM
    • Unmarked as answer by zaveri cc Friday, September 2, 2016 2:44 PM
    • Marked as answer by zaveri cc Friday, September 2, 2016 4:07 PM
    Friday, September 2, 2016 2:37 AM
    Moderator
  • I manually imported the xl file into access so now all the fields are available in access and than using above code tried to import it but it throws same error. Odd thing is it says field "Other" do not exist in access table but this field do not even exist in xl but one field has data row which has other as value.

    I think my xl file is corrupted bcs i copied the file to new file and than my code worked.

    • Edited by zaveri cc Friday, September 2, 2016 4:07 PM
    Friday, September 2, 2016 2:46 PM
  • It sounds like something is jammed up in memory, perhaps.  Close everything and reopen it.  If that doesn't work, reboot your machine.  Make sure the spelling of the fields in Access matches what you have in Excel.  Also, look for a possible space before, or after, and field name.

    MY BOOK

    Friday, September 2, 2016 7:08 PM