Solved

split Data TransferSpreadsheet When row exceed 65K limit

Posted on 2013-10-31
8
929 Views
Last Modified: 2013-12-05
I am looking for code that will look at the row count of the query results and if it exceeds 65k  rows then split it between existing and a new worksheet within the workbook that is created from Code  

Since this version of 2003 has this limitation and I am using export to EXCEL  I need to modify this code to handle the splitting of the data

Thanks,

K
see below >>>>>>>>>>>

    DoCmd.SetWarnings (False)
'>>>>>>>>>>>>>>>>>This is the query that returns over 65K    
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryREPORT:DailyTitaniumShipments", sRptFilepath, True
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryREPORT:DailyTitaniumShipments_Revenue", sRptFilepath, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryREPORT:DailyTitaniumShipments_PrimusCutShape", sRptFilepath, True

    DoCmd.SetWarnings (True)

    SysCmd acSysCmdSetStatus, "Running " & ReportName & ": Formatting report . . ."


    ' open Excel to add additional tabs of data and to format
    Set xla = New Excel.Application
    Set xlb = xla.Workbooks.Open(FileName:=sRptFilepath)
  
'>>>>>>>>>>>>>>>>>>>>>>>>>Modify Code Here  -  If Row count over 65k
Set xls = xlb.Worksheets(1)
    xls.Name = "Detail"
    xls.Range("A2").Select
    xla.ActiveWindow.FreezePanes = False
    xla.ActiveWindow.FreezePanes = True
    Call FormatReport2(FormatSheet:=xls)
  '>>>>>>>>>>>>>>>>>>>>>>>>>  

    ' re-format column "H"
    With xls.Range("H:H")
    .NumberFormat = "0.00%"
        End With

    Set xls = xlb.Worksheets(2)
    xls.Name = "Revenue"
    xlb.Worksheets(2).Activate
    xls.Range("A2").Select
    xla.ActiveWindow.FreezePanes = False
    xla.ActiveWindow.FreezePanes = True
    Call FormatReport2(FormatSheet:=xls)

    Set xls = xlb.Worksheets(3)
    xls.Name = "BarShapeOrders"
    xlb.Worksheets(3).Activate
    xls.Range("A2").Select
    xla.ActiveWindow.FreezePanes = False
    xla.ActiveWindow.FreezePanes = True
    Call FormatReport2(FormatSheet:=xls)
  
    ' re-format column "H"
    With xls.Range("H:H")
    .NumberFormat = "0.00%"
        End With


    ' save and close workbook
    xlb.Worksheets(1).Activate
    xlb.Close SaveChanges:=True
    xla.Quit
]

Open in new window

0
Comment
Question by:Karen Schaefer
8 Comments
 
LVL 57
ID: 39614665
You'll need to open a recordset on the query, open the spreadsheet in code as your doing, then populate the rows/sheets as needed with a loop.

 You won't be able to use the Transferspreadsheet method to do this.

Jim.
0
 

Author Comment

by:Karen Schaefer
ID: 39614674
JIm,

Are you suggesting that I populate 1 record at a time?  Since this qry is humungous  this will really slow down the process, correct?

K
0
 
LVL 57
ID: 39614731
Basically yes.   You open a record set, then fill in the Excel spreadsheet one cell at a time from the recordset.   When you run out of room on one sheet, you insert another sheet.   That would be slower, but I don't think it would be as slow as you think.

As an alternative (and I've never tried this, but I'm pretty sure it would work), would be to add a row counter, then execute multiple Transferspreadsheet commands to block the data out into 65535 row chunks, each to a seperate spreadsheet file.

Then with automation, open the first  and for each addtional file, open that workbook and copy the sheet into the current workbook.  That would allow you to still use the transferspreadsheet command.

Jim.
0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 39614752
export query to text or csv and then use below code to import text:
 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

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Karen Schaefer
ID: 39614911
Is it possible to include this an call it from with Access instead of EXcel?  If so, What pieces should be include in my code above?  Please keep in mind I am not using text files but a complex query to get the results, do I need to export this query into a text file then imported into Excel via Access VBA Code?

K
0
 
LVL 57
ID: 39614965
<<Is it possible to include this an call it from with Access instead of EXcel? >>

 You certainly can drive this from the Access side.  Just as you are working with the spreadsheet in terms of formatting, you can have a loop to create and fill sheets.

<< Please keep in mind I am not using text files but a complex query to get the results, do I need to export this query into a text file then imported into Excel via Access VBA Code?>>

  There's really no need to do that since you will have the recordset data available.

<<If so, What pieces should be include in my code above?  >>

  Dim db as DAO.Database
  Dim rs as DAO.Recordset
 
  set db = CurrentDB()
  set rs = db.OpenRecodset("qryREPORT:DailyTitaniumShipments")

 ' open Excel to add additional tabs of data and to format
    Set xla = New Excel.Application
    Set xlb = xla.Workbooks.Open(FileName:=sRptFilepath)
    Set xls = xlb.Worksheets(1)
   
    xls.Name = "Detail"

    ' Fill the spreadsheet.  Use a new sheet if > 65535 rows
    lngSheetCount = 1
    lngRecordCount = 1
    lngRow = 1

    Do until rs.EOF
         ' Here you can do one of two things
         ' Place each field in a cell directly
         ' concatenate all the fields and place them in a single cell
         ' I would do the first:
         xls.Cells(lngRow, 1) = rs!<some field>
         xls.Cells(lngRow, 2) = rs!<some field>

        lngRow = lngRow + 1
       
        ' Do we need another sheet?
        If lngRow >65535 then
            xlb.Worksheets.Add
            lngSheetCount = lngSheetCount + 1
            Set xls = xlb.Worksheets(lngSheetCount)
            xls.Name = "Detail " & lngSheetCount
            lngRow = 1
        End If

        lngRecordCount = LngRecordCount + 1
        rs.Movenext
   Loop

 That should do it.    Yoy may want to do your formatting before you move onto the next sheet.

Jim.
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 39615052
You can use the CopyFromRecordset method in Excel to get data from Access.  You might be able to create queries, each with a range of records not to exceed the size limit, and then import each recordset in turn to a different sheet.  The advantage of this method is that it lets you place the data exactly where you want in the worksheet.  Here is some code to work with this method:

Public Sub GetDataFromAccessDAO()
'Created by Helen Feddema 23-Feb-2010
'Last modified by Helen Feddema 23-Feb-2010

On Error GoTo ErrorHandler

   Dim strDatabaseNameAndPath As String
   Dim strRecordSource As String
   Dim dbe As DAO.DBEngine
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim lngCount As Long
   Dim rng As Excel.Range
   Dim strPrompt As String
   Dim strTitle As String
   Dim strSearch As String
   Dim strLastName As String
   
   strRecordSource = "qryNewContacts"
   strDatabaseNameAndPath = "G:\Documents\ExpertsExchange\ExpertsExchange Sample Code.mdb"
   Set dbe = DAO.DBEngine
   
   'Connect to database with password
   'Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
      Options:=False, _
      ReadOnly:=True, _
      Connect:=";pwd=xyxyxyx")
   
   'Connect to database without password
   Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
      Options:=False, _
      ReadOnly:=True)
   Set rst = dbs.OpenRecordset(strRecordSource, dbOpenDynaset)
   Set rng = Application.Sheets(1).Range("G1")
   rng.CopyFromRecordset rst
   
   rst.Close
   dbs.Close
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in GetDataFromAccessDAO procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Closing Comment

by:Karen Schaefer
ID: 39699823
thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now