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 excelHow 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