Importing Excel 97 spreadsheet into an Access 97 database (1 Viewer)

[shay]

I'm using the Docmd.Transferspreadsheet method to import data from a spreadsheet which contains several worksheets.

To select the worksheet to import, I have included the worksheet name as the final parameter of the DoCmd.Transferspreadsheet method. The only way I can get the code to work is to include a cell range with the worksheet name eg "Compliance!A1:z1000" but as I have absolutely no idea how much data will be present when the program is up and running, I need to import the entire spreadsheet and not specify a range.

Can anyone help me with this?

 

[Travis]

This is cut from an Article on the MSDN

TransferSpreadsheet Action

...

Range
The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax no longer works in Microsoft Access 97). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.


Note When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

...

 

[boblarson]

You can specify the COLUMNS you need to import and it will take the number of rows with data. I've done it and an example is shown below:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCMS", _
"C:\Data\CMS.xls", True, "Sheet2!A:F"


BL
hth

Dang thing here kept posting a smiley face where my code is. I guess it senses some UBB.


[This message has been edited by boblarson (edited 04-29-2002).]

 

[shay]

Thanks Bob. It would have been nice to get Access to import an entire specified worksheet but being able to specify columns is a reasonable compromise.

shay