Combining multiple tables of similar type

[vbaInet]

That's quite peculiar.

If it's consistently adding a row at the very top there are a couple of options.

First method:
1. Linking the spreadsheet to Access, as you probably already have it
2. This means that the field names will be something like F1, F2, F3 etc, but that's absolutely fine
3. Create an UPDATE query to exclude the first two records

Second method:
1. Open the Excel file in code
2. Use EntireRow.Delete of the Cells object to delete the first row, along the lines of:

objExl.Cells(1).EntireRow.Delete

... that'll delete the first row.
3. Close and save the workbook then import

Third method:
1. Open the spreadsheet in code
2. Select the range you want to copy in code and copy it. At this point it's in the Office clipboard so it can be shared between applications.
3. Turn off Access warnings
4. Open a form (in Datasheet view) that's bound to a staging table and paste using one of the DoCmd.RunCommand actions.
5. Turn on Access warnings

Fourth method:
1. Explore the use of ADO to open it as a recordset where it will regard the top row as the header. This will be one of the parameters of the connection string
2. Copy the recordset into an array using GetRows method
3. Copy the array into the Windows clipboard
4. Turn off Access warnings
5. Paste into datasheet form (bound to a staging table as explained in previous method) in Access
6. Delete first two rows
7. Turn on Access warnings

 

[Thales750]

This is the entire code for Method 2 from vbaInet above:

  Dim xls     As Excel.Application
     Dim wkb     As Excel.Workbook
     Dim wks     As Excel.Worksheet
     
     Set xls = New Excel.Application
     Set wkb = xls.Workbooks.Open("D:\YourPath\YourSpreadSheet.xls")
     Set wks = wkb.Worksheets(1)
     
     'objExl.Cells(1).EntireRow.Delete
      xls.Cells(1).EntireRow.Delete
    
     'wks.Name = "My New Name"
     wkb.Close True
     
     Set wks = Nothing
     Set wkb = Nothing
     
     xls.Quit
     
     Set xls = Nothing