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

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.

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

--
Regards,
Tom Ogilvy

"Liz L." <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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 wrote:
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "Liz L." <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >

mm = "127"

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


--
Regards,
Tom Ogilvy
"Liz L." <[email protected]> wrote in message
news:[email protected]...
> 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.
>