Execute SQL Query from excel

Question

I'm a bit stuck for a while in a small project to generate results from several sql queries in several excel Sheets, I am trying to use SQL Server 2008 and it's the first time I code VBA I tried this code (for a SQL single query) but I still have compilation problems

Sub New_Feuil1() 
    ThisWorkbook.Activate 

     'First clear the contents from the query
    Worksheets("Feuil1").Select 
    Range("A2").Select 
    Do Until ActiveCell = "" 
        ActiveCell.Offset(1).Select 
    Loop 
    Range("A4", ActiveCell.Offset(-1, 3)).ClearContents 

     'Get reporting date
    ReportingDate = ThisWorkbook.Sheets("Parameters").Range("D1") 

     'Format the value for use in the SQL query
    ReportingDateFor = Format(ReportingDate, "yyyy-mm-dd") 

    Worksheets("Feuil1").Select 
    Range("A1").Select 

    Dim cnn As New ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim StrQuery1 As String 
    Dim ConnectionString As String 

    ConnectionString ="ODBC;" & _
    "Driver={SQL Server Native Client 10.0};" & _
    "Server=187.125.254.231;" & _
    "Database=database;" & _
    "UID=sa; PWD=pwd"
    cnn.Open ConnectionString 
    cnn.CommandTimeout = 900 

     'Queries to be executed
    StrQuery1 = StrQuery1 & "Select Id from Users" 

    rst.Open StrQuery1, cnn, adOpenForwardOnly, adLockReadOnly 
    rst.Close 

    Debug.Print "StrQuery1:"; StrQuery1 
    cnn.Close 

    ThisWorkbook.Sheets("Central Dashboard").Select 
    Sheets("Feuil1").Range("A2").CopyFromRecordset rst

End Sub

is there any other solution ?

Can you please show us the specific issue(s) you are having. Read up on how to write a Short, Self Contained, Correct Example and have a look at the help centre on here. Then edit your question and give all the necessary details. — RossC
– RossC, Commented Aug 27, 2014 at 10:06
every time i run the macro , it keeps telling me that I have a complation probleme — user3708197
– user3708197, Commented Aug 27, 2014 at 10:11
ok... and what is the compilation problem? I can't see your computer. Please read the link I provided and edit your question to explain to us what is going on. "It gives a compiler error" tells me nothing whatsoever. — RossC
– RossC, Commented Aug 27, 2014 at 10:13
Have you imported the necessary sql references needed? Go to Tools > References and import the relevant SQL references. — RossC
– RossC, Commented Aug 27, 2014 at 10:23

Krish · Accepted Answer · 2014-08-27 13:51:36Z

it seems you are new to programming :).. before you use any variables please declare them this will help you to understand them quickly.

like:

Dim ReportingDate as Date
ReportingDate = ThisWorkbook.Sheets("Parameters").Range("D1")

Dim ReportingDateFor As String
ReportingDateFor = Format$(ReportingDate, "yyyy-mm-dd")

also check your connection string. try this connection string.

ConnectionString = "Driver={SQL Server Native Client 10.0};Server=187.125.254.231;Database=database;UID=sa; PWD=pwd"

Apart from that, looking at your code you are connecting to server, opening recordset, closing recordset and finally closing the connection AND THEN trying to retrieve the results. logically this will never work :) :)

try this:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command

Dim ConnectionString As String
ConnectionString = "Driver={SQL Server Native Client 10.0};Server=187.125.254.231;Database=database;UID=sa; PWD=pwd"

cnn.Open ConnectionString

'Queries to be executed
Dim StrQuery1 As String
StrQuery1 = StrQuery1 & "Select Id from Users"

'Prepare SQL execution
cmd.Name = "SelectUsers"
cmd.ActiveConnection = conn
cmd.CommandText = StrQuery1

Set rst = cmd.Execute
If Not rst.EOF Then
    With Sheets(1).Cells ' Enter your sheet name and range here
        .ClearContents ' clears the entire sheet
        .CopyFromRecordset rst ' copy the result
    End With
Else
    MsgBox "no records found.."
End If

'After work done close connection
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Collectives™ on Stack Overflow

Execute SQL Query from excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related