Excel automation fail

[septimus]

I have a program I'm running in Access 2007 that automates Excel. It
needs to create a large number of columns in one worksheet (somewhere
in the neighborhood of 670).

The problem is that it keeps cutting me off after 256 columns. I did
some research and found that versions prior to Excel 2007 max out at
256. So I changed this line of code:

Set objXLApp = CreateObject("Excel.Application")

to this:

Set objXLApp = CreateObject("Excel.Application.12")

But I'm still encountering the same problem. I'm running Excel 2007
and I'm specifiying E2K7 in my initialization statement. Why is it
still creating an E2K3 document??

Thanks for your help.

 

[Jim Cone]

What does your code look like that creates the workbook?

 

[septimus]

Well, I found the problem in the code. I needed to change this:

objXLApp.ActiveWorkbook.SaveAs strPath, -4143

to this:

objXLApp.ActiveWorkbook.SaveAs strPath, 51

But now I have another question. Some of the people who will be
receiving the Excel workbook this code produces are running Excel 2003
or earlier. Is it possible to save my 670 column xlsx file as a xls
file for distribution? Or will that chop off all the columns after
#256?

Thanks.

 

[Gord Dibben]

2003 and earlier versions will show only 256 columns.

The others will be chopped.


Gord