Importing from Excel to Access through a query.

[rikklaney1]

OK, I have a question I need to import the data from an Excel spreadsheet but only certain cells. Is there any way I can call out a range of cells in a query and tell it what fields in a table to put those records in?

 

[Frothingslosh]

It might be possible with Power Query or something like that, but it's pretty easy to do just that with a macro or VBA instead.

Is there a specific reason you need to do this as part of a query?

 

[rikklaney1]

Heres a little more data. I have this..

DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel5, _
"Table1", _
"D:\users\shopapps\Documents\SL57 Line Load .xlsm", _
False, _
"b4:h63"

That works, but I also need to put "n4:T63" as the next group of records in the same fields. Any ideas? I'm pretty new at Access and these forums have been a great source to help me learn. Thank you everyone.

 

[Frothingslosh]

The only real way to do what you're asking (that I know of - I may be corrected shortly!) would be to run a transfer for each range separately. On the bright side, the range data is the only thing you'd need to change with each import, and it's quite straightforward to set up a loop to do this.

Is this a one-time deal or will this be a regular occurence?

Also, if the field names in your table aren't going to match the names on the spreadsheet, then what you'll want to do is first load the spreadsheet into a staging table (either in the backend or a side-end (temporary database file)), and then run an append query to copy the data to the final destination.

 

[rikklaney1]

It will be about a once a week occurence but as I said I'm fairly new so I'll have to do some looking on that but at least now I know it's somewhat possible. I can import it to one table then make an append query to rearrange all the data in another table if I'm understandig correctly.

 

[Frothingslosh]

Yep. Pretty standard technique when importing data, especially if the data needs to be cleaned as well.

 

[rikklaney1]

Crap... now taht I stop and think about it it does seem kind of obvious. lol

 

[Frothingslosh]

One thing to keep in mind - if you create the destination table as part of the import, the data type for each field will be determined by the data in the first ten rows of the import. If that doesn't work correctly for your needs, then you're going to need to create the table first - either through VBA or have it as a pre-existing staging table.

There are, naturally, pros and cons for each.