Using chBox & VBA for dynamic Export to Excel

[BustyAshley]

I'm currently using the Code below to export a table from Access into a new workbook in excel.

Is there a way to modify this code to do a couple things;

Part A: I want to put a cbBox on my form tab that has a contract (First Column on master table), then use that as an "activate filter" on the Master Table and just export that detail for that one specific contract.

Part B: There are six other tables; table1, table2, table3, table4, table5, table6... I want this same cbBox to filter each of these tables and export the results into the same workbook as Part A, then name the worksheets in the workbook from the table it was taken from.

The end Result, would be a 7 tab workbook, Master Table; table1, table2 table3, table4, table5, table6, showing only the contract selected in the cbBox

Private Sub Command313_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Master Table", dbOpenSnapshot)
 'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
 Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
 'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
 'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs
 'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With
 oApp.Visible = True
oApp.UserControl = True
'Close the Database and Recordset
rs.Close
db.Close
 End Sub

 

[MarkK]

In your Part A, you want to change this line . . .

Set rs = db.OpenRecordset("[COLOR="DarkRed"]Master Table[/COLOR]", dbOpenSnapshot)

. . . and rather than the table name, provide a SQL SELECT statement that has a WHERE clause drawing data from the combo in question, something like . . .

Private Const SQL As String = _
   "SELECT * FROM MasterTable WHERE SomeField = " 

Private Sub Command313_Click()
   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset(SQL & Me.cbBox) [COLOR="Green"]'criteria from cbBox[/COLOR]
   . . .

See what's going on there?

For your Part B, a question: Do your table 1-6 have exactly the same structure?

 

[BustyAshley]

All 6 tables have different columns, and different structures. The only thing that brings the 6 of them together is "Contract". Each Table has a Column Labeled "Contract". The master table just slams these tables together like;

Contract A / Table 2 / $5
Contract A / Table 3 / $6

 

[BustyAshley]

In your Part A, you want to change this line . . .

Set rs = db.OpenRecordset("[COLOR=darkred]Master Table[/COLOR]", dbOpenSnapshot)

. . . and rather than the table name, provide a SQL SELECT statement that has a WHERE clause drawing data from the combo in question, something like . . .

Private Const SQL As String = _
   "SELECT * FROM MasterTable WHERE SomeField = " 

Private Sub Command313_Click()
   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset(SQL & Me.cbBox) [COLOR=green]'criteria from cbBox[/COLOR]
   . . .

See what's going on there?

For your Part B, a question: Do your table 1-6 have exactly the same structure?

The table names are

Master Table
Table1
Table2
Table3
Table4
Table5
Table6

All the tables have a column named "Contract"

I need to set rs = Master table with column "Contract" filtered by Me.cbBox

 

[MarkK]

I need to set rs = Master table with column "Contract" filtered by Me.cbBox

Yeah, so I would design the query that does that in the query design grid. Tweak it there, including a trial WHERE clause, then copy that text to your module, then splice in the Me.cbBox value when the user clicks the button.