thread705-1425791
Have a similar challenge that I initially thought that I could perform using filtering from one MS Excel workbook to multiple Excel templates as referenced by thread707-1622424
The MS Excel workbook was initially populated with the results of a sql server query and then I wanted to filter records to multiple Excel templates.
However, it appears that MS Access is the way to go.
Therefore, one option is to develop one passthrough query in MS Access and export the query results to multiple Excel templates dependent on a particular field.
Or, is it preferable to just create multiple passthrough queries (one for each product) and then export the results to multiple Excel templates?
MS Access Passthrough Query is below
Select Prod.Product_Name,
convert(varchar,Prod.Product_End_Date,101),
Mfg.Bin_Number,
Mfg.Manufacturer_Name,
convert(varchar,Prod.Product_Order_Date,101)
From Product Prod
Inner Join Manufacturer Mfg
on Prod.Product_ID = Mfg.Product_ID
Where Prod.Product_Name in ('Widgets', 'Bolts', 'Washers'...)
and Product_End_Date> '10/31/10'
Group By Prod.Product_Name
Did review the following code but do not know how to modify to accomplish the objective.
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim MyTemplate As String 'template file as used to create mulitiple graphs
Dim MYOutput As String 'Output file buildup
Dim rst As New ADODB.Recordset ' Query/table
Dim Rows As Long
Dim Mcolumn as long
dim mcount as long
'Start with clean file built from template file
MyTemplate = CurrentProject.Path & "\template.xls"
MYOutput = CurrentProject.Path & "\output.xls"
'create blank output file
FileCopy MyTemplate, MyOutput
'Create the Excel Application, Workbook and Worksheet and Database object
Set appExcel = New Excel.Application 'Assigns objects to variables
appExcel.Visible = True 'Makes Excel session visible
Set wbk = appExcel.Workbooks.Open(MyOutput)
rst.Open "SELECT * FROM Table_Query1 ", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Rows = 3 ' start row
mColumn = 4 ' start column
Do Until rst.EOF
With wbk.Sheets("sheet1") ' existing excel worksheet
.Cells(Rows, mcolumn).Value = rst.Fields("Field1").Value ' increment column and row as appropriate
.
.
.End With
rst.MoveNext
Loop
rst.Close
rst.Open "SELECT * FROM Table_Query2 ", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Rows = 3
mColumn = 4
Do Until rst.EOF
With wbk.Sheets("sheet2") ' existing excel worksheet
.Cells(Rows, mcolumn).Value = rst.Fields("Field1").Value ' increment column and row as appropriate
.
.
.End With
rst.MoveNext
Loop
rst.Close
Have a similar challenge that I initially thought that I could perform using filtering from one MS Excel workbook to multiple Excel templates as referenced by thread707-1622424
The MS Excel workbook was initially populated with the results of a sql server query and then I wanted to filter records to multiple Excel templates.
However, it appears that MS Access is the way to go.
Therefore, one option is to develop one passthrough query in MS Access and export the query results to multiple Excel templates dependent on a particular field.
Or, is it preferable to just create multiple passthrough queries (one for each product) and then export the results to multiple Excel templates?
MS Access Passthrough Query is below
Select Prod.Product_Name,
convert(varchar,Prod.Product_End_Date,101),
Mfg.Bin_Number,
Mfg.Manufacturer_Name,
convert(varchar,Prod.Product_Order_Date,101)
From Product Prod
Inner Join Manufacturer Mfg
on Prod.Product_ID = Mfg.Product_ID
Where Prod.Product_Name in ('Widgets', 'Bolts', 'Washers'...)
and Product_End_Date> '10/31/10'
Group By Prod.Product_Name
Did review the following code but do not know how to modify to accomplish the objective.
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim MyTemplate As String 'template file as used to create mulitiple graphs
Dim MYOutput As String 'Output file buildup
Dim rst As New ADODB.Recordset ' Query/table
Dim Rows As Long
Dim Mcolumn as long
dim mcount as long
'Start with clean file built from template file
MyTemplate = CurrentProject.Path & "\template.xls"
MYOutput = CurrentProject.Path & "\output.xls"
'create blank output file
FileCopy MyTemplate, MyOutput
'Create the Excel Application, Workbook and Worksheet and Database object
Set appExcel = New Excel.Application 'Assigns objects to variables
appExcel.Visible = True 'Makes Excel session visible
Set wbk = appExcel.Workbooks.Open(MyOutput)
rst.Open "SELECT * FROM Table_Query1 ", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Rows = 3 ' start row
mColumn = 4 ' start column
Do Until rst.EOF
With wbk.Sheets("sheet1") ' existing excel worksheet
.Cells(Rows, mcolumn).Value = rst.Fields("Field1").Value ' increment column and row as appropriate
.
.
.End With
rst.MoveNext
Loop
rst.Close
rst.Open "SELECT * FROM Table_Query2 ", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Rows = 3
mColumn = 4
Do Until rst.EOF
With wbk.Sheets("sheet2") ' existing excel worksheet
.Cells(Rows, mcolumn).Value = rst.Fields("Field1").Value ' increment column and row as appropriate
.
.
.End With
rst.MoveNext
Loop
rst.Close
