Ali  Alfaraj

Ali Alfaraj

  • NA
  • 68
  • 108k

Import data from access to excel using VBA & SQL

Feb 5 2013 1:46 AM

 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