Solved

Getting SQL Query Result to Excel

Posted on 2014-12-15
3
68 Views
Last Modified: 2015-01-13
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

0
Comment
Question by:gvilbis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
psdesignadmin earned 500 total points
ID: 40501260
You could use the inbuilt CopyFromRecordSet method once you open the recordsheet.

SheetName.RangeAddress.CopyFromRecordset rst 

Open in new window


A detailed commentary is here.

~Tim
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40546220
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question