Hello Excel VBA experts,
I got a macro recorded from Excel VBA that extract data from access file.mdb. How do i subsititue user prompt this string `C:\test.mdb` under CommandText = Array(). I would like to have a user prompt different file.mdb everytime. what is symbol ` mean in Excel VBA? please help, thank you
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0,
Source:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:\test.mdb;DefaultDir=C:\;DriverId=25;FIL=MS
Access;MaxB" _
),
Array("ufferSize=2048;PageTimeout=5;")),
Destination:=Range("$A$1")). _
QueryTable
.CommandText = Array(
_
"SELECT Program.`Program
Name`, Program.`Program Desc`, Program.`Program Unique`,
Program.`Program DB`, Operator.`Operator ID`, Operator.`Operator
Unique`, `Device Under Test`.`Device ID`, `Device Unde" _
, _
"r Test`.Notes, `Device
Under Test`.`Device Under Test Unique`, Data_vD.`Test Unique`,
Data_vD.Exclude, Data_vD.`Total Time`, Data_vD.Cycle, Data_vD.`Loop
Counter #1`, Data_vD.`Loop Counter #2`, Data_v" _
, _
"D.`Loop Counter #3`,
Data_vD.Step, Data_vD.`Step time`, Data_vD.Current,
Data_vD.Voltage, Data_vD.Power, Data_vD.`Instantaneous Amps`,
Data_vD.`Instantaneous Volts`, Data_vD.`Instantaneous Watts`, Dat"
_
, _
"a_vD.`Amp-Hours`,
Data_vD.`Watt-Hours`, Data_vD.`Assignable Variable 1`,
Data_vD.`Assignable Variable 2`, Data_vD.Mode, Data_vD.`Data
Acquisition Flag`" & Chr(13) & "" & Chr(10) & "FROM
`C:\test.mdb`.Data_v" _
, _
"D Data_vD,
`C:\test.mdb`.`Device Under Test` `Device Under Test`,
`C:\test.mdb`.Operator Operator, `C:\test.mdb`.Program Program"
_
)
.RowNumbers =
False
.FillAdjacentFormulas =
False
.PreserveFormatting =
True
.RefreshOnFileOpen =
False
.BackgroundQuery =
True
.RefreshStyle =
xlInsertDeleteCells
.SavePassword =
False
.SaveData = True
.AdjustColumnWidth =
True
.RefreshPeriod = 0
.PreserveColumnInfo =
True
.ListObject.DisplayName
= "Table_Query_from_MS_Access_Database"
.Refresh
BackgroundQuery:=False
End With
Columns("J:J").Select
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Add
Key:=Range( _
"Table_Query_from_MS_Access_Database[[#All],[Test Unique]]"),
SortOn:= _
xlSortOnValues,
Order:=xlAscending, DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet4").ListObjects( _
"Table_Query_from_MS_Access_Database").Sort
.Header = xlYes
.MatchCase = False
.Orientation =
xlTopToBottom
.SortMethod =
xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-12
Range("I2").Select
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Add
Key:=Range( _
"Table_Query_from_MS_Access_Database[[#All],[Device Under Test
Unique]]"), _
SortOn:=xlSortOnValues,
Order:=xlAscending, DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet4").ListObjects( _
"Table_Query_from_MS_Access_Database").Sort
.Header = xlYes
.MatchCase = False
.Orientation =
xlTopToBottom
.SortMethod =
xlPinYin
.Apply
End With
End Sub
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0,
Source:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:\test.mdb;DefaultDir=C:\;DriverId=25;FIL=MS
Access;MaxB" _
),
Array("ufferSize=2048;PageTimeout=5;")),
Destination:=Range("$A$1")). _
QueryTable
.CommandText = Array(
_
"SELECT Program.`Program
Name`, Program.`Program Desc`, Program.`Program Unique`,
Program.`Program DB`, Operator.`Operator ID`, Operator.`Operator
Unique`, `Device Under Test`.`Device ID`, `Device Unde" _
, _
"r Test`.Notes, `Device
Under Test`.`Device Under Test Unique`, Data_vD.`Test Unique`,
Data_vD.Exclude, Data_vD.`Total Time`, Data_vD.Cycle, Data_vD.`Loop
Counter #1`, Data_vD.`Loop Counter #2`, Data_v" _
, _
"D.`Loop Counter #3`,
Data_vD.Step, Data_vD.`Step time`, Data_vD.Current,
Data_vD.Voltage, Data_vD.Power, Data_vD.`Instantaneous Amps`,
Data_vD.`Instantaneous Volts`, Data_vD.`Instantaneous Watts`, Dat"
_
, _
"a_vD.`Amp-Hours`,
Data_vD.`Watt-Hours`, Data_vD.`Assignable Variable 1`,
Data_vD.`Assignable Variable 2`, Data_vD.Mode, Data_vD.`Data
Acquisition Flag`" & Chr(13) & "" & Chr(10) & "FROM
`C:\test.mdb`.Data_v" _
, _
"D Data_vD,
`C:\test.mdb`.`Device Under Test` `Device Under Test`,
`C:\test.mdb`.Operator Operator, `C:\test.mdb`.Program Program"
_
)
.RowNumbers =
False
.FillAdjacentFormulas =
False
.PreserveFormatting =
True
.RefreshOnFileOpen =
False
.BackgroundQuery =
True
.RefreshStyle =
xlInsertDeleteCells
.SavePassword =
False
.SaveData = True
.AdjustColumnWidth =
True
.RefreshPeriod = 0
.PreserveColumnInfo =
True
.ListObject.DisplayName
= "Table_Query_from_MS_Access_Database"
.Refresh
BackgroundQuery:=False
End With
Columns("J:J").Select
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Add
Key:=Range( _
"Table_Query_from_MS_Access_Database[[#All],[Test Unique]]"),
SortOn:= _
xlSortOnValues,
Order:=xlAscending, DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet4").ListObjects( _
"Table_Query_from_MS_Access_Database").Sort
.Header = xlYes
.MatchCase = False
.Orientation =
xlTopToBottom
.SortMethod =
xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-12
Range("I2").Select
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").ListObjects(
_
"Table_Query_from_MS_Access_Database").Sort.SortFields.Add
Key:=Range( _
"Table_Query_from_MS_Access_Database[[#All],[Device Under Test
Unique]]"), _
SortOn:=xlSortOnValues,
Order:=xlAscending, DataOption:=xlSortNormal
With
ActiveWorkbook.Worksheets("Sheet4").ListObjects( _
"Table_Query_from_MS_Access_Database").Sort
.Header = xlYes
.MatchCase = False
.Orientation =
xlTopToBottom
.SortMethod =
xlPinYin
.Apply
End With
End Sub
Get Answers For Free
Most questions answered within 1 hours.