Excel VBA extract access file.mdb to file.csv

[jenny80]

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