mm = "127"

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


--
Regards,
Tom Ogilvy
"Liz L." wrote in message
oups.com...
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(Arra y( _
"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.