Solved

No export to Excel worksheet (2000/XP)

Posted on 2002-06-15
13
399 Views
Last Modified: 2008-02-01
Over the last week or so I got help implementing multiple worksheets in Excel via Access 97. Everything was working great - transferring data to many worksheets.

Now, the task was to move to Office 2000 and XP, and guess what? - it doesn't work for either version!!!

I've been using TransferSheet from within Access, and even though Help says you are not suppose to specify a Range when exporting - it works great in Access 97.

I use the following code in a loop for each transfer:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblExportLinks", sDestinationFile, 0, sSheet & "AA1:AW100

I've tried all Excel types, etc. Does anyone know how to export data out to multiple worksheets for 2000/XP?

Thanks!
0
Comment
Question by:rauerd
  • 6
  • 5
  • 2
13 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 7083109
Hi Rauerd, do you get any errors?

and i guess the last quote was cut off in the post?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblExportLinks", sDestinationFile,
0, sSheet & "AA1:AW100" <---

:O)Bruintje


0
 

Author Comment

by:rauerd
ID: 7083958
Bruintje,

On the 2nd TransferSpreadsheet I get: Error 3274, External Table is not in the expected format. AND When I try to open the Excel file I get a fatal error and Excel shuts down - the file is toast.

A typo - I have the last quote in the statement. The code is the same in the 97 version as the 2000/XP versions - just the Excel type is changed from 97 to 8 or 9,

This all looks like MS fixed the feature that slipped through in 97, but there should be a way to easily export multiple sheet data!

Thanks.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7091921
found this on another source:

simply export each query to the same excel spreadsheet and as long as each query is saved as a different name I get seperate sheet tabs for each query in my spreadsheet labelled with thequeries name

or

using Access 2000 and/or Access 2002. You have to export, not "output to", and it has to be Excel 3.0 format. Any other way and it overwrites the existing file.

or

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qry1", "C:/Temp/QueryBoy.xls", True
    DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qry2", "C:/Temp/QueryBoy.xls", True
    DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qry3", "C:/Temp/QueryBoy.xls", True
   
that's all hopefully enough to get you going

:O)Bruintje
0
 

Author Comment

by:rauerd
ID: 7093099
Bruintje,

Yes - That part I have. It's just that in Access97 I've used the following and it works quite well.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblSalesMargins", "C:\Reports\Template.xls", "Links!AA1:AW55", True

This puts data on an Existing worksheet which links to these cells to create a Balance sheet. When it comes to 2000 and XP, this no longer works.

acSpreadsheetTypeExcel97 specifies the format type - in this case version 8. You showed using version 9, which is Access 2000.

All along Microsoft has said in their Help files that you can't specify the Range when Exporting, but it's always worked in Access97. You would have thought that they discovered that and include this feature in 2000 and XP. That's my problem now - Microsoft FIXED the feature out of their newer versions, and now I'm screwed upgrading my program to 2000 or XP.

If this can't be solved, I'll have to export to unique worksheets (as you've shown) and put code in Excel to pull the data in from these sheets to my working sheets.

Thanks - We need to keep digging.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7097088
a bit late but it dawned on me what you're trying to do

export to a range in a sheet?

well this PAQ gives you the way i guess
http://www.experts-exchange.com/msaccess/Q_20314306.html

the code posted there exports to a specific sheet, but from there it should eb easy to export to a specific range

:O)Bruintje
0
 

Author Comment

by:rauerd
ID: 7099728
Correct - I need to specify the sheet and Range.

We just discovered another problem with good ole XP. When I export data to a new worksheet in a workbook that already has several worksheets, many formulas, and lots of VBA - the Transfer totally hoses parts of all the other worksheets, and the new worksheet only has about 80~90% of the data and some of it it is out of order.

It looks like Microsoft totally screwed something up in AccessXP and/or ExcelXP!!!!!!!!!!!!!!!

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:bruintje
ID: 7100036
ok then what about

exporting to a new workbook
and after all the exporting
open the other workbook
then do a copy from there into the right ranges?

if you need help with the code say so, i know this is a crude workaround
0
 

Author Comment

by:rauerd
ID: 7104231
Hi Bruintje,

Sorry for the delay - busy weekend!

That's what it looks like I'm going to have to do - it's just that all this stuff works fine in Access 97, then goes to hell with Access 2000 & XP. And the reason behind the DB in the first place was to simplify things - eliminating the need to do so much manual work with Excel. But . . . .

Any way, I started doing just that, but the first table still screws up, and sometimes messes up Excel completely so the other tables won't export. I wish I could send you the table out of access and the result in Excel. What happens is part of the 39 rows of data gets one of the fields shifted over by three columns. There are no commas, or weird characters in any of the cells - just plain old text or numbers (double type). Yet sometimes half the rows are messed up, but doing the exact same thing in Access97 turns out perfect.

Thank you for all your time, but let's keep this open for a little bit in case one of us, or another, finds something from that marvelous Microsquish!

Thanks!
0
 

Author Comment

by:rauerd
ID: 7122465
This Question needs to be deleted because Microsoft has a problem here, and we can't find a work around.

Thank You,
rauerd
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7122479
can be done is CS
0
 

Author Comment

by:rauerd
ID: 7123695
Hi Bruintje,

Thanks. I called MS on this and they admit to there being a "Slight" bug. I liked their term slight!!!

Any ways - thanks for your time!

0
 

Accepted Solution

by:
ComTech earned 0 total points
ID: 7191756
As per request via email from CS@EE this question will be placed in PAQ.

Regards,
ComTech
CS Admin @ EE
0
 

Expert Comment

by:ComTech
ID: 7191770
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now