Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Reading, UK
    Posts
    16

    Unanswered: Export to Excel using OutputTo - too many rows

    I am trying to export the contents of a form to Excel using DoCmd.OutputTo. I get an error 2306 too many rows when I try to export more than about 16000. I can't use TransferSpreadsheet because it will only export a table and I am using a parameterised stored procedure with an Access 2002 adp.

    The ever useful Help says I can also use the name of a query with TransferSpreadsheet but there are no queries because I am using an adp.

    I've found this on the web in a few places with the only solution being to use TransferSpreadsheet. Surely Microsoft have sorted this one out by now, 16000 rows was the limitation for pre-97.

    Any help greatly appreciated!

  2. #2
    Join Date
    Mar 2004
    Location
    Austria
    Posts
    9
    Why you want to write 16000 Records into EXCEL?
    The Limit ist between 15000 and 16000 Records!

  3. #3
    Join Date
    Apr 2004
    Location
    Reading, UK
    Posts
    16

    Thumbs down

    No, my ill-informed friend, the limit is actually 2^14 (16384). This is a throwback to the Office 97 days and have has never been upgraded since by our learned friends at Microsoft.

    The only workaround that I can find (and an altogether more useful reply than yours) is to create a temporary table based on a unique identifier for the user, dump the results and then use the TransferSpreadsheet method on the temp table.

    On a separate note in reply to your first sentence, I work for a financial consultancy and it obviously isn't me personally that wants to export to Excel but a functional requirement from the client.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Perhaps you can write your own function to export it, taking into consideration to check always a counter, when it reaches say 12000 then add new Sheet and start there exporting the rest.

  5. #5
    Join Date
    Mar 2006
    Posts
    2
    Quote Originally Posted by sean_fackrell
    I am trying to export the contents of a form to Excel using DoCmd.OutputTo. I get an error 2306 too many rows when I try to export more than about 16000. I can't use TransferSpreadsheet because it will only export a table and I am using a parameterised stored procedure with an Access 2002 adp.

    The ever useful Help says I can also use the name of a query with TransferSpreadsheet but there are no queries because I am using an adp.

    I've found this on the web in a few places with the only solution being to use TransferSpreadsheet. Surely Microsoft have sorted this one out by now, 16000 rows was the limitation for pre-97.

    Any help greatly appreciated!
    I ran into this to, the users required some crazy big report. This is how I did it. I cut some of the formatting out to make my post a bit small, but you should get the idea. With this method I could fill the xls up to it's limit quickly if I need to. Enjoy.

    Code:
    Public Function ExportXls(strXLSFile As String, strXLSQry As String, _
    strXLSDateField As String, Optional strXLSSecondaryFile As String, Optional strXLSPassword As String) As Boolean
    On Error GoTo Error:
    Const xlUp = -4162
    Const xlNone = -4142
    Const xlNormal = -4143
    Const xlPasteFormats = -4122
    Const xlWhole = 1
    Const xlByRows = 1
    Const xlAscending = 1
    Const xlGuess = 0
    Const xlTopToBottom = 1
    Const xlSortNormal = 0
    Const xlLastCell = 11
    
    Dim objXlsApp As Object
    Dim rsExport As ADODB.Recordset
    
    Set rsExport = New ADODB.Recordset
    Set objXlsApp = CreateObject("Excel.Application")   'create Excel application object
    objXlsApp.Visible = False
    objXlsApp.DisplayAlerts = False
    rsExport.Open strXLSQry, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    If strXLSPassword = "" Then
        objXlsApp.Workbooks.Open FileName:=strXLSFile
    Else
        objXlsApp.Workbooks.Open FileName:=strXLSFile, WriteResPassword:=strXLSPassword
    End If
    
    'Snip
    'Cut out formating stuff here
    'Snip
        
    objXlsApp.ActiveWorkbook.Close
    objXlsApp.Quit
    
    Set objXlsApp = Nothing
    Set rsExport = Nothing
    ExportXls = True
    Exit Function
    
    Error:
    
    If Not objXlsApp Is Nothing Then objXlsApp.Quit
    Set objXlsApp = Nothing
    Set rsExport = Nothing
    ExportXls = False
    End Function

  6. #6
    Join Date
    Mar 2006
    Posts
    2
    Quote Originally Posted by dingleberry
    I ran into this to, the users required some crazy big report. This is how I did it. I cut some of the formatting out to make my post a bit small, but you should get the idea. With this method I could fill the xls up to it's limit quickly if I need to. Enjoy.

    Code:
    Public Function ExportXls(strXLSFile As String, strXLSQry As String, _
    strXLSDateField As String, Optional strXLSSecondaryFile As String, Optional strXLSPassword As String) As Boolean
    On Error GoTo Error:
    Const xlUp = -4162
    Const xlNone = -4142
    Const xlNormal = -4143
    Const xlPasteFormats = -4122
    Const xlWhole = 1
    Const xlByRows = 1
    Const xlAscending = 1
    Const xlGuess = 0
    Const xlTopToBottom = 1
    Const xlSortNormal = 0
    Const xlLastCell = 11
    
    Dim objXlsApp As Object
    Dim rsExport As ADODB.Recordset
    
    Set rsExport = New ADODB.Recordset
    Set objXlsApp = CreateObject("Excel.Application")   'create Excel application object
    objXlsApp.Visible = False
    objXlsApp.DisplayAlerts = False
    rsExport.Open strXLSQry, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    If strXLSPassword = "" Then
        objXlsApp.Workbooks.Open FileName:=strXLSFile
    Else
        objXlsApp.Workbooks.Open FileName:=strXLSFile, WriteResPassword:=strXLSPassword
    End If
    
    'Snip
    'Cut out formating stuff here
    'Snip
        
    objXlsApp.ActiveWorkbook.Close
    objXlsApp.Quit
    
    Set objXlsApp = Nothing
    Set rsExport = Nothing
    ExportXls = True
    Exit Function
    
    Error:
    
    If Not objXlsApp Is Nothing Then objXlsApp.Quit
    Set objXlsApp = Nothing
    Set rsExport = Nothing
    ExportXls = False
    End Function


    I cut the most important line out.

    Code:
    objXlsApp.Cells(5, 1).CopyFromRecordset rsExport

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •