Hi All,
I have created a excel userform which pulls the data from sql server table on the basis of the parameters selected on the userform.
Following are the details of the same.
1) Mydata - Table which contains the data which I want to pull.
2) Country_Region Mapping - Table to populate listbox1 with country column from this table and used as a criteria in sql statment for pulling data from Mydata table.
3) Cost Element Mapping - Table to populate listbox2 with FSI_LINE3_code column from this table and used as a criteria in sql statment for pulling data from Mydata table.
4) Cost Center Mapping - Table to populate listbox4 with Sub Product UBR Code column from this table and used as a criteria in sql statment for pulling data from Mydata table.
Following is the code which I have as of now. Basically there are three joins required in the sql statement.
Join 1 : [VBA]mydata.[Company Code] = [country_Region mapping].[Company Code] 'Join Type : INNER JOIN [/VBA]
Join 2 : [VBA] mydata.[Cost Center] = [Cost Center Mapping].[Cost Center]'Join Type : INNER JOIN [/VBA]
Join 3 : [VBA] mydata.[Unique Indetifier 1] = [Cost Element Mapping].[CE_SR_NO]'Join Type : INNER JOIN [/VBA]
Below is the code which I have so far in my Userform background which works fine with the first join. I have bolded \ highlighted the sql statment in which I need help to incorporate other two joins and get the data from mydata on the basis of that :
[VBA]Private Sub CommandButton5_Click()
Dim selection As String
' Get the selected products escaping single quotes
'selection = Replace(UserForm2.listbox4.Value, "'", "''")
Dim lItem As Long
For lItem = 0 To ListBox4.ListCount - 1
If ListBox4.Selected(lItem) = True Then
selection = selection & "'" & Replace(ListBox4.List(lItem), "'", "''") & "',"
End If
Next
selection = Mid(selection, 1, Len(selection) - 1)
' Setup connection string
Dim connStr As String
connStr = "Provider=SQLOLEDB.1;Password=adminL;User ID=*****;Integrated Security=SSPI;" _
& "Persist Security Info=True;Initial Catalog=XXXXXX;" _
& "Data Source=XXXXXXXX"
' Setup the connection to the database
Dim connection As ADODB.connection
Set connection = New ADODB.connection
connection.ConnectionString = connStr
' Open the connection
connection.Open
' Open recordset.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = connection
Cmd1.CommandText = "SELECT * FROM dbo.mydata t1 INNER JOIN dbo.[Cost Center Mapping] t2 ON t1.[Cost Center] = t2.[Cost Center] AND t2.[Sub Product UBR Code] IN (" & selection & ")"
Set Results = Cmd1.Execute()
' Clear the data from the active worksheet
Cells.Select
Cells.ClearContents
' Add column headers to the sheet
headers = Results.Fields.Count
For iCol = 1 To headers
Cells(1, iCol).Value = Results.Fields(iCol - 1).Name
Next
' Copy the resultset to the active worksheet
Cells(2, 1).CopyFromRecordset Results
' Stop running the macro
MsgBox "Data Extraction Successfully Completed"
Unload Me
End Sub[/VBA]
Thanks a lot for your help in advance.