Good day dear members,
I'm using the below VBA code to import data from access to excel.
I would like to set a varialbe for the Well Number parameter that is selected from a combobox I created in excel
How can I modify the below code to achieve that.
------------------------------The code------------------------------------------------------------------
Sheets("Sheet2").Select
Sheet2.Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=O:\SGPED\UNITS\HHPEU\HRDH GOSP-3 Master\Well Rate Tests.accdb;DefaultDir=O:\SGPED\UNITS\HHPEU\HRDH G" _
), Array( _
"OSP-3 Master;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("Sheet2!$A$7")).QueryTable
.CommandText = Array( _
"SELECT Information.Date1, Information.`Well Number`, Information.`BHP (psi)`, Information.`BHT (F)`, Information.`CCA (psi) (PI-0325)`, Information.`TCA (psi) (PI-0325)`, Information.`Upstream T (F)`," _
, _
" Information.`Upstream P (psi)`, Information.`Downstream P (psi)`, Information.`Choke Setting`, Information.`Choke (Auto/Manual)`, Information.`Remote Choke Operability`, Information.`Oil Rate (SBPD)`" _
, _
", Information.`Gas Rate (MMSCF)`, Information.`Water Rate (SBRD)`, Information.Remarks" & Chr(13) & "" & Chr(10) & "FROM `O:\SGPED\UNITS\HHPEU\HRDH GOSP-3 Master\Well Rate Tests.accdb`.Information Information" & Chr(13) & "" & Chr(10) & "WHERE (Information" _
, ".`Well Number`='1409')" & Chr(13) & "" & Chr(10) & "ORDER BY Information.Date1")
.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("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Range("D18").Select
End Sub