Excel VBA extract access file.mdb to file.csv

jenny80

New Member
Joined
Feb 2, 2017
Messages
1
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

Rich (BB code):
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
 
Last edited by a moderator:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top