Use transferspreadsheet in macro, randomly receive error 2950

[Bobarian]

When using the transferspreadsheet function in a macro I randomly receive an
error 2950. I'm using the macro to export query output into excel so that I
can generate graphs and charts. I'm generating roughly two dozen worksheets
into Excel. The error seems to randomly generate, maybe every 2nd or third
time I run a report. But once it does produce an error message, I have to
completely remove the referenced spreadsheet in order to rerun the macro.

 

[Ken Snell [MVP]]

Macro or VBA code? Show us the details, regardless of which one you're
using.

 

[Bobarian]

It is a Macro, using the 'transferspreadsheet' command to export numerous
queries (mostly crosstabs) into a single macro enabled Excel (.xlsm)
spreadsheet.

I'm using the database to report various parameters on our work population
and generating data to be graphed in excel. I don't have the specific error
in front of me right now but it doesn't ever seem to be consistent except for
2950, and if I go and delete the targeted worksheet that the error occurred

 

[Ken Snell [MVP]]

Ok, but you still have not given us the details, which I requested, for the
macro's steps and arguments for each action.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Bobarian]

Here are specifics of what the macro is doing, along with the error messages.

Action: TransferSpreadsheet
Argument: Export, Excel Workbook, Selected CMFs, G:\CMF
Modelling\Output_File\CMF_Output.xlsm, Yes,

This step is repeated more than 2 dozen times, with the only variation being
the name of the query (in this case, Selected CMFs) becoming the name of the
worksheet in Excel.

Received this error -
Too many fields defined.
Help says Error 3190 - Too many fields defined.
“You tried to perform an operation that involves more than 255 fields.
Reduce the number of fields, and then try the operation again.â€
The query it failed on results in a 30 X 7 spreadsheet, and the macro will
run if I delete the spreadsheet it hung up on and allow the macro to recreate
it.

In action failed box that follows, I get:
Macro name – Output_Creator
Condition – True
Action Name - TransferSpreadsheet
Argument - Export, 10, Enl_Auth_RCMS, G:\CMF
Modelling\Output_File\CMF_Output.xlsm, Yes,
Error Number - 2950

 

[Ken Snell [MVP]]

My first thought is that workbook has a named Range that is the same as the
name of the query that you're exporting when it fails.

Run the macro and when it fails, go to the EXCEL workbook, open it, and
check the list of named Ranges. Does that query name appear in that list?
(If you are not familiar with where to find the named ranges, you click the
down arrow next to the box that shows the current cell reference.)
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Bobarian]

That appears to be the exact problem. How do I go about fixing it? Is there
a way to redefine the named range when executing the transfer?

 

[Ken Snell [MVP]]

You might be able to use Automation to manipulate EXCEL and change the name
of a Range object there, but I'm not familiar with the VBA code needed to do
that. You probably could get an answer to that question in an EXCEL
newsgroup.

I have an article that discusses the use of the Range argument in the
exporting of data to an EXCEL spreadsheet. While this is not what you're
doing, it may provide some insight into the behavior that you're seeing:
Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

My suggestion would be for you to slightly modify the query names that
you're exporting so that they will not match to existing Ranges in the EXCEL
workbook. Try changing the name of the one query that is failing and try
again to see if that resolves the issue.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Kevin Seymour]

I'm having the same problem... sort of. transferspreadsheet was working fine in 2007 access, but was not supported in 2010 access and the was automatically changed to inportexportspreadsheet. Since I strated using 2010 the "Too many fields defined" error keeps appearing while trying to run one macro, but does not appear when I run a similar macro exporting data. What triggers the "Too many fields defined" error?

When using the transferspreadsheet function in a macro I randomly receive an
error 2950. I am using the macro to export query output into excel so that I
can generate graphs and charts. I am generating roughly two dozen worksheets
into Excel. The error seems to randomly generate, maybe every 2nd or third
time I run a report. But once it does produce an error message, I have to
completely remove the referenced spreadsheet in order to rerun the macro.

On Wednesday, September 09, 2009 9:09 PM Ken Snell [MVP] wrote:
Macro or VBA code? Show us the details, regardless of which one you are
using.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/