How to skip first few rows in Excel when importing data into Access?

1
votes

I am very new to Access 2016 VBA and I have been importing Excel data into an Access table.

But the issue is that I need to skip the first three rows of the Excel sheet, so that I can directly save the Excel data into Access table. How can I achieve that?

Here is the code am using to import the Excel data into a table:

DoCmd.TransferSpreadsheet acImport, , Tbl, SelectedFls, True

And one more thing is that it's not possible to specify the range of Excel cells since it varies.

3
vbams-access
Post more of your code/ - Dean

3 Answers

0
votes

MS Access operates on unordered data sets and, as such, you cannot rely upon the order of records returned by a query, or the order in which data is imported into a table from an external source such as a spreadsheet or text file.

You can only rely on the order of records returned by a query when the ordering is strictly defined with the inclusion of an order by clause.

Therefore, if you wish to continue to use the TransferSpreadsheet method for your task, you will need to determine a method of identifying the records to be omitted which is independent of the order of the imported records. This way, you can import all records into your table and then run a delete query implementing the record identifying logic as its selection criteria.

The only other way that I can see to achieve this would be to interface with the Excel worksheet from MS Access using ActiveX, iterate over the rows in the worksheet, and populate a recordset whilst omitting the first three rows encountered.

0
votes

  • For such workarounds, I've used deletion of first three rows in Excel file. You can record a macro in Excel, and then called preprocesing before importing file in Access.

  • Other option is to used named ranges, you can import them directly to Access.

  • And most suitable solution for you is to use DoCmd.Transferspreadsheet Range specification, as described here - https://docs.microsoft.com/ru-ru/office/vba/api/access.docmd.transferspreadsheet

DoCmd.TransferSpreadsheet acImport, , Tbl, SelectedFls, True, "A4:G12"

In this case, you would import specified range, e.g. from 4th row to 12th.

Some disadvantages are:

0
votes

If you ALWAYS want the first three rows to be skipped you can simply change the starting positions to DoCmd.TransferSpreadsheet acImport, , Tbl, SelectedFls, True, "A4" and then use the code below to take in all inputs until you hit an empty cell. 

DoCmd.TransferSpreadsheet acImport, , Tbl, SelectedFls, True, "A4"
'first line checks if this cell is empty or not
Do While xlc.Value <> ""
    'takes the value of the current cell
    WO_Num(lngColumn) = xlc.Offset(0, 0).Value
    'shifts the selected cells one row downwards
    Set xlc = xlc.Offset(1, 0)
    lngColumn= lngColumn + 1 
Loop