Does TransferSpreadsheet create new sheets if greater than 65356 rows?

[QuietRiot]

Trasfering a large query to Excel will it create new sheets if it reaches Excel 2003's limitation?

DoCmd.TransferSpreadsheet acExport, , QueryName, PathNameFileName, True

 

[boblarson]

No, it will simply fail.

 

[QuietRiot]

No, it will simply fail.

ouch. Is there a work around?

 

[MSAccessRookie]

ouch. Is there a work around?

I believe that you should be able to loop through your recordset and process it in blocks of 64,000 records or less, writing each block to a separate Excel Spreadsheet until the recordset is fully processed. You might even be able to create separate worksheets in a single spreadsheets, but I am not sure about that.

 

[boblarson]

You would have to code for it. Check the recordset count and then go into a spot where you either iterate through using a counter and send it over that way, or go select records based on a count and then transfer that recordset over and then go select another set and transfer that, etc.

 

[QuietRiot]

I'm actually not even using transfersheet but was going to change if it did support Excel limitations.

I'm currently using copyfromrecordset if there is a way to tweak it?

    Set dbs = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    'edit this if template is moved.
    Set objCreateWkb = objXL.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook
    objXL.Visible = False
    Set rstGetExportData = dbs.OpenRecordset("qExport3")
    On Error GoTo ErrClean
    FieldCount = rstGetExportData.Fields.Count
    objActiveWkb.ActiveSheet.Range("A1").Select
   For x = 0 To FieldCount - 1
       With objXL
         .ActiveCell = rstGetExportData.Fields(x).Name
         .ActiveCell.Offset(0, 1).Select
       End With
   Next
    With objActiveWkb.Worksheets("Sheet1")
        .Cells(2, 1).CopyFromRecordset rstGetExportData
        .Columns.AutoFit
        .Cells.HorizontalAlignment = xlLeft
    End With

 

[MSAccessRookie]

I'm actually not even using transfersheet but was going to change if it did support Excel limitations.

I'm currently using copyfromrecordset if there is a way to tweak it?

I do not usually use copyfromrecordset, but I suspect that my earlier suggestion might still apply.

 

[DCrake]

Here is a sub that handles the importing of text files that contain more than 65k records

  Sub LargeFileImport()

      'Dimension Variables
      Dim ResultStr As String
      Dim FileName As String
      Dim FileNum As Integer
      Dim Counter As Double
      'Ask User for File's Name
      FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
      'Check for no entry
      If FileName = "" Then End
      'Get Next Available File Handle Number
      FileNum = FreeFile()
      'Open Text File For Input
      Open FileName For Input As #FileNum
      'Turn Screen Updating Off
      Application.ScreenUpdating = False
      'Create A New WorkBook With One Worksheet In It
      Workbooks.Add template:=xlWorksheet
      'Set The Counter to 1
      Counter = 1
      'Loop Until the End Of File Is Reached
      Do While Seek(FileNum) <= LOF(FileNum)
         'Display Importing Row Number On Status Bar
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & FileName
          'Store One Line Of Text From File To Variable
          Line Input #FileNum, ResultStr
          'Store Variable Data Into Active Cell
          If Left(ResultStr, 1) = "=" Then
             ActiveCell.Value = "'" & ResultStr
          Else
             ActiveCell.Value = ResultStr
          End If
          
          'For Excel versions before Excel 97, change 65536 to 16384
          If ActiveCell.Row = 65536 Then
             'If On The Last Row Then Add A New Sheet
             ActiveWorkbook.Sheets.Add
          Else
             'If Not The Last Row Then Go One Cell Down
             ActiveCell.Offset(1, 0).Select
          End If
          'Increment the Counter By 1
          Counter = Counter + 1
      'Start Again At Top Of 'Do While' Statement
      Loop
      'Close The Open Text File
      Close
      'Remove Message From Status Bar
      Application.StatusBar = False

   End Sub

If you change the methodology frm reading a text file to reading a recordset it will solve your issue.

David

 

[gemma-the-husky]

if there are more than 64K rows, then you need to question whether its worth outputting to excel at all.

one way would be to export a summarised form of the data, that doesnt need 64K + rows.

its all to do with the way you need to inspect your data - and the think about access is, that you very rarely need ot see the whole lot of data - you generally are always working with a subset.