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

Data from Excel to Access

P: n/a
Hi all,

I need to transfer a lot of (denormalized) data from Excel To Access.
Data is totally wrong formatted for my needs. I need to transform rows and columns.
I also need to be able to specify different ranges to get what I want
So I need range A1:E34 in one able and I need range A39:P50 in another table.
After that I still will have to do some 'data-massage' in Access ...

IIRC I did see code here recently to import data AND transform rows-columns.

Thanks in advance
Arno R
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
You can use the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"TableName", "Pathname.xls", False, "TestSheet!a1:g17"

The False argument is if the sheet has field names or not. The final
argument specifies the sheet name and range.

Nov 13 '05 #2

P: n/a
Thanks Tanis but the major problem is the 'transform rows and columns'.
Any other idea's ?

Arno R

"Tanis" <dn*****@accuride.com> schreef in bericht news:11**********************@o13g2000cwo.googlegr oups.com...
You can use the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"TableName", "Pathname.xls", False, "TestSheet!a1:g17"

The False argument is if the sheet has field names or not. The final
argument specifies the sheet name and range.

Nov 13 '05 #3

P: n/a
I don't think you can do it in one step. I'm working on the same thing.
What I did was upload the spreadsheet into an intermediate table as it is
with transferspreadsheet, then do an append query to move that into the
final table in the proper format. It seems to be working okay.

Robert

"Arno R" <ar***********@tiscali.nl> wrote in message
news:1114534067.1ddb6703a16ed32607d6eba0618fc5e8@t eranews...
Thanks Tanis but the major problem is the 'transform rows and columns'.
Any other idea's ?

Arno R

"Tanis" <dn*****@accuride.com> schreef in bericht
news:11**********************@o13g2000cwo.googlegr oups.com...
You can use the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"TableName", "Pathname.xls", False, "TestSheet!a1:g17"

The False argument is if the sheet has field names or not. The final
argument specifies the sheet name and range.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.