S
SF
Hi,
I am think of exporting a set of queries to excel but putting each result
set in different sheets, I try to put the code together but I don't know how
to go further, could someone help me to complete this task?
Public Function OutputReport(Qtr As String)
On Error GoTo ProcError
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Stg As String
Dim QryStg As String
Set dbs = CurrentDb
QryStg = "SELECT [2-1-1-A By Qtr].FY, [2-1-1-A By Qtr].Qtr, [2-1-1-A By
Qtr].[Public Session], [2-1-1-A By Qtr].[# of Session]"
QryStg = QryStg & " FROM [2-1-1-A By Qtr] WHERE ((([2-1-1-A By Qtr].Qtr)=" &
Qtr & "));"
Set qdf = dbs.QueryDefs(QryStg)
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
How to transfer the content of the queryset to excel here....
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
'xlApp.Visible = True
'' xlApp.ActiveWorkbook.PrintOut
'' 'xlApp.ActiveWorkbook.Saved = False
'' xlApp.ActiveWorkbook.Close SaveChanges:=False
'' xlApp.UserControl = False
'' 'Application.DisplayAlerts = False
'' xlApp.Quit
ExitProc:
'Cleanup
On Error Resume Next
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure PrintTravel..."
Resume ExitProc
Resume
End Function
SF
I am think of exporting a set of queries to excel but putting each result
set in different sheets, I try to put the code together but I don't know how
to go further, could someone help me to complete this task?
Public Function OutputReport(Qtr As String)
On Error GoTo ProcError
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Stg As String
Dim QryStg As String
Set dbs = CurrentDb
QryStg = "SELECT [2-1-1-A By Qtr].FY, [2-1-1-A By Qtr].Qtr, [2-1-1-A By
Qtr].[Public Session], [2-1-1-A By Qtr].[# of Session]"
QryStg = QryStg & " FROM [2-1-1-A By Qtr] WHERE ((([2-1-1-A By Qtr].Qtr)=" &
Qtr & "));"
Set qdf = dbs.QueryDefs(QryStg)
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
How to transfer the content of the queryset to excel here....
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
'xlApp.Visible = True
'' xlApp.ActiveWorkbook.PrintOut
'' 'xlApp.ActiveWorkbook.Saved = False
'' xlApp.ActiveWorkbook.Close SaveChanges:=False
'' xlApp.UserControl = False
'' 'Application.DisplayAlerts = False
'' xlApp.Quit
ExitProc:
'Cleanup
On Error Resume Next
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure PrintTravel..."
Resume ExitProc
Resume
End Function
SF