VBA Multiple SQL query to Excel

Question

I need some help.

I have the following working VBA to import data into Excel for a stored procedure.

Challenge is how to modify the code to run multiple stored procedures and paste it on different pages.

Please help.

Sub Macro1()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "EXEC sp_Week_Option1_01_Export"
' Copy the records into cell A1 on Sheet1.
Sheet4.Range("A2").CopyFromRecordset rsPubs
For intColIndex = 0 To rsPubs.Fields.Count - 1
Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name
Next

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
'
End Sub

I modified to the following but I feel it's not the most efficient way. I am thinking creating a loop. Please help:

Sub Macro1()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Dim rsPubs2 As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Set rsPubs2 = New ADODB.Recordset

With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "EXEC sp_Week_Option1_01_Export"
' Copy the records into cell A1 on Sheet1.
Sheet4.Range("A2").CopyFromRecordset rsPubs
For intColIndex = 0 To rsPubs.Fields.Count - 1
Sheet4.Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name
Next

' Tidy up
.Close
End With

With rsPubs2
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "sp_Week_Option1_01_Export_Crosstab"
' Copy the records into cell A1 on Sheet1.
Sheet9.Range("A2").CopyFromRecordset rsPubs2
For intColIndex = 0 To rsPubs2.Fields.Count - 1
Sheet9.Range("A1").Offset(0, intColIndex).Value =     rsPubs2.Fields(intColIndex).Name
Next

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
'
End Sub

I suggest using parameters instead of hard coded strings @Ben — Keith John Hutchison
– Keith John Hutchison, Commented May 15, 2015 at 17:57
Ok cool. isn't there any way to loop executing queries for the above code? — Ben
– Ben, Commented May 15, 2015 at 19:10

John Smith · Accepted Answer · 2015-05-15 20:11:47Z

1

Sounds like this should be its own Subroutine. You can call the Sub whenever you need it by passing in the procedure to execute, and the worksheet to put the results on.

Public Sub Macro1(byval storedProc as string, byval ws as worksheet)
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=PC\SQL2014;INITIAL CATALOG=Option Database;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open storedProc
' Copy the records into cell A1 on Sheet1.
ws.Range("A2").CopyFromRecordset rsPubs
For intColIndex = 0 To rsPubs.Fields.Count - 1
ws.Range("A1").Offset(0, intColIndex).Value = rsPubs.Fields(intColIndex).Name
Next

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
'
End Sub

edited May 15, 2015 at 20:11

answered May 15, 2015 at 17:58

John Smith

7,4477 gold badges52 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ben Over a year ago

Is there any way to loop the opening query and pasting part?

John Smith Over a year ago

@Ben yes, but having it this way makes it more re-usable, since it won't be built to exactly the situation you are using it for now.

Ben Over a year ago

Ok shoot. I didnt even realize it. Thank you so much. How do I call the procedure though?

John Smith Over a year ago

@Ben one small thing, I just changed the solution to a Subroutine instead of a function. It will do the same thing, and called the same way, it just doesn't return a value

Ben Over a year ago

Thanks alot @aStackOverflowUser. I start using your subroutine and it is very useful and you taught me an efficient way to code VBA. Thank you! I wish I can vote but I have <15 rep.

|

Collectives™ on Stack Overflow

VBA Multiple SQL query to Excel

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related