troubleshooting Question

Results should Always populate the same worksheet named ProjectResults

Avatar of Mike Orther
Mike OrtherFlag for United States of America asked on
VBAMicrosoft ExcelMicrosoft Office
3 Comments1 Solution82 ViewsLast Modified:
I am running the following VBA code in Excel 2016.  Every time it runs, the results are populated on a new worksheet.  I want this code to clear anything on a single worksheet named ProjectResults and the results should always go to this same ProjectResults worksheet.

Option Explicit


Dim conn As ADODB.Connection

Dim rst As ADODB.Recordset


Sub Connect_To_SQLServer(ByVal Server_Name As String, ByVal Database_Name As String, ByVal SQL_Statement As String)

Dim strConn As String

Dim wsReport As Worksheet

Dim col As Integer


strConn = "Provider=SQLOLEDB;"

strConn = strConn & "Server=" & Server_Name & ";"

strConn = strConn & "Database=" & Database_Name & ";"

strConn = strConn & "Trusted_Connection=yes;"


Set conn = New ADODB.Connection

With conn

        .Open ConnectionString:=strConn

        .CursorLocation = adUseClient

End With


Set rst = New ADODB.Recordset

With rst

        .ActiveConnection = conn

        .Open Source:=SQL_Statement


End With


Set wsReport = ThisWorkbook.Worksheets.Add

With wsReport


        For col = 0 To rst.Fields.Count - 1

                .Cells(1, col + 1).Value = rst.Fields(col).Name

        Next col


        .Range("A2").CopyFromRecordset rst


End With


Set wsReport = Nothing


Call Close_Connections


End Sub


Private Sub Close_Connections()


If rst.State <> 0 Then rst.Close

If conn.State <> 0 Then conn.Close


'// Release Memory

Set rst = Nothing

Set conn = Nothing


End Sub


Sub Run_Report()

Dim Server_Name As String



Server_Name = "CLDGP2018TEST\DEV"


Call Connect_To_SQLServer(Server_Name, "MED", "SELECT PAPROJNUMBER Project_Number, REQDATE Required FROM PA01303 WHERE PA01303.REQDATE >CONVERT(DATE, '2008-07-16', 102)")



End Sub

Open in new window

NorieSenior Associate
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros