Solved

split Data TransferSpreadsheet When row exceed 65K limit

Posted on 2013-10-31
8
1,094 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 58
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 58
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 

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 58
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

617 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