Results 1 to 4 of 4
  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Splitting A Recordset

    I have a table in Access that has roughly 800 rows. I am in need of turning rows to columns for an Export to Excel so I was creating a recordset to parse the data and Export to Excel. I have this part set, and can export all 800 rows in the proper format and very quick I might add.


    We now have an added twist of the vendor system that we upload these .xlsx files to can only accept a .xlsx with a max of 45 columns. So my .xlsx with 800 columns is auto rejected due to the column limitation.

    Would it be a more feasible solution to parse the master recordset into sub recordset every 45 rows (since we are turning rows to columns), or would it be easier to export the entire recordset to Excel, then allow access to control Excel and every 45 columns parse the data to a new workbook?

    Actually - upon further thought - I *think* one would be able to split the recordset while still within access and generate multiple tables for every 45 records, then export those tables 1 by 1 to Excel....



    Gurus?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Certainly. I am sure there are a variety of ways to accomplish.

    If you are writing out to Excel one field to one cell at a time, have a counter variable that increments in the loop. When it reaches 45, create a new sheet or workbook, reset the counter, repeat.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by June7 View Post
    Certainly. I am sure there are a variety of ways to accomplish.

    If you are writing out to Excel one field to one cell at a time, have a counter variable that increments in the loop. When it reaches 45, create a new sheet or workbook, reset the counter, repeat.

    This is the syntax I am using to iterate my recordset and export the data to Excel - how would I add a counter to add a new worksheet since I am adding the worksheet and setting it to a variable, and adding the export column to a variable etc?

    Code:
    Set excelApp = CreateObject("Excel.Application")
    Set xlWB = excelApp.Workbooks.Add
    Set xlWS = xlWB.Worksheets(1)
    Set xlc = xlWS.Range("A2")
    
    
    Set rs = CurrentDb.OpenRecordset("Select [OrderNum], [OrderBy] FROM [Dsys] WHERE [ItemID] = '1528' AND [AExists] = 'Yes' ORDER BY [ItemID]", dbOpenDynaset)
    Debug.Print rsSub.RecordCount
    colNo = 1
    Do While Not rs.EOF
       For Each fld In rs.Fields
    	   excelApp.Cells(2, colNo).Value = fld.Value
    	 colNo = colNo + 1
       Next fld
       rs.MoveNext
    Loop

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This worked for me:

    Code:
    Dim ExcelApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim rs As DAO.Recordset
    Dim colNo As Integer
    Dim fld As Field
    Dim x As Integer
    Dim z As Integer
    Dim wsCount As IntegerSet ExcelApp = CreateObject("Excel.Application")
    Set xlWB = ExcelApp.Workbooks.Add
    ExcelApp.Visible = True
    Set rs = CurrentDb.OpenRecordset("SELECT FirstN, LastN FROM Umpires;", dbOpenDynaset)
    colNo = 1
    x = 1
    z = 1
    wsCount = xlWB.Worksheets.Count
    Do While Not rs.EOF
        For Each fld In rs.Fields
            ExcelApp.Cells(2, colNo).value = fld.value
            colNo = colNo + 1
            x = x + 1
            If x > 45 Then
                x = 1
                colNo = 1
                z = z + 1
                If z > wsCount Then xlWB.Worksheets.Add After:=xlWB.Worksheets(xlWB.Worksheets.Count)
                xlWB.Worksheets("Sheet" & z).Activate
            End If
        Next fld
        rs.MoveNext
    Loop
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-22-2015, 09:57 AM
  2. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  3. How to Select second Recordset from first recordset
    By FrustratedAlso in forum Programming
    Replies: 28
    Last Post: 05-10-2012, 05:45 PM
  4. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums