L
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.
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.