Can I automatically transfer data from an executed script in MS SQL Server to a worksheet tab on an Excel file?

Databases
1

I’m not very familiar with what can be accomplished with VBA coding, or with the full export capabilities of MS SQL Server, but I would like to know if the following is possible.

Currently, we have a script that we run in MS SQL Server. After executing it, we then copy the output and paste it into a tab of a worksheet. I have a separate “mastersheet” in Excel, which I use for clicking buttons to run macros that automate the many different steps which involve separate Excel sheets.

I would like to try to automate this process as much as possible, so I want to know if VBA has the capability of interacting with the data output on MS SQL Server?

From my understanding I can not use VBA to load and execute a script file. Accordingly, I would like to do the following:

  1. Open MS SQL and load the query file.
  2. Execute the query file.
  3. Copy the data in the MS SQL Server output window.
  4. Open the target Excel file.
  5. Paste the data result into an “Data Import” tab on the target Excel file.

I suspect the closest I can get to automating this process, is that I would have to manually copy the data onto the clipboard, and then from my mastersheet, run a VBA sub to past the clipboard data, and then open the target sheet and tab for pasting the clipboard data into?

I would appreciate any guidance or suggestions on how I can best automate this process.

1 Like
2

You are thinking much to complicated.

You can directly connect to the database with VBA and execute the query to fetch the data directly to the worksheet.

You do not need to work with exported files

3

I am a novice to pretty much the entire process, but I have found this YouTube video and I believe it describes the process you have indicated:

4

The example below requires me to type the SQL script into the VBA file.

i.e.

Sub Run_Report()

Dim Server_Name As String
Dim Database_Name As String
Dim SQL As String

Server_Name = “MyServer”
Database_Name = “MyDataBase”
SQL = “Select * From MyTable”

End Sub

Rather than copy and paste my script coding in the declaration of the SQL, would it be possible to load to my script file (i.e. MyScript.sql) and then reference the SQL variable to be the contents of the MyScript.sql file? If so, how would I do this?

5

I believe the answer to my question above may be in this line of code, but I am not familiar at all with the syntax so I don’t know what to change to make it work with my code:

SQL = "EXEC [dbo].[Procedure_make_report] 'param1'"

Based on the names I used in the example above, could someone please let me know what should appear where [dbo] and [Procedure_make_report] are.

6

Here you just put the same query as you use to create your .sql file. That’s all

7

I can’t do it that way, as the .sql file still needs to be the main source for the code. I just need the ability to call the code from the .sql file and input it into the value of SQL.

In theory I should be aware of when the .sql code changes so I could then update the code in VBA, but it would just be cleaner/more-reliable if I can just call the code directly from the .sql file.

8

What is the reason, that a file, which contains a copy of a database, must be the master, when I can fetch the data directly from the database?

Sorry i can’t follow your process

9

The file that contains our script must be a .sql file.

If there are any changes that are done to the script, it is must be done in the .sql file, since all our team members and our designated I.T. staff have access to that file.

This is why I need the value for SQL to be contents of the .sql file.

However, as an experiment I tried copying and pasting all 93 lines of our code into the quotations of the SQL script, however this does not seem possible on MS SQL Server, as it does not seem to allow a multi-line script? – MS SQL Server forces the first line to end with a double quote, even though I don’t want it.

Does MS SQL Server allow for multi-line scripts within quotations? If so, I will look through the options and turn off auto-formatting or whatever is forcing the double quotes in.

10

I think I know what you are referencing now – you are referencing the Data / Get Data option within Excel.

I am trying to make an automated process for a variety of users. I want this to be as simple as clicking a button that will then capture all the required data and output it into a spreadsheet.

The only way I believe this can be achieved is via VBA?

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.