No export to Excel worksheet (2000/XP)

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

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.

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

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.

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
https://www.experts-exchange.com/questions/20314306/Exporting-to-Specific-Worksheet.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

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

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

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!

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

Thank You,
rauerd

can be done is CS

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!

bruintje,

https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msaccess&qid=20330719

CT