Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

No export to Excel worksheet (2000/XP)

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
rauerd
Asked:
rauerd
  • 6
  • 5
  • 2
1 Solution
 
bruintjeCommented:
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
 
rauerdAuthor Commented:
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
 
bruintjeCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rauerdAuthor Commented:
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
 
bruintjeCommented:
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
 
rauerdAuthor Commented:
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
 
bruintjeCommented:
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
 
rauerdAuthor Commented:
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
 
rauerdAuthor Commented:
This Question needs to be deleted because Microsoft has a problem here, and we can't find a work around.

Thank You,
rauerd
0
 
bruintjeCommented:
can be done is CS
0
 
rauerdAuthor Commented:
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
 
ComTechCommented:
As per request via email from CS@EE this question will be placed in PAQ.

Regards,
ComTech
CS Admin @ EE
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now