Data from Excel to Access

[Arno R]

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

[Tanis]

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.

[Arno R]

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.

[Robert]

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.