Import Excel File without Specific Rows

[Lipper]

Is there a way to automate a process to import an excel spreadsheet without the first and last couple of rows into Access?

[TheSmileyCoder]

If you do it through VBA you can specify the range to import. Is it always the same rows/range you are importing?

[Lipper]

As of now yes. I am receiving an excel file that will come in the same format periodically.

[NeoPa]

Check out the TransferSpreadsheet method in the help, but the sixth parameter is Range. It will allow you to specify exactly what to include.

[Lipper]

Thanks, I used the following code below, and it works out pretty good.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Worksheet Name", "Path Name", True, "Range"

[Lipper]

Say the "Range" may change from time to time, and I was trying to find an easier way to input the cell range other than directly into the code, any ideas?

[TheSmileyCoder]

Would depend on the specific situation. One way could be to import it all, then delete the offending rows.

Another approach could be to open an excel application via VBA (can be hidden from user), open the sheet, and programmatically set/check the range, provided you have something that computer logic can use to determine the range. Would have to see an example to help you with that.

[NeoPa]

@Lipper
Essentially no. There are ways around it, but they're cumbersome. This was the point of the question in post #2 of course.

[TheSmileyCoder]

You can open the excel sheet, and make a named range, and have the transferspreadsheet function always import the named range.

[Lipper]

I want to thank you both for helping me out with this. One more thought, do any of you know any method that will exclude a number of top rows as opposed to defining a range?

[NeoPa]

For a Range parameter, you either need to know that the range is named, and use that, or you need to be able to specify the range in one of the standard formats, "A1:B2" or "R1C1:R2C2". Clearly you could specify a range that missed the top X rows, but that would leave you required to specify which row to go down to too. You could mix this with an earlier suggestion, to import line X+1 to 65,536, and then delete all records with no data, but without access to the Excel file itself, and with no prior knowledge as to how many lines are there, you will not be in a position to specify a meaningful range.