Export error

[Arman]

I have upgraded from Office 2003 to Office 2007 and after the upgrade I
started getting "Could not find installable ISAM" error message when
exporting a file to Excel. The VBA code that does the export is as follows:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Some String", _
FileName

Any suggestions would be greatly appreciated.

 

[Klatuu]

acSpreadsheetTypeExcel3 Is a little out of date. I haven't used 2007, but in
2003, I believe it is acSpreadsheetTypeExcel9

 

[Arman]

Klatuu,

Thank you!!! Made my day Spent an hour yesterday trying to figure out
what was wrong.

 

[Klatuu]

Just a tip. You can leave that argument blank and it will use whichever
version of Excel is installed.

 

[Arman]

Klatuu,

May be you could answer this question for me, when I run my queries the
result gets put on a different worksheet of the same file. with access 2003,
the result was appened to the same worksheet. Is there way to change this
behaviour? I use the spreadsheet as an input file for a different application
and it accepts one worksheet format?

Much appreciated!!!

 

[Klatuu]

Can you post the full code. What you are describing is not normal.

 

[Arman]

HI Klatuu:

Here is the code that does the exporting:

'Export 2 queries to excel spreadsheet for the data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Export Query One", _
FileName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Export Query Two", _
FileName
Else
DoCmd.Close
GoTo Exit_cmdExport_Click

Before the upgrade the result of both of these queries would get put on the
same worksheet. Now, I get two worksheets created in the same xls file
Query_One and Query_Two.

Thanks!

 

[Klatuu]

I am suprised it would put both queries in the same worksheet in 2003.
My experience in 2003 is that if you use the same file name and don't
specifiy a Range argument, each query ends up in a different spreadsheet
within the workbook. I have not used 2007, so I don't know how it works.

You can specify a range in TransferSpreadsheet, but the problem would be
that the second transfer would not know where the last row of the first is to
put it in the correct row. Maybe you do a count of the records in the first
and programmatically determine the range for the second.

 

[Arman]

I was still using acSpreadsheetTypeExcel3 with 2003, may be that was the
reason it was putting the results on the same worksheet. I'll give it a try,
thanks again!