Extract data from SQL Server table into excel using Listbox Selection as criteria

[abhay_547]

Hi All,

I have the below macro which I have recorded for importing the data from SQL Server table. I have a userform in which I have list box I am populating that listbox with some values from another sql server table. Now what I am doing is that while importing the data from sql table I select a criteria now I want the user to select multiple items in listbox and my below macro should consider that selection and extract the data according. As of now below mentioned code works fine with single selection in listbox but when I select multiple items in my listbox. It shows error. Please help.

Sub sqldataextract()
Dim Product As String
Dim CostElement As String
CostElement = frmwarehouse.TextBox1.Value
Product = frmwarehouse.ListBox4.Value
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER=SQL Native Client;SERVER=XXXXXXXXX;UID=admin;PWD=*****;APP=Microsoft Office XP;WSID=XXXXXXXX" _
        ), Array(";DATABASE=meta_data;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT mydata.CAC, mydata.Year, mydata.""Cost Element"", mydata.""Cost Element Name"", mydata.Name, mydata.""Cost Center"", mydata.""Company Code"", mydata.""Unique Indentifier 1"", ""Cost Center mapping"".""Produ" _
        , _
        "ct UBR Code"", ""Cost Element Mapping"".FSI_LINE2_code" & Chr(13) & "" & Chr(10) & "FROM sap_data.dbo.""Cost Center mapping"" ""Cost Center mapping"", sap_data.dbo.""Cost Element Mapping"" ""Cost Element Mapping"", sap_data.dbo.mydata myda" _
        , _
        "ta" & Chr(13) & "" & Chr(10) & "WHERE mydata.""Unique Indentifier 1"" = ""Cost Element Mapping"".CE_SR_NO AND mydata.""Cost Center"" = ""Cost Center mapping"".""Cost Center"" AND ((""Cost Center mapping"".""Product UBR Code""='" & Product & "') AND (""" _
        , "Cost Element Mapping"".FSI_LINE2_code='" & CostElement & "'))")
        .Name = "Query from mydatanew"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Currently my listbox's MultiSelect property is set to 0. If I set it to 1 or 2 the above macro doesn't work.

Thanks for your help in advance.