Unanswered: Export Access Report Excel/Word by value
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)
I keep a tbl of queries to to export into 1 workbook. But you can look at this...
Private Sub ExportXlTabs(ByVal pvQry)
Dim i As Integer
Dim vTab, vFile
'verify the output qry
If pvQry = "" Then
MsgBox "Missing Query"
vFile = "c:\Export.xls"
KillFile vFile 'erase any prev. existing version
For i = 1 To 4
Set rst = CurrentDb.OpenRecordset(pvQry & i)
vTab = .Fields("Block").Value & ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, pvQry, vFile, True, vTab
Set rst = Nothing