Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    10

    Question Unanswered: Export Access Report Excel/Word by value

    Hi guys,
    i am facing a challenge with report.
    I need to get values from Access separated.
    I have Report that will contain thousands of rows. Lets say each 400 row will have the same value in first Column.

    So first 400 rows with the same value (lets say 1) in first column, different in other columns.
    Second 400 rows different value (lets say 2) in first column, different in other columns.

    Question is: is it possible to export this report to multiple tables in excel depends on first column value?
    I wouldnt like to export whole report to the one excel sheet and then separating it into sheets based on these values from first column with macro. (or if you know macro that could do that easily i will appreciate it)

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    I keep a tbl of queries to to export into 1 workbook. But you can look at this...

    Code:
    Private Sub ExportXlTabs(ByVal pvQry)
    Dim i As Integer
    Dim vTab, vFile
    
       'verify the output qry
    If pvQry = "" Then
      MsgBox "Missing Query"
      Exit Sub
    End If
    
    vFile = "c:\Export.xls"
    KillFile vFile          'erase any prev. existing version
    
    HrGlass
    For i = 1 To 4
        Set rst = CurrentDb.OpenRecordset(pvQry & i)
        With rst
              vTab = .Fields("Block").Value & ""
              DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, pvQry, vFile, True, vTab
        End With
    Next
    'OpenNativeApp vFile
    Set rst = Nothing
    HrGlass False
    End Sub

Tags for this Thread

Posting Permissions

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