0

My skill level: basic user of VBA, know next to nothing about SQL (though I’ve recently learned how to execute code, etc).

I’ve created a user interface in an Excel worksheet to allow user input of report variables (dates, etc). These pass into a second Excel worksheet that, through the use of various Excel statements, creates the necessary SQL code. My macro then copies this worksheet and opens Microsoft SQL Server 2008 Rx via a shell command. Currently, I then manually click “connect” on the resultant Microsoft SQL Server 2008 R2 connect-to-server window (which does not need a password, just requires me to click “connect”), I create a new query, I paste the code in and then execute in SQL. So it is these latter steps (opening SQL, click “connect”, copy/paste code, execute) that I’m trying to automate via VBA.

To offer more context, once the SQL code has run, I then have a second macro that uses the Excel Data Connection wizard to bring the resultant SQL data back into my worksheet. This is working well.

I’ve researched this for several days on various boards. None seem to observe my approach of creating the code in Excel (probably because it is an inelegant approach). Or the post responses are beyond my skill level to understand. But I do fear that I’m asking a question that has been answered before (apologies in advance).

Thanks for whatever advice/time can be offered.

I’m using: Excel/Office 2010 Microsoft SQL Server2008 R2 (is this the same as Microsoft SQL Server Management Studios? My SQL application shows both names) Microsoft ActiveX Data Objects 6.1 Library (this is NOT currently checked off. One post I saw referenced the need for this. I have multiple prior AciveX Library versions available as well.)

Kal10
  • 23
  • 2
  • You can connect to SQL and execute queries from VBA. No need to open Mangement Studio. – Vojtěch Dohnal Mar 09 '15 at 16:19
  • Thank you all for the input. Paqogomez: my intent around using Excel was to A) allow for an end-user input interface (my end-users are not SQL savvy and wouldn't reliably be able to update SQL code with changing parameters) and B) to allow for end user report customization after the data in imported to Excel. Does SQL Server have solutions to these concerns? – Kal10 Mar 09 '15 at 16:37
  • Vojtech Dohnal: thank you. Could you offer me some insight as to how to do this? – Kal10 Mar 09 '15 at 16:55
  • For simple CRUD I have found infopath to be great. There are some caveats and small hurdles but you can nock up a form really quickly. – T I Mar 09 '15 at 20:09
  • There are tutorials on the web for that, see http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/. Check also this question http://stackoverflow.com/questions/27385245/using-excel-vba-to-run-sql-query – Vojtěch Dohnal Mar 10 '15 at 07:39
  • Vojtech Dohnal: thanks to your referenced tutorials, I've been successful setting up and running a very simple SQL query in VBA. Two questions: 1) the SQL query I want to pass via VBA is quite long. I'd like to avoid putting it all directly into VBA. Can I use VBA to reference a .txt file that contains the intended query? 2) I wrote the SQL query code in Managmenent Studio. Assuming I am able to copy/paste this into a .txt file and reference it through VBA, is syntax, etc the same for Managment Studio and VBA? Or will Management Studio query code not be interpreted exactly the same in VBA? – Kal10 Mar 11 '15 at 17:01
  • 1
    If your SQL code is complex then you should consider using SQL stored procedure or table-valued function etc. and then just call it from your VBA. You can also paste it into VBA as a string literal. I cannot see any advantage reading it from a text file, technically possible but overcomplicated. – Vojtěch Dohnal Mar 12 '15 at 08:10
  • 1
    If you need that your message appears in someone's SO Inbox, you should write it like this: @Kal10 – Vojtěch Dohnal Mar 12 '15 at 08:11
  • Just a follow up to help out future readers: thanks to the help from folks here, it became clear that my initial approach wasn't sound. After some research and education, I created a stored procedure in SQL Server Management Studio with variables for parameters. I then created an Excel interface for the user to enter their report parameters and used VBA to 1) create an ADO connection to the stored procedure and 2) pass the parameter values from the Excel interface to the SQL stored procedure. The VBA code then returned the resultant SQL dataset to an Excel destination of my choosing. – Kal10 Apr 28 '15 at 16:56
  • So thanks to each of you for pointing me in the right direction. I appreciate it. – Kal10 Apr 28 '15 at 16:56

1 Answers1

2

What you're doing is very ingenious, but it's probably not necessary. Excel has a number of features for importing external data.

I would look at the Data tab in Excel and the From Other Sources section of the ribbon. You have at least three options: the From SQL Server, From Data Connection Wizard, and From Microsoft Query options.

All of these will set up a connection to a database server and execute SQL against it. They return data to your spreadsheet in different ways.

Where VBA will come into play again is in automating the generation and parameterization of the queries these will execute.

But I would set up something that works, with constants for your parameter values, first, so you get an idea of the possibilities, and then read and research dynamically setting your SQL with VBA.

You might also investigate PowerPivot, by the way.

Ann L.
  • 13,760
  • 5
  • 35
  • 66
  • Thank you Ann L. These Excel Data tab options all seem to reference IMPORTING to Excel, which is indeed what I'm already using to import the SQL data. But it sounds like you're saying they can also execute the SQL queries? If so, how does that happen and where does the user input their variables? The Microsoft Query Wizard seems to touch on this, but it doesn't appear very user friendly for they layperson (as far as end users using this system to generate reporting). – Kal10 Mar 09 '15 at 16:44
  • @Kal10 It's possible to set up parameters & prompts in the Microsoft Query tool, so that the user is prompted at run time to enter a value. When the user clicks the Refresh Query button, the query executes, prompts the user, and then returns all the records. It's also possible to programmatically generate SQL and assign it to the appropriate property with VBA, so that the query that's executed is the one you've created programmatically. I do not have any code handy that does this, but I can look around for examples. – Ann L. Mar 10 '15 at 16:24