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.

On Dec 23, 2:52*pm, septimus <ovengra... (AT) yahoo (DOT) com> wrote:
I entered, using Excel 2010, the number 1 in ColA1 and A1+1 in Col2
and copied that to the last column, I ended up with 16,384 columns.

However, Access can only have 256 columns (fields). You can find that
information by entering "Specifications" in the Access Help Search
bar. That's the count you bombed out on. So I wonder if it is your
logic that is at fault or what you really are attempting to do that is
the issue.

I read mostly and write occasionally, to specific cells using VBA on a
daily basis at work. I know you can create a 670 column spreadsheet
although a file that size with 50K rows would be a "get and drink a
cup of coffee" while waiting for it to load.

Go to this site http://access.mvps.org/access/_vti_b...dll/search.htm
and search for "Excel" Maybe something there will help you.

septimus wrote:
Patrick to believe, perhaps correctly, that you are trying to read the data
into an Access table, which of course has a limit of 256 fields. I'm not so
sure that is your problem. Give us a little more information.

Also, I seem to remember seeing a thread here (or somewhere else) a while
ago that talked about the inability to specify the Office version in the
CreateObject statement. I seem to recall that a registry change was required
to force CreateObject to use a specific version of Office application
(Excel, in this case) being automated. Do a google for automating specific
versions of Excel for details.

Excel 2007 and 2010 do allow over 16,000 columns, but Excel 2003 will
only go up to 256. I'm not actually working with an Access table in
this instance, certainly not one with 256 columns.

I found out what I was doing wrong... instead of this:
"objXLApp.ActiveWorkbook.SaveAs strPath, 51" I had this:
"objXLApp.ActiveWorkbook.SaveAs strPath, -4143".

You're right --- it is a "go make coffee" program to run.

Thanks for the help.

On Dec 23, 6:06*pm, Patrick Finucane <patrickfinucan... (AT) gmail (DOT) com>
wrote: