Issue uploading Excel(.xls) onto Access(.accdb) - Setting Parameters for Range

[mhasham]

Hello Folks,

I am getting a run-time error 2391 (Field F68 doesn't exist in the destination table)....I know why this error is happening however im not sure on how to fix it.
I have a form (Access) where a button is clicked for browsing and locating your Excel file.

I believe the error is occuring because the browse button is selecting the entire spreadsheet, where i need it to only select a range (since there are elemenets that do not need to be appended within the excel file

Followed by a Import button, which appends the data onto a table using the following code:

Private Sub cmdimport_Click()
      Dim importtbl As String, sqlapp As String, answer As String
      Dim ctl As Control
      
      For Each ctl In Me.Controls
         Select Case ctl.ControlType
            Case acComboBox, acTextBox
              If IsNull(ctl.Value) Then
                 MsgBox "Please fill in all the boxes"
                 GoTo cmdimport_Click_Exit
              End If
         End Select
      Next ctl
      
      
      importtbl = Me!lbldb
      DoCmd.RunMacro "testdelete"
      
      DoCmd.RunSQL "delete * from tbl_standardtemp"
      'import dataset into temp table
      'check if table standardtemp is empty
      
 If TableName.Value = "tbl_standardQs" Then
      DoCmd.RunSQL "delete * from tbl_standardtemp"
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_standardtemp", importtbl, True

Followed by and append code "INSERT INTO (list all fields) "SELECT (fields)" "FROM (table)

I Guess what i'm asking is if there is a range or specifation i can set to only look at fields up a certain point in the excel file that the data is appending....upto column BP (68th field - where the error is happening), example "A1:BP100"?

Any help would be greatly appreciated.

 

[Darren Bartrup]

I usually open the XL file and update the range headers to something accepted by Access, give my data range a name, save the file as a temporary file and use TransferSpreadsheet to import that.

It's a bit convoluted - took me a couple of hours to code a perfect import and a couple of more weeks to take in to account all the errors that could occur during manual data entry to the spreadsheet but it hasn't failed me yet (but has produced many error reports saying the data is rubbish).