Access 97 query Numeric Field Overflow. qry links to excel sheet

[hey yer barred]

Hi All

I have a query set up in a Access 1997 database which runs fine under the query tab and run manually. The query is a append query which takes data from a Excel sheet and puts it into a table in the database.

When I run it through coding, I keep getting a run-time erro 3349 'Numeric field OverFlow'. Does anyone know how to stop this or fix it please?

I know I could use the transfersheet code but there is 7 tabs on this workbook, and the code doesnt let me select a tab. The workbook is on a shared drive as is put there by someone else each day, so its impossible for me to save it on the sheet I want....plus I need 2 sheets out of it!

Thanks for your help
HYB

 

[RoryA]

You can specify a range on a particular worksheet as an argument to the transferspreadsheet method, if that helps?

 

[hey yer barred]

yeah, that sounds likes a winning plan to me then...
could you please post a example?

 

[Joe4]

Rory's suggestion is exactly what you want to do. I do this myself quite often, and can attest that it works quite well.

Name each of your ranges that you want to import (Insert -> Name -> Define in Excel). Then, used that named Range when importing (you can specify named range instead of Sheet). That way, no matter what sheet the workbook is saved on, the import will work.

The only "caveat" is if the size of your range is not static, you might need some Excel VBA to automatically resize your range so that you get all your data.

 

[RoryA]

It'd just be something like:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    "Employees","C:\blah\myfile.xls", True, "Sheet1!A1:G12"

 

[hey yer barred]

Thank you both for your help, its much appreciated!

For future ref incase anyone else gets this error in the future, it is caused by blank cells in the Excel sheet. From what I can gather if there is a entire blank column (like my sheet has) or the first few cells are blank it causes this numeric overflow. No surprise that MS have not put a fix or patch in for this!

 

[SydneyGeek]

In that case you'll get the overflow if you have a key defined as Integer, instead of Long. Integer maxes out at 32K, halfway down the worksheet.
The defined range is a good way to avoid importing blank rows and columns.

Denis