Link to home
Start Free TrialLog in
Avatar of gvilbis
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.

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of psdesignadmin
psdesignadmin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Martin Liss
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.