Filter ADODC records and send to Excel File
Posted on 2004-11-15
I have a project that includes a form 'form1' for data input only and another form 'form2' for viewing the data in a datagrid. I use a combo box that automatically populates with item numbers that I would like to filter for. I can send the data to excel for formatting and printing but I can only send all the data.
How can incorporate my recordset.filter so that I only send the filtered data. Maybe I can use two command buttons to do this. Command1 just sends all the data from the grid and Command2 will send just the filtered data.
Below is the code I am using for connection to Acces and Excel and the code for my Filtering.
Private Sub Command2_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim excel_app As excel.Application
Dim excel_sheet As excel.Worksheet
Screen.MousePointer = vbHourglass
' Open the Access database.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\my documents\programming\bs tree sales\treelotsales1.mdb"
' Select the Access data.
Set rs = conn.Execute("Sales")
' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
' Uncomment this line to make Excel visible.
excel_app.Visible = True
' Open the Excel workbook.
excel_app.Workbooks.Open "c:\my documents\programming\bs tree sales\test2.xls"
' Use the Recordset to fill the table.
Set excel_sheet = excel_app.ActiveSheet
Screen.MousePointer = vbDefault
item1 = Form2.Combo1.Text
Form2.Adodc1.Recordset.filter = "ITEM like '" & item1 & "'"