Solved

No export to Excel worksheet (2000/XP)

Posted on 2002-06-15
13
404 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

730 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