Export error

A

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

Klatuu

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

Arman

Klatuu,

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

Klatuu

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

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

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

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top