title: Importing MS Excel sheet into MS Access via VBA not importing everything
body: <p>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. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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). </p>
  
  <p>Here is the segment of the VBA that deals with this part of the program: </p>
  
  <pre><code>Dim acSpreadsheetType As Integer
  
  strFile2Import = txtFindFile
  
  acSpreadsheetType = ExcelVersion(strFile2Import)
  
  DoCmd.Hourglass (HourglassOn)
  DoCmd.SetWarnings (WarningsOff)
  
  DoCmd.RunSQL _
      "DELETE tbl_Work_Plan_Item_Import.*, * " &amp; _
      "FROM tbl_Work_Plan_Item_Import;"
  
  DoCmd.TransferSpreadsheet _
      acImport, acSpreadsheetType, "tbl_Work_Plan_Item_Import", strFile2Import, True
  </code></pre>
  
  <p>and here is the "ExcelVersion" function: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>"strFile2Import" and "txtFindFile" are the file name and full path to be imported that are gotten from a File Dialog box. </p>
  
  <p>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. </p>
  
  <p>What am I missing? </p>
  
  <p>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. </p>
  
  <p>Any help you guys can throw my way would be greatly appreciated!</p>