By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Import Excel File without Specific Rows

P: 5
Is there a way to automate a process to import an excel spreadsheet without the first and last couple of rows into Access?
Mar 16 '10 #1
Share this Question
Share on Google+
10 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
If you do it through VBA you can specify the range to import. Is it always the same rows/range you are importing?
Mar 16 '10 #2

P: 5
As of now yes. I am receiving an excel file that will come in the same format periodically.
Mar 16 '10 #3

NeoPa
Expert Mod 15k+
P: 31,494
Check out the TransferSpreadsheet method in the help, but the sixth parameter is Range. It will allow you to specify exactly what to include.
Mar 16 '10 #4

P: 5
Thanks, I used the following code below, and it works out pretty good.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  2.     "Worksheet Name", "Path Name", True, "Range"
Mar 16 '10 #5

P: 5
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?
Mar 16 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 16 '10 #7

NeoPa
Expert Mod 15k+
P: 31,494
@Lipper
Essentially no. There are ways around it, but they're cumbersome. This was the point of the question in post #2 of course.
Mar 17 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
You can open the excel sheet, and make a named range, and have the transferspreadsheet function always import the named range.
Mar 17 '10 #9

P: 5
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?
Mar 17 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
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.
Mar 17 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.