gvilbis
asked on
Getting SQL Query Result to Excel
Hello All,
I have created an excel macro which connects to a DB server, gets data from sheet1 and uses it to parameterize the query.
The problem is to paste the data to excel. here is the code.
I have created an excel macro which connects to a DB server, gets data from sheet1 and uses it to parameterize the query.
The problem is to paste the data to excel. here is the code.
Sub sql_query()
Application.ScreenUpdating = False
Dim Code_Number As String
Dim Color As String
Dim usedRowCount
Dim conn1 As New Connection
Dim rec As New Recordset
Dim comm As New Command
Dim Str
Set ws = ThisWorkbook.Worksheets("CDF Style Color")
conn1.Open "Provider=MSDASQL;DRIVER=SQL SERVER;SERVER=192.168.1.1;111;DNS=DB;UID=SA;PWD=sa;DATABASE=db"
Set comm.ActiveConnection = conn1
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To 50 'LR
ThisWorkbook.Sheets("CDF Style Color").Activate
codenum = Cells(i, 1)
clr = Cells(i, 2)
Str = "Select code, color, upc, upc2, upc3, upc4, upc5, upc6, upc7, upc8, upc9, upc10, upc11, upc12, upc_prepack from ivtf where code ='" & codenum & "' and Color = '" & clr & "'" '& Chr(34)
comm.CommandText = Str
rec.Open comm
Sheets("Style&Color_Match").Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).CopyFromRecordset rec
rec.Close
Next
Rows(1).Delete
Columns("A:AZ").Select
Selection.Sort key1:=[C1], order1:=xlAscending, key2:=[A1], order2:=xlAscending, Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom
Application.DisplayAlerts = False
Columns("A:A").Select
Application.ScreenUpdating = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.