vba transferspreadsheet drop a row

[DoniFromMars]

We’re trying to transfer Excel spreadsheets into an access 2002 database using VBA in access (transferspreadsheet). The transfer is ignoring the last row and last column of the spreadsheet. If the spreadsheet has 12 columns and 1000 rows the table will have 11 columns and 998 rows (one row is converted to field names). This also happens when using in a macro in access and when using the access wizard to get external data. We also discovered that if we open the spreadsheet and save it (even without making a change) the missing row and column will be imported correctly.The spreadsheets come via internet from a third party. We have no control over what they use to create it. We're getting around the problem by having VBA open and save each spread before the transfer (Thanks again for that reply} We’re running VBA 6 in Access 2002 SP3,Excel 2002 SP3. We’d greatly appreciate any explanation and fix. Thanks.

 

[CautionMP]

DoniFromMars,
I'll take a guess at what's happening and provide one possible solution.

Problem: The third party that supplies the workbook is probably using a third party component (would that make it fourth party?) to create the extract you get in Excel. This third/fourth party component is probably setting the [tt]UsedRange[/tt] of the Excel workbook incorrectly, probably a zero based pointer instead of a one, which causes Access to see the data range incorrectly (losing one row and one column). I'm guessing that when you open, save, and close the workbook Excel resets the [tt]UsedRange[/tt] to acurately represent the data in the workbook.

Work around: When you call [tt]DoCmd.TransferSpreadsheet()[/tt] without a [Range] argument I believe Access looks at the workbook and uses the [tt]UsedRange[/tt] property (probably to speed up the import). If this is the case you could try calling [tt]DoCmd.TransferSpreadsheet()[/tt] and specify the range you want to import, using the last column you know has data and the last row of the worksheet (65536). If your destination table is keyed correctly any empty rows between your last row of data and row 65536 will be ignored.

Here is a sample based on worksheet (Sheet1) with a header row and twelve columns (A - L) of data:

DoCmd.TransferSpreadsheet acImport, , [i][TableName][/i], [i][FileName][/i], True, [b]"Sheet1!A1:L65536"[/b]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)