VBA to open and run .sql file and copy the result in excel

Question

I am a bit new to this so my apologies in advance for any mistakes. I am trying to open and execute a sql query which is saved in C drive. The sql works fine. I did not write that sql and it is a very big file so would not like to add the code in VBA. I have been searching on this site and other places but struggling to make this work. I have had some or the other problem each time. I am pasting the code below for you to have a look and help me please. I have created this function which I am using in my VBA code.

Public Function ss()


'Open Connection
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection

'Set Connection String
Dim sCon As String
sCon = "Provider=SQLOLEDB;Persist Security Info=True;User 
ID='*****';Password='*****';Initial Catalog=****;Data Source=*******;Use 
Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption 
for Data=False;Tag with column collation when possible=False"

oCon.Open sCon 'Connect established

'Create recordset
Dim ps As ADODB.Recordset
Set ps = New ADODB.Recordset

'Set & execute SQL Command
Dim pCMD As ADODB.Command
Set pCMD = New ADODB.Command
Set pCMD.ActiveConnection = oCon

Dim filename As String
filename = "C:\Users\???????????\******.sql"


'select data
pCMD.CommandText = 
CreateObject("scripting.filesystemobject").opentextfile(filename).ReadAll()
Set ps = pCMD.Execute(, , adCmdText)

Debug.Print ps.GetRows

If ps.EOF = False Then Sheets("Sheet2").Range("A1").CopyFromRecordset ps

'Close connection
Set ps = Nothing
Set pCMD = Nothing
oCon.Close
Set oCon = Nothing

End Function

Debug.Print has only been used to see the result but it is showing the same error "Run-time error '3704' Operations is not allowed when the object is closed. After trying so many different things I am not sure what is it that I am doing wrong.

Can someone please help?

Any help will be really appreciated.

Nicolaesse · Accepted Answer · 2018-02-26 15:40:57Z

0

For me the easiest solution for you is to modify the end of the stored procedure so when executed, it insert all the results in a table. The next time you execute the SP you just have to TRUCATE the table and INSERT new results.

Execute the SP

You can call the SP from Excel with VBA using some code you can find here on StackOverflow, for example the following (which I've taken from this answer):

Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
    cmd.CommandType = 1      ' adCmdText
    cmd.CommandTimeout = 120 ' number of seconds to time out

Dim ConnectionString As String
cnn.ConnectionString = "driver={SQL Server};server=MYSERVERNAME;Database=MYDATABASE;Trusted_Connection=yes;"
cnn.Open

Dim SQL As String
SQL = "EXEC [dbo].[Procedure_make_report] 'param1'"

cnn.Open
    cmd.CommandText = SQL
    cmd.ActiveConnection = cnn
    cmd.Execute

If Not cnn Is Nothing Then
    cnn.Close
    Set cnn = Nothing
End If

Retrive results into Excel

Now that the results of the SP are stored into a table of you DB you can import them into Excel with ODBC connection as shown here or with this procedure.

answered Feb 26, 2018 at 15:40

Nicolaesse

2,76413 gold badges51 silver badges78 bronze badges

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

1 Comment

P Arora Over a year ago

Thank you so much for the quick response. I will give this a try though I would really like to find out if there is a way to do this directly opening and running the sql file and then copying all records to my excel file.

Collectives™ on Stack Overflow

VBA to open and run .sql file and copy the result in excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related