OutputTo Bug

[David]

Access 2003 (11.6355.6360) SP1
Windows 2000 (5.00.2195) SP4

I have a query that returns 35,155 rows of data.

I've been able to successfully export this data to Excel by going through
the menu:

* File > Export...
* Set "Save As Type" to Microsoft Excel 97-2003
* Check AutoStart
* Click Export All

I've also been successful in export the data to Excel by creating a macro
with the following arguments:

Action: OutputTo
Object Type: Query
Object Name: [My query name]
Output Format: Microsoft Excel 97-2003(*.xls)
Output File: (Blank)
Auto Start: Yes
Template File: (Blank)
Encoding: (Blank)

That macro runs successfully without error.

When I take that same macro and convert it to VBA, the following line of
code is generated:

DoCmd.OutputTo acQuery, "[My query name]", "MicrosoftExcelBiff8(*.xls)", "",
True, "", 0

When I've tried to use that line of VBA code to export this data to Excel,
I've been running up against the error.

"Error 2306: There are too many rows to output, based on the limitation
specified by the output format or by Microsoft Office Access."

Now, I've done a good amount of searching, and I've seen numbers of postings
that are variation on MS Knowledge Base Article 291951:

You receive a "Too Many Rows" error message when you use "Analyze It with MS
Excel" in Access 2002
http://support.microsoft.com/kb/291951/en-us

This article states, in part, that "For backward compatibility, Microsoft
Access defaults to the Excel 5.0 specification, which is limited to 16,384
rows."

I have been able to work around this issue by having VBA run the macro, i.e.:

DoCmd.RunMacro "mcrExportReport"

I have difficulty believing that VBA has to be subject to this limitation.
Clearly, the macro that generates the code does not.

I suspect that the problem lies in the "MicrosoftExcelBiff8(*.xls)" argument
that is generated by the underlying code that is run by the "Convert Macros
to Visual Basic". It would seem to me to be a simple matter to have that tool
generate the correct code so that it performs the same action that the macro
performs, which it clearly does not.

Shouldn't there be another argument that specifies the version of Excel that
is able to receive more than 16,384 rows? Something like
"MicrosoftExcelBiff11(*.xls)"? Clearly the macro is able to do that behind
the scenes, and this ability should be provided on the VBA level as well.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-bc67-f9aac2a66c87&dg=microsoft.public.access

 

[Sharkbyte]

Actually, if you create an ExportSpecification, you are provided the ability
to select precisely which version of Excel you wish to export to. Thus
eliminating the record limit.

With the macro conversion, Access is left to guess what version you are
after (since you asked Access to write the code for you), so MS has chosen to
write the code at the lowest, common version. Thus version 5.0.

Access 2003 (11.6355.6360) SP1
Windows 2000 (5.00.2195) SP4

I have a query that returns 35,155 rows of data.

I've been able to successfully export this data to Excel by going through
the menu:

* File > Export...
* Set "Save As Type" to Microsoft Excel 97-2003
* Check AutoStart
* Click Export All

I've also been successful in export the data to Excel by creating a macro
with the following arguments:

Action: OutputTo
Object Type: Query
Object Name: [My query name]
Output Format: Microsoft Excel 97-2003(*.xls)
Output File: (Blank)
Auto Start: Yes
Template File: (Blank)
Encoding: (Blank)

That macro runs successfully without error.

When I take that same macro and convert it to VBA, the following line of
code is generated:

DoCmd.OutputTo acQuery, "[My query name]", "MicrosoftExcelBiff8(*.xls)", "",
True, "", 0

When I've tried to use that line of VBA code to export this data to Excel,
I've been running up against the error.

"Error 2306: There are too many rows to output, based on the limitation
specified by the output format or by Microsoft Office Access."

Now, I've done a good amount of searching, and I've seen numbers of postings
that are variation on MS Knowledge Base Article 291951:

You receive a "Too Many Rows" error message when you use "Analyze It with MS
Excel" in Access 2002
http://support.microsoft.com/kb/291951/en-us

This article states, in part, that "For backward compatibility, Microsoft
Access defaults to the Excel 5.0 specification, which is limited to 16,384
rows."

I have been able to work around this issue by having VBA run the macro, i.e.:

DoCmd.RunMacro "mcrExportReport"

I have difficulty believing that VBA has to be subject to this limitation.
Clearly, the macro that generates the code does not.

I suspect that the problem lies in the "MicrosoftExcelBiff8(*.xls)" argument
that is generated by the underlying code that is run by the "Convert Macros
to Visual Basic". It would seem to me to be a simple matter to have that tool
generate the correct code so that it performs the same action that the macro
performs, which it clearly does not.

Shouldn't there be another argument that specifies the version of Excel that
is able to receive more than 16,384 rows? Something like
"MicrosoftExcelBiff11(*.xls)"? Clearly the macro is able to do that behind
the scenes, and this ability should be provided on the VBA level as well.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-bc67-f9aac2a66c87&dg=microsoft.public.access

 

[David]

That's a useful suggestion regarding the Export Specification and I'll keep
it in mind for future reference, but I would still classify it as a
workaround.

The macro to VBA conversion tool shouldn't have to "guess" what format of
Excel it should export to, since the macro Output Format is able to specify
"Microsoft Excel 97-2003(*.xls)".

My goal here is to avoid having to resort to a workaround, or having to
create an extraneous object in order to work around what I still consider to
be a bug. I haven't seen any convincing argument that this is "by design" or
a "feature".

Actually, if you create an ExportSpecification, you are provided the ability
to select precisely which version of Excel you wish to export to. Thus
eliminating the record limit.

With the macro conversion, Access is left to guess what version you are
after (since you asked Access to write the code for you), so MS has chosen to
write the code at the lowest, common version. Thus version 5.0.

Access 2003 (11.6355.6360) SP1
Windows 2000 (5.00.2195) SP4

I have a query that returns 35,155 rows of data.

I've been able to successfully export this data to Excel by going through
the menu:

* File > Export...
* Set "Save As Type" to Microsoft Excel 97-2003
* Check AutoStart
* Click Export All

I've also been successful in export the data to Excel by creating a macro
with the following arguments:

Action: OutputTo
Object Type: Query
Object Name: [My query name]
Output Format: Microsoft Excel 97-2003(*.xls)
Output File: (Blank)
Auto Start: Yes
Template File: (Blank)
Encoding: (Blank)

That macro runs successfully without error.

When I take that same macro and convert it to VBA, the following line of
code is generated:

DoCmd.OutputTo acQuery, "[My query name]", "MicrosoftExcelBiff8(*.xls)", "",
True, "", 0

When I've tried to use that line of VBA code to export this data to Excel,
I've been running up against the error.

"Error 2306: There are too many rows to output, based on the limitation
specified by the output format or by Microsoft Office Access."

Now, I've done a good amount of searching, and I've seen numbers of postings
that are variation on MS Knowledge Base Article 291951:

You receive a "Too Many Rows" error message when you use "Analyze It with MS
Excel" in Access 2002
http://support.microsoft.com/kb/291951/en-us

This article states, in part, that "For backward compatibility, Microsoft
Access defaults to the Excel 5.0 specification, which is limited to 16,384
rows."

I have been able to work around this issue by having VBA run the macro, i.e.:

DoCmd.RunMacro "mcrExportReport"

I have difficulty believing that VBA has to be subject to this limitation.
Clearly, the macro that generates the code does not.

I suspect that the problem lies in the "MicrosoftExcelBiff8(*.xls)" argument
that is generated by the underlying code that is run by the "Convert Macros
to Visual Basic". It would seem to me to be a simple matter to have that tool
generate the correct code so that it performs the same action that the macro
performs, which it clearly does not.

Shouldn't there be another argument that specifies the version of Excel that
is able to receive more than 16,384 rows? Something like
"MicrosoftExcelBiff11(*.xls)"? Clearly the macro is able to do that behind
the scenes, and this ability should be provided on the VBA level as well.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-bc67-f9aac2a66c87&dg=microsoft.public.access

 

[Sharkbyte]

I thought everything that Microsoft products did, whether useful or not, was
a 'feature' and/or 'by design'. =)

That's a useful suggestion regarding the Export Specification and I'll keep
it in mind for future reference, but I would still classify it as a
workaround.

The macro to VBA conversion tool shouldn't have to "guess" what format of
Excel it should export to, since the macro Output Format is able to specify
"Microsoft Excel 97-2003(*.xls)".

My goal here is to avoid having to resort to a workaround, or having to
create an extraneous object in order to work around what I still consider to
be a bug. I haven't seen any convincing argument that this is "by design" or
a "feature".

Actually, if you create an ExportSpecification, you are provided the ability
to select precisely which version of Excel you wish to export to. Thus
eliminating the record limit.

With the macro conversion, Access is left to guess what version you are
after (since you asked Access to write the code for you), so MS has chosen to
write the code at the lowest, common version. Thus version 5.0.

Access 2003 (11.6355.6360) SP1
Windows 2000 (5.00.2195) SP4

I have a query that returns 35,155 rows of data.

I've been able to successfully export this data to Excel by going through
the menu:

* File > Export...
* Set "Save As Type" to Microsoft Excel 97-2003
* Check AutoStart
* Click Export All

I've also been successful in export the data to Excel by creating a macro
with the following arguments:

Action: OutputTo
Object Type: Query
Object Name: [My query name]
Output Format: Microsoft Excel 97-2003(*.xls)
Output File: (Blank)
Auto Start: Yes
Template File: (Blank)
Encoding: (Blank)

That macro runs successfully without error.

When I take that same macro and convert it to VBA, the following line of
code is generated:

DoCmd.OutputTo acQuery, "[My query name]", "MicrosoftExcelBiff8(*.xls)", "",
True, "", 0

When I've tried to use that line of VBA code to export this data to Excel,
I've been running up against the error.

"Error 2306: There are too many rows to output, based on the limitation
specified by the output format or by Microsoft Office Access."

Now, I've done a good amount of searching, and I've seen numbers of postings
that are variation on MS Knowledge Base Article 291951:

You receive a "Too Many Rows" error message when you use "Analyze It with MS
Excel" in Access 2002
http://support.microsoft.com/kb/291951/en-us

This article states, in part, that "For backward compatibility, Microsoft
Access defaults to the Excel 5.0 specification, which is limited to 16,384
rows."

I have been able to work around this issue by having VBA run the macro, i.e.:

DoCmd.RunMacro "mcrExportReport"

I have difficulty believing that VBA has to be subject to this limitation.
Clearly, the macro that generates the code does not.

I suspect that the problem lies in the "MicrosoftExcelBiff8(*.xls)" argument
that is generated by the underlying code that is run by the "Convert Macros
to Visual Basic". It would seem to me to be a simple matter to have that tool
generate the correct code so that it performs the same action that the macro
performs, which it clearly does not.

Shouldn't there be another argument that specifies the version of Excel that
is able to receive more than 16,384 rows? Something like
"MicrosoftExcelBiff11(*.xls)"? Clearly the macro is able to do that behind
the scenes, and this ability should be provided on the VBA level as well.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-bc67-f9aac2a66c87&dg=microsoft.public.access

 

[David]

LOL!!!

-¿O

I thought everything that Microsoft products did, whether useful or not, was
a 'feature' and/or 'by design'. =)

That's a useful suggestion regarding the Export Specification and I'll keep
it in mind for future reference, but I would still classify it as a
workaround.

The macro to VBA conversion tool shouldn't have to "guess" what format of
Excel it should export to, since the macro Output Format is able to specify
"Microsoft Excel 97-2003(*.xls)".

My goal here is to avoid having to resort to a workaround, or having to
create an extraneous object in order to work around what I still consider to
be a bug. I haven't seen any convincing argument that this is "by design" or
a "feature".

Actually, if you create an ExportSpecification, you are provided the ability
to select precisely which version of Excel you wish to export to. Thus
eliminating the record limit.

With the macro conversion, Access is left to guess what version you are
after (since you asked Access to write the code for you), so MS has chosen to
write the code at the lowest, common version. Thus version 5.0.



:

Access 2003 (11.6355.6360) SP1
Windows 2000 (5.00.2195) SP4

I have a query that returns 35,155 rows of data.

I've been able to successfully export this data to Excel by going through
the menu:

* File > Export...
* Set "Save As Type" to Microsoft Excel 97-2003
* Check AutoStart
* Click Export All

I've also been successful in export the data to Excel by creating a macro
with the following arguments:

Action: OutputTo
Object Type: Query
Object Name: [My query name]
Output Format: Microsoft Excel 97-2003(*.xls)
Output File: (Blank)
Auto Start: Yes
Template File: (Blank)
Encoding: (Blank)

That macro runs successfully without error.

When I take that same macro and convert it to VBA, the following line of
code is generated:

DoCmd.OutputTo acQuery, "[My query name]", "MicrosoftExcelBiff8(*.xls)", "",
True, "", 0

When I've tried to use that line of VBA code to export this data to Excel,
I've been running up against the error.

"Error 2306: There are too many rows to output, based on the limitation
specified by the output format or by Microsoft Office Access."

Now, I've done a good amount of searching, and I've seen numbers of postings
that are variation on MS Knowledge Base Article 291951:

You receive a "Too Many Rows" error message when you use "Analyze It with MS
Excel" in Access 2002
http://support.microsoft.com/kb/291951/en-us

This article states, in part, that "For backward compatibility, Microsoft
Access defaults to the Excel 5.0 specification, which is limited to 16,384
rows."

I have been able to work around this issue by having VBA run the macro, i.e.:

DoCmd.RunMacro "mcrExportReport"

I have difficulty believing that VBA has to be subject to this limitation.
Clearly, the macro that generates the code does not.

I suspect that the problem lies in the "MicrosoftExcelBiff8(*.xls)" argument
that is generated by the underlying code that is run by the "Convert Macros
to Visual Basic". It would seem to me to be a simple matter to have that tool
generate the correct code so that it performs the same action that the macro
performs, which it clearly does not.

Shouldn't there be another argument that specifies the version of Excel that
is able to receive more than 16,384 rows? Something like
"MicrosoftExcelBiff11(*.xls)"? Clearly the macro is able to do that behind
the scenes, and this ability should be provided on the VBA level as well.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-bc67-f9aac2a66c87&dg=microsoft.public.access