OutPut To File size limit

[Guest]

Hi

I have problem when using macro to export my data to excel.
Error message pop out saying that :
"There are too many rows to output, based on the limitation specified by the
outputformat or by microsoft access"

However, if I still can use a select query to bring the data, total row is
17733.

can any one help me with it please ?

Regards
J

 

[Arvin Meyer [MVP]]

Check the version of Excel. Earlier versions allowed only 16K rows. If
that's not the case, there may be an internal limiter, which you can get
around by writing 2 or more queries to divide and export the data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Arvin,

I think you might be right, I am running excell 97.

could you explain a bit more on how to write 2 or more queries to divide and
export the data.
Regards
Jeffry

 

[Tom Wickerath]

Hi Jeffry,

You mentioned using a macro. Are you using the OutputTo action? This is indeed limited to 16 K
rows, even in current versions of Excel. Try using the TransferSpreadsheet action instead. I use
macros as little as possible, but here is a sample VBA procedure that I use for a QBF (Query by
Form) form, to export records displayed in a subform. Perhaps this will be useful to you. Watch
for word wrap. I removed some indenting, in an effort to minimize any word wrap issues.


Private Sub cmdExportToExcel_Click()
On Error GoTo ProcError

Dim strPath As String
Dim lngRecordCount As Long
Const conFileName As String = "UnitData.xls"

lngRecordCount = Me.subQueryByForm.Form.Recordset.RecordCount
strPath = CurrentProject.Path

'Limit is 65536, but you need to reserve one row for the column
' headings if HasFieldNames = true.
If lngRecordCount > 65535 Then
MsgBox "There are too many records to export." & vbCrLf _
& "The maximum limit is 65,535 records.", vbCritical, "Too Many Records..."
Else
DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="qryQBF", _
FileName:=strPath & "\" & conFileName, HasFieldNames:=True

MsgBox "The selected data has been exported to the file " & conFileName & vbCrLf _
& "in the folder:" & vbCrLf & strPath, vbInformation, "Export Complete..."

End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdExportToExcel_Click event procedure..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
____________________________________________________


Arvin,

I think you might be right, I am running excell 97.

could you explain a bit more on how to write 2 or more queries to divide and
export the data.
Regards
Jeffry

____________________________________________________

 

[Arvin Meyer [MVP]]

Instead of a single select query as the source of the data for the macro,
use criteria to limit the data. Say there's a date field in your data. You
might use a criteria of:

<#1/1/2005#

in 1 query, and:

#12/31/2004#

in a second query, then use both of those queries as the source (the same
way you'd use a table) for 2 macros instead of 1.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access