Importing data from Access to Excel, but I need to vary the table from Access

[Liz L.]

I've created a macro to import data from Access. The database I am
using contains over 30 tables and I'd like to be able to automate the
import process. I can't seem to create a variable that will substitute
for the table name so that I can choose which table I import at any
given time. Here is an example of the code I have generated.

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=D:\Documents and
Settings\Class2007\My Documents\Wave Data.mdb;DefaultDir=D:\Documents
and Settings\" _
), Array( _
"Class2007\My Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `127`.ID, `127`.Year, `127`.Month, `127`.Day,
`127`.Hour, `127`.Depth, `127`.Hmo, `127`.DTp, `127`.Atp, `127`.tmean,
`127`.wvdmn, `127`.wv, `127`.wsp, `127`.wdir" & Chr(13) & "" & Chr(10)
& "FROM `D:\Documents and Setting" _
, _
"s\Class2007\My Documents\Wave Data`.`127` `127`" & Chr(13) &
"" & Chr(10) & "WHERE (`127`.Year= " & Year1 & ")" & Chr(13) & "" &
Chr(10) & "ORDER BY `127`.Month, `127`.Day, `127`.Hour" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With

Anywhere that it says '127' I'd like to make it a variable to use an
input to change it. It takes too much time to manually change the
table name each time.

Thanks,

Liz L.

 

[Tom Ogilvy]

s = "Wave Data.mdb"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=D:\Documents and
Settings\Class2007\My Documents\" & s &";DefaultDir=D:\Documents
and Settings\" _
), Array( _
"Class2007\My Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `127`.ID, `127`.Year, `127`.Month, `127`.Day,
`127`.Hour, `127`.Depth, `127`.Hmo, `127`.DTp, `127`.Atp, `127`.tmean,
`127`.wvdmn, `127`.wv, `127`.wsp, `127`.wdir" & Chr(13) & "" & Chr(10)
& "FROM `D:\Documents and Setting" _
, _
"s\Class2007\My Documents\" & s & "`.`127` `127`" & Chr(13) &
"" & Chr(10) & "WHERE (`127`.Year= " & Year1 & ")" & Chr(13) & "" &
Chr(10) & "ORDER BY `127`.Month, `127`.Day, `127`.Hour" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With

 

[Liz L.]

I don't want the variable to be the database but the table itself. So
the number 127 within the SELECT command, all of the data is within the
database file of "Wave Data.mdb" but in different tables.

Thanks,
Liz L.

 

[Tom Ogilvy]

mm = "127"

"s\Class2007\My Documents\Wave Data`.`" & mm & "` `127`"