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 ?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments