troubleshooting Question

Results should Always populate the same worksheet named ProjectResults

Avatar of Mike Orther
Mike OrtherFlag for United States of America asked on
Microsoft OfficeMicrosoft ExcelVBA
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
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