Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

OutPut To File size limit

Discussion in 'Information Technology' started by J, Jul 28, 2006.

  1. J

    J
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Punjab wheat output to be highest in 7 years (Reuters via Yahoo! India News) Breaking News May 15, 2008
    Sikh News Punjab fears shortfall of 2.4m tons in wheat output (Dawn) Breaking News Apr 7, 2008
    Sikh News Punjab fastener makers trim output by 30% on rising cost (The Hindu) Breaking News Mar 10, 2008
    Sikh News Profiled US Sikh Throws 'Know Me' Challenge Breaking News Jul 25, 2016
    Hard Talk Sikh Musician Racially Profiled At US Restaurant Hard Talk Jul 6, 2016

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    "J" <J@discussions.microsoft.com> wrote in message
    news:9BFAC613-780B-42F3-884D-40ECD9AEA2D0@microsoft.com...
    > 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
     
  4. J

    J
    Expand Collapse
    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

    "Arvin Meyer [MVP]" wrote:

    > 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
    >
    > "J" <J@discussions.microsoft.com> wrote in message
    > news:9BFAC613-780B-42F3-884D-40ECD9AEA2D0@microsoft.com...
    > > 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

    >
    >
    >
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    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
    ____________________________________________________

    "J" <J@discussions.microsoft.com> wrote in message
    news:4C3AFED1-1522-4FBF-97E5-E03F05FB54E3@microsoft.com...

    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]" wrote:

    > 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



    ____________________________________________________

    > "J" <J@discussions.microsoft.com> wrote in message
    > news:9BFAC613-780B-42F3-884D-40ECD9AEA2D0@microsoft.com...
    > > 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

    >
    >
    >
     
  6. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    "J" <J@discussions.microsoft.com> wrote in message
    news:4C3AFED1-1522-4FBF-97E5-E03F05FB54E3@microsoft.com...
    > 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]" wrote:
    >
    > > 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
    > >
    > > "J" <J@discussions.microsoft.com> wrote in message
    > > news:9BFAC613-780B-42F3-884D-40ECD9AEA2D0@microsoft.com...
    > > > 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

    > >
    > >
    > >
     

Share This Page