Solved: Import Multiple Tables

[lifeson]

How do I get Excel to import multiple access tables into excel
I have the following to do 1 table
[vba]Sub GetTable()

Dim DBFullName As String
Dim Cnct As String, src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

' Database information
DBFullName = "C:\Program Files\FieldSalesApplication\PremierPlusField.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset

With Recordset
' Filter
src = "SELECT * FROM TblQuote"
.Open Source:=src, ActiveConnection:=Connection
' Write the field names
Sheets("tblQuote").Select
Cells.Clear
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
Set Recordset = Nothing
End With[/vba]

But without replicating all that for every table I want is there an easier/more logical approach

[T-J]

Just make the GetTable subroutine general in which you pass 2 arguments:

1. the name of the table used in the SQL statement
2. the sheet where the recordset should be written to
[vba]Sub GetTable(tbl As String, sht As Worksheet)
'replace relevant lines of code with these below
src = "SELECT * FROM " & tbl
sht.Select
End Sub[/vba]
Call the subroutine like this:
[vba]GetTable "TblQuote", Worksheets("tblQuote")
GetTable "tblSuppliers", Worksheets("sheet2")[/vba]

[lifeson]

Thanks TJ
Great 1st post

I modified it slightly to be

[vba]Sub GetTable(ws As Worksheet, filter As String)[/vba]

an example to calll the routine is

[vba]GetTable Worksheets("TblQuoteLine"), "SELECT * FROM TblQuoteLine " & _
"WHERE (((TblQuoteLine.LeadNumber) = " & leadID & ") " & _
"And ((TblQuoteLine.quoteID) = '" & quoteID & "') " & _
"And ((TblQuoteLine.ResourceType) = '" & resourceID & "')) " & _
"ORDER BY TblQuoteLine.EntityTypeID;"[/vba]

Thanks again