J
JoeA2006
I need to export results from multiple queries to a single Excel work sheet.
However I only need certain columns from the queries to be exported to an
Excel template. The problem is I don't want the rows from the criteria
columns (Location and Month) showing up in the spread sheet detail,but I need
them to select the correct data. This is the code I am using to export the
results of 2 queries to a spreadsheet. I am passing a Location and a period
paramter from a form.
The only columns I actually need are Description and Amount from the query.
I am trying to avoid creating a new set of queries for this Excel project.
Public Function ExportDetail(Locn As String, Mnyr As String) As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdf1 As DAO.QueryDef
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim intPrdCol As Integer
Dim intPrdRow As Integer
Dim objXLApp As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set db = CurrentDb
Set qdf = db.QueryDefs("qryHdrData")
qdf.Parameters("LOCATION") = Locn
qdf.Parameters("ENTRYPER") = Mnyr
Set rs = qdf.OpenRecordset(dbOpenDynaset)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXLApp = New Excel.Application
With objXLApp
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 6), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
.Cells.Font.Bold = True
.Cells.Font.Size = 7
End With
End With
End If
Set db = CurrentDb
Set qdf1 = db.QueryDefs("qryInventoryAdj")
qdf1.Parameters("ENTRYPER") = Mnyr
qdf1.Parameters("LOCATION") = Locn
Set rs1 = qdf1.OpenRecordset(dbOpenDynaset)
intPrdCol = rs1.Fields.Count
If rs1.RecordCount > 0 Then
rs1.MoveLast: rs1.MoveFirst
intPrdRow = rs.RecordCount
Set objXLApp = New Excel.Application
With objXLApp
.Visible = True
' Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(intMaxRow, 5), .Cells(2, 3)).CopyFromRecordset rs1
End With
End With
End If
End Function
However I only need certain columns from the queries to be exported to an
Excel template. The problem is I don't want the rows from the criteria
columns (Location and Month) showing up in the spread sheet detail,but I need
them to select the correct data. This is the code I am using to export the
results of 2 queries to a spreadsheet. I am passing a Location and a period
paramter from a form.
The only columns I actually need are Description and Amount from the query.
I am trying to avoid creating a new set of queries for this Excel project.
Public Function ExportDetail(Locn As String, Mnyr As String) As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdf1 As DAO.QueryDef
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim intPrdCol As Integer
Dim intPrdRow As Integer
Dim objXLApp As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set db = CurrentDb
Set qdf = db.QueryDefs("qryHdrData")
qdf.Parameters("LOCATION") = Locn
qdf.Parameters("ENTRYPER") = Mnyr
Set rs = qdf.OpenRecordset(dbOpenDynaset)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXLApp = New Excel.Application
With objXLApp
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 6), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
.Cells.Font.Bold = True
.Cells.Font.Size = 7
End With
End With
End If
Set db = CurrentDb
Set qdf1 = db.QueryDefs("qryInventoryAdj")
qdf1.Parameters("ENTRYPER") = Mnyr
qdf1.Parameters("LOCATION") = Locn
Set rs1 = qdf1.OpenRecordset(dbOpenDynaset)
intPrdCol = rs1.Fields.Count
If rs1.RecordCount > 0 Then
rs1.MoveLast: rs1.MoveFirst
intPrdRow = rs.RecordCount
Set objXLApp = New Excel.Application
With objXLApp
.Visible = True
' Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(intMaxRow, 5), .Cells(2, 3)).CopyFromRecordset rs1
End With
End With
End If
End Function