Excel Output Error - Newbie Help

[TheScullster]

Hi all

We have an Access2000 application which controls drawing status etc.
One of the features of this app is to output drawings to an Excel table.
Today it seems that we have exceeded some limit in number of records.
When trying to generate an Excel output from a particular search we obtain
the error:
Run Time Error 2306
There are too many rows to output, based on the limitations specified by the
output format or by "Data Manager" (the filename).

The debug window leads to the following code:

DoCmd.OutputTo acOutputTable, "exceltemp", acFormatXLS, "c:\temp.xls", True

Anyone know the reason for this please?
The search states that there are 21800 records returned by this search.

TIA

Phil

 

[Guest]

I believe that the acFormatXLS is an older version of Excel that can handle
only about 16000 records.

Change the code to TransferSpreadsheet which by default outputs to a newer
version of Excel which can handle about 64000 records. However
TransferSpreadsheet does not have AutoStart.

Now what is really strange is that in a macro you can specify the Excel
format when doing an OutputTo.

 

[TheScullster]

Change the code to TransferSpreadsheet which by default outputs to a newer
version of Excel which can handle about 64000 records. However
TransferSpreadsheet does not have AutoStart.

Thanks Jerry, but I am struggling with all this code stuff somewhat and
don't want to cock things up too far!

Below is the code for the section highlighted when the error occurs.
It appears that the data output to the excel file currently is a record set
rather than a named query.
Can you advise how this must be changed to incorporate the
transferspreadsheet action please?


Case 8:
Dim intHalf As Integer
Dim n As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from qrySearchCrownSupplier")

DoCmd.SetWarnings False

intHalf = InStr(1, Me.lstResult.RowSource, "FROM") - 1
DoCmd.RunSQL Left(lstResult.RowSource, intHalf) & " into ExcelTemp " &
Right(lstResult.RowSource, Len(lstResult.RowSource) - intHalf)
'DoCmd.RunMacro "macExcelTemp"
DoCmd.OutputTo acOutputTable, "exceltemp", acFormatXLS, "c:\temp.xls", True
DoCmd.SetWarnings True

'Set objxl = CreateObject("Excel.Application")
'objxl.Visible = True
'Set objwkb = objxl.Workbooks.Open("c:\temp.xls")
'Set objsht = objwkb.Sheets(1)
'n = 2
'objsht.Cells(1, 11) = "Manufactuer"
'Do While objsht.Cells(n, 1) <> ""
'rs.FindFirst ("([no]='" & objsht.Cells(n, 1) & "') and ([ct]='" &
objsht.Cells(n, 4) & "') and ([so]='" & objsht.Cells(n, 7) & "')")
'objsht.Cells(n, 11) = rs("sa")
'n = n + 1
'Loop

'objwkb.Save
'objWkb.Close
'Set objsht = Nothing
'Set objWkb = Nothing
'objxl.Quit
'Set objxl = Nothing

TIA

Phil

 

[Guest]

Try changing the following line from
DoCmd.OutputTo acOutputTable, "exceltemp", acFormatXLS, "c:\temp.xls", True
to
DoCmd.TransferSpreadsheet acExport, 8, "exceltemp", "C:\temp.xls"

Remember to go up to Debug, Compile when done. One other change is that the
spreadsheet will not open automatically as TransferSpreadsheet unfortunately
doesn't support that. There's code out there to open an Excel spreadsheet
from within Access if needed.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Change the code to TransferSpreadsheet which by default outputs to a newer
version of Excel which can handle about 64000 records. However
TransferSpreadsheet does not have AutoStart.

Thanks Jerry, but I am struggling with all this code stuff somewhat and
don't want to cock things up too far!

Below is the code for the section highlighted when the error occurs.
It appears that the data output to the excel file currently is a record set
rather than a named query.
Can you advise how this must be changed to incorporate the
transferspreadsheet action please?


Case 8:
Dim intHalf As Integer
Dim n As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from qrySearchCrownSupplier")

DoCmd.SetWarnings False

intHalf = InStr(1, Me.lstResult.RowSource, "FROM") - 1
DoCmd.RunSQL Left(lstResult.RowSource, intHalf) & " into ExcelTemp " &
Right(lstResult.RowSource, Len(lstResult.RowSource) - intHalf)
'DoCmd.RunMacro "macExcelTemp"
DoCmd.OutputTo acOutputTable, "exceltemp", acFormatXLS, "c:\temp.xls", True
DoCmd.SetWarnings True

'Set objxl = CreateObject("Excel.Application")
'objxl.Visible = True
'Set objwkb = objxl.Workbooks.Open("c:\temp.xls")
'Set objsht = objwkb.Sheets(1)
'n = 2
'objsht.Cells(1, 11) = "Manufactuer"
'Do While objsht.Cells(n, 1) <> ""
'rs.FindFirst ("([no]='" & objsht.Cells(n, 1) & "') and ([ct]='" &
objsht.Cells(n, 4) & "') and ([so]='" & objsht.Cells(n, 7) & "')")
'objsht.Cells(n, 11) = rs("sa")
'n = n + 1
'Loop

'objwkb.Save
'objWkb.Close
'Set objsht = Nothing
'Set objWkb = Nothing
'objxl.Quit
'Set objxl = Nothing

TIA

Phil

 

[TheScullster]

Try changing the following line from
DoCmd.OutputTo acOutputTable, "exceltemp", acFormatXLS, "c:\temp.xls",
True
to
DoCmd.TransferSpreadsheet acExport, 8, "exceltemp", "C:\temp.xls"

Remember to go up to Debug, Compile when done. One other change is that
the
spreadsheet will not open automatically as TransferSpreadsheet
unfortunately
doesn't support that. There's code out there to open an Excel spreadsheet
from within Access if needed.
--

Thanks Jerry
That did the trick

Phil