TransferSpreadsheet with Named Range

[eschaef2]

TransferSpreadsheet (with a named range) HELP

Hello,

Thanks for taking the time to read this thread. I have an issue with the TransferSpreadsheet method that I am curious if others have seen this before, and what kind of work around(s) are available.

My users have a workbook (Limits.xls) that has named ranges within the workbook to identify areas relevant to their field of expertise (Machine1, Machine2, Machine3, etc.).

Each machine can post a condition code that must be mitigated if the total number of posts exceeds a predetermined limit within a pre determined time frame.

Example: Machine1 says 'Maintenance' 3 times in 1 day; the limit is 5- No action is required on Machine1
Machine1 says 'DriveCalReq' 6 times in 1 day; the limit is 3- Action is required for Machine1 (we must recalibrate the drive circuitry)

My dilemma: There are twelve to twenty machines who post up to three hundred unique condition codes (to each type of machine) within a given day. My users are only allowed to change the Limits.xls workbook to refine the number of 'acceptable' hits against any one condition code (for each respective machine) in any one day.
The users are allowed to update their respective 'tables' within the Excel workbook, and I must be able to import their latest alert limits for decision making.
I have utilized named regions within the workbook, but when I try to DoCmd.TransferSpreadsheet <>,<>,<>,<>, "Machine1" ACCESS posts a 'can not find "Machine1"' object error.

Can anyone post an idea as to what I am not properly passing to the TransferSpreadsheet object, and if there is a work around?

Thanks for reading this, and I look forward to any suggestions that may come up.

Best regards,
Eric Schaeffer

 

[hqengint]

If "Machine1" is the name of a sheet in you excel file then you need to put an exclamation point at the end of the sheet name to import the entire sheet. (ex. "Machine1!")
Hope this helps.

 

[Pat Hartman]

You cannot use TransferSpreadsheet to export to a specific range. Here is what Help says:

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.

The only way to export to a specific range is to use OLE. You'll need to find out how to do this from the Help file of Excel. Or search the archives here. I think that examples have been posted.

 

[darpe]

Thanks hqengint, you bet your info helped! I registered mostly to offer my sincere thanks!! My import of an excel worksheet worked fine earlier tonight, then all of a sudden failed. But your advice resolved my error 3011 problem. No 3011 problem again, thanks to you.
Thank you!!
Best of luck to you.
darpe