Access transferSpreadsheet Error 1

[DoniFromMars]

We’ve been using a macro to import Excel spreadsheets into an access 2002 database. After 5 months of this we found an error on a report. It turns out that Access 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 of data (one row is converted to field names). This happens when using Transferspreadsheet in a macro and in VBA. The same result is produced when using the 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. I would imagine that when we save it, it is saved with a different version of Excel than it was created with. However, I can’t imagine how Access would let this happen. By the way, the spreadsheets come via internet from a third party. We have no control over what they use to create it. We’d greatly appreciate any ideas. Thanks.

We’re running
Access 2002 SP3
Excel 2002 SP3
VB 6.3

 

[Shirley]

Did you change the specification for the import and export procedures?

 

[DoniFromMars]

Nothing changed. Just open spreadsheet manually, click save, then run the import macro or vba module and all is well. (Of course this process is supposed to be automated.)

 

[Shirley]

Check the macro and check the TransferSpreadsheet property window and see if you have a Speciaification Name?

 

[PHV]

Could you please post the actual VBA code doing the transfer ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DoniFromMars]

no specification.
Here is the VBA

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "schedvb", "P:\sched.xls", True


Here is the macro
On Premises
TransferSpread Transfer Type: Import
sheet


Spreadsheet Type: Microsoft Excel 8-10
Table Name: On premises
File Name: P:\Prodution\imported
files\onprem.xls
Has Field Names: Yes
Range:
On Premises

 

[PHV]

I don't play with macro.
For VBA you may try this:
Dim oXL As Object
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Open "P:\sched.xls"
oXL.ActiveWorkbook.Save
oXL.Quit
Set oXL = Nothing
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "schedvb", "P:\sched.xls", True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DoniFromMars]

Thanks. I tried out your code and it works. This will give us a temporary band aid to keep the app going. If anyone has any idea about why this is happening I'd love to hear it . I have to know. Who knows what else is being affected by the same issue. Please. anyone??