Importing MS Excel sheet into MS Access via VBA not importing everything

Question

I have an MS Access Database program that brings in the data from an MS Excel workbook (it's only one sheet) that is then processed and kicks out a completed form for the user.

The whole thing was working fine, but when they encountered an error in the report I went to work with the database program to find the problem and a new one cropped up. MS Access is only importing cells A1 and A2.

When I try to manually import the Excel file and append to a table, the Import Wizard only shows those two cells as being included in the file. Somehow, MS Access is not seeing the entire sheet of data.

I have tried manually changing the acSpreadsheetType option in DoCmd.TransferSpreadsheet and it worked once. I have also tried changing it programmatically with no effect (i.e., I still only get cells A1 and A2).

Here is the segment of the VBA that deals with this part of the program:

Dim acSpreadsheetType As Integer

strFile2Import = txtFindFile

acSpreadsheetType = ExcelVersion(strFile2Import)

DoCmd.Hourglass (HourglassOn)
DoCmd.SetWarnings (WarningsOff)

DoCmd.RunSQL _
    "DELETE tbl_Work_Plan_Item_Import.*, * " & _
    "FROM tbl_Work_Plan_Item_Import;"

DoCmd.TransferSpreadsheet _
    acImport, acSpreadsheetType, "tbl_Work_Plan_Item_Import", strFile2Import, True

and here is the "ExcelVersion" function:

Public Function ExcelVersion(ByVal strFile2Import As String)
    'https://msdn.microsoft.com/en-us/library/office/ff840717.aspx
    'https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    Set wb = objapp.workbooks.Open(strFile2Import, True, False)
    ExcelVersion = wb.FileFormat
    wb.Close

    objapp.Quit

    Set objapp = Nothing

    Select Case ExcelVersion
        Case 29             'xlExcel3 (Excel3)
            ExcelVersion = 0    'acSpreadsheetTypeExcel3 (Microsoft Excel 3.0 format)
        Case 33             'xlExcel4 (Excel4)
            ExcelVersion = 6    'acSpreadsheetTypeExcel4 (Microsoft Excel 4.0 format)
        Case 39             'xlExcel5 (Excel5)
                            'xlExcel7 (Excel7)
            ExcelVersion = 5    'acSpreadsheetTypeExcel5 (Microsoft Excel 5.0 format)
                                'acSpreadsheetTypeExcel7 (Microsoft Excel 95 format)
        Case 46             'xlXMLSpreadsheet (XML Spreadsheet)
            ExcelVersion = 10   'acSpreadsheetTypeExcel12Xml (Microsoft Excel 2010 XML format)
        Case 50, 51         'xlExcel12 (Excel12)
                            'xlWorkbookDefault (Workbook Default)
            ExcelVersion = 9    'acSpreadsheetTypeExcel12 (Microsoft Excel 2010 format)
        Case 56             'xlExcel8   (Excel8)
            ExcelVersion = 8    'acSpreadsheetTypeExcel8 (Microsoft Excel 97 format)
                                'acSpreadsheetTypeExcel9 (Microsoft Excel 2000 format)
    End Select

End Function

"strFile2Import" and "txtFindFile" are the file name and full path to be imported that are gotten from a File Dialog box.

My original program didn't have the ExcelVersion function and I had hardcoded acSpreadsheetType as "acSpreadsheetTypeExcel12XML" and when I changed that to "acSpreadsheetTypeExcel9" it worked correctly once.

What am I missing?

For the record, I have also tried doing a .recalc and .requery to the entire form after performing the import, and that was unsuccessful as well.

Any help you guys can throw my way would be greatly appreciated!

I think the interesting thing you wrote was that "manually import the Excel file" also only imported A1 and A2. Forget getting the code to work for now and try and get manually importing the workbook to do what you expect! I would try saving the excel file in various different excel formats. I would try recreating a NEW excel file that you can trust is not "corrupted" somehow. This will case more light on what is going wrong.. I would try importing the xl fiel that does not work into access installed on another PC. Good idea? — HarveyFrench

HarveyFrench HarveyFrench · Accepted Answer · 2015-09-30T18:29:07

"DELETE tbl_Work_Plan_Item_Import.*, * " & _

This has the * twice.

Change to

"DELETE tbl_Work_Plan_Item_Import.* " & _

Importing MS Excel sheet into MS Access via VBA not importing everything

1 Answers