Deprecated: Creation of dynamic property CF\WordPress\DataStore::$logger is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php on line 23

Deprecated: Creation of dynamic property CF\WordPress\Proxy::$pluginAPI is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/Proxy.php on line 31

Deprecated: file_get_contents(): Passing null to parameter #2 ($use_include_path) of type bool is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/codelights-shortcodes-and-widgets/codelights.php on line 20

Warning: session_start(): Session cannot be started after headers have already been sent in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/wordpress/src/class/wordpress.php on line 53

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831

Deprecated: Creation of dynamic property ternplugin\youtube_video::$post is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/plugin/src/class/youtube_video.php on line 64

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831

Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1831
{"id":3618,"date":"2022-03-31T09:44:12","date_gmt":"2022-03-31T16:44:12","guid":{"rendered":"https:\/\/bonbonsguide.com\/wp\/?p=3618"},"modified":"2023-01-21T13:04:13","modified_gmt":"2023-01-21T20:04:13","slug":"constructing-and-executing-sql-queries-in-excel-using-vba-import-data","status":"publish","type":"post","link":"https:\/\/bonbonsguide.com\/wp\/blog\/2022\/03\/constructing-and-executing-sql-queries-in-excel-using-vba-import-data\/","title":{"rendered":"Amazing Method for Executing SQL Queries in Excel Using VBA (Import Data) – No Need to Use Multiple Applications!"},"content":{"rendered":"\n

I have found several use cases for my team and I to save time and utilize Excel alone for pulling data via SQL, as opposed to running queries in Access, SSRS, SSMS or Toad and then exporting that data into Excel for manipulation and analysis. There is not only the benefit of saving time by skipping the export\/import process, but also in the ability to build templates\/files and save them for quick and easy future SQL pulls. The days of needing multiple applications for your SQL pulls to import into Excel are over!<\/p>\n\n\n\n

The best use cases I’ve found that support this method are repeatable processes where the same input variables are required each time, and where the query results will not exceed the row limitations of an Excel sheet (~1M). Even in this case, you may find the first two sections of this post useful for learning about constructing and executing SQL queries<\/strong> for use outside of Excel. This alone may save you time if you are in the habit of writing long and involved queries with changing criteria.<\/p>\n\n\n\n

If you are exceeding the Excel row limit but still want to work in Excel, using Power Pivot can handle this, so you may wish to utilize Power Query instead. See my post on utilizing parameters with Power Query here<\/a> for more information on how to import the data this way.<\/p>\n\n\n\n

I have also found this approach to be useful when pulling data from different sources using the same criteria. It saves me lots of time.<\/p>\n\n\n\n

In this example, we will construct and then run a simple SQL query using a few parameters\/variables that we enter into the spreadsheet. <\/p>\n\n\n\n

Follow along so you can see how it works in practice, and then I encourage you to try it out with your own data. Once you master this method, hopefully you will find amazing ways to apply it to your own work! <\/p>\n\n\n\n

A copy of the file described in this post is available for purchase – just reach out to me<\/a> for information. A more complex version that handles wildcards is also for sale.<\/p>\n\n\n\n

\n\n\n\n
Setting Up the Variables\/Criteria<\/strong>\/Parameters<\/strong> for Executing SQL Queries in Excel<\/strong> <\/h2>\n\n\n\n
My goal will be to run this query: Select * from Store where CreatedDate > #3\/1\/2022# and State = ‘GA’<\/strong><\/p>\n\n\n\n
\n
The two variables in this example will be the created date<\/strong> (3\/1\/2022) and the State<\/strong> (GA).<\/li>\n\n\n\n
At the top of my first sheet, we will designate named ranges for the two inputs. <\/li>\n\n\n\n
Simply type in Created Date in cell A1 to identify the input, then in cell B1, type in 3\/1\/22. <\/li>\n\n\n\n
Make B1 a named range – we will call this CreatedDate. <\/li>\n\n\n\n
Similarly, in A2, type in State, and then in B2, type in GA. Ultimately, you can add in data validation and use lists, but let’s keep it very simple for now. <\/li>\n\n\n\n
Make cell B2 a named range called State.\n
\n
If you are not familiar with named ranges, the easiest way to make one is to select the cell or cells and then type the name into the dropdown box at the top left next to the formula bar, where the cell address is displayed. Alternately, you can press CTRL+F3 to bring up the Named Ranges dialog box.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
$\"SQL$
Executing SQL Queries in Excel – Setting up Inputs for Variables Using Named Ranges<\/figcaption><\/figure>\n\n\n\n
Constructing the SQL Statement<\/strong><\/h2>\n\n\n\n
From here, we can either construct the SQL statement in an Excel sheet or in VBA. I have found that it is far easier to construct in an Excel sheet, especially for complex statements with many criteria. This way certainly allows for more flexibility and better troubleshooting in my opinion. Also, it’s easier to manage from a support standpoint.<\/p>\n\n\n\n
\n
I will add a new sheet\/tab to house the SQL Statement. I will start by typing in the full statement I expect in to A1, just to use as a reference as I build out the statement in the cells below. <\/li>\n\n\n\n
I then break the statement out line by line, using column A for the variables, and column B for the statements and column C for the joining of statements with variables. <\/li>\n\n\n\n
Finally, I use the TEXTJOIN function in cell A4 to join together all of the rows in the combined section. I used a space as the delimiter (” “). This ensures proper spacing throughout the resulting SQL statement.<\/li>\n\n\n\n
I then make cell A4 a named range called SQLStatement.<\/li>\n\n\n\n
See image below to see how I have achieved all of this.\n
\n
I have included some helper\/formula notes on how to format the variables, since the SQL statement has to be properly formatted in order to work.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n
$\"Executing$
Construction of SQL Queries in Excel<\/figcaption><\/figure>\n\n\n\n
VBA Code for Executing SQL Statement and Importing the Data<\/strong><\/h2>\n\n\n\n
\n
Open the VBA editor (Alt + F11).<\/li>\n\n\n\n
Add a new module.<\/li>\n\n\n\n
Add a new subroutine. I’ve called mine SQLPull.<\/li>\n\n\n\n
Very important step – enable the required references. Go to the Tools menu and select References. Select the ActiveX Data Objects 2.8 Library and Recordset 6.0 Library – see the last two checked in the image below (yours may be different versions). Click OK to save.<\/li>\n<\/ul>\n\n\n\n
$\"\"$
VBA Project References – ActiveX Data Objects\/Recordset<\/figcaption><\/figure>\n\n\n\n
Here is an overview the VBA code I wrote, that is pasted below.<\/p>\n\n\n\n
\n
Note that it will connect to the database you identify after you update the connection string if needed.<\/li>\n\n\n\n
It will then execute the SQL select statement and grab the recordset.<\/li>\n\n\n\n
The code will then write the headers from the query into the cells identified.<\/li>\n\n\n\n
Next, it will paste the rows that are returned by the SQL query, starting in the specified cell.<\/li>\n\n\n\n
Finally it will close the connection and end the subroutine.<\/li>\n\n\n\n
For my applications of this, I like to switch to manual calculation and turn off screen updating because I have found it improves the speed of loading the data. You may choose to leave these alone if working with less data.<\/li>\n<\/ul>\n\n\n\n
I have added commentary and explanations throughout to hopefully help you to modify as needed to support your own needs.<\/p>\n\n\n\n
Sub SQLPull()

‘* www.bonbonsguide.com * <\/strong>
‘Importing Data into Excel using a SQL select statement in a cell <\/strong>

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath, sconnect, sqlstring As String
Dim icols As Long

””””””””””””””””””””””””’
‘Database Name\/Path
””””””””””””””””””””””””’
DBPath = STOREDB

‘NOTE:
‘If you are querying a SQL Server or Oracle DB, use the name of the database:
‘DBPath = YourDatabaseName

‘If you are querying an Access Database, use the path to the file in quotes:
‘DBPath = “\\myfileserver\\Bonbon\\MyAccessDB.accdb” OR “F:\\Bonbon\\MyAccessDB.accdb”

””””””””””””””””””””””””’
‘Connection String
””””””””””””””””””””””””’
‘The DSN is the existing ODBC connection on your PC – this must be set up first!

‘Uncomment the applicable sconnect string for your database and modify as needed. For MS Access, no modifications should be needed.

‘SQL Server using Windows Authentication:
sconnect = “Provider=MSDASQL.1;DSN=STOREDB;DBQ=” & DBPath & “;HDR=Yes’;”

‘ORACLE Connection using an UID\/PWD:
‘sconnect = “Provider=MSDASQL.1;DSN=WAREHOUSE;uid=bonbonsguide;pwd=helpsus;DBQ=” & DBPath & “;HDR=Yes’;”

‘MS Access:
‘sconnect = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & DBPath & “;”

””””””””””””””””””””””””””””””””””””””””””””””””””””’
‘Set Timeouts (These may not be required in your environment)
””””””””””””””””””””””””””””””””””””””””””””””””””””’
Conn.ConnectionTimeout = 200
Conn.CommandTimeout = 200

””””””””””””””””””””””””’
‘Connect to datasource
””””””””””””””””””””””””’
Conn.Open sconnect

””””””””””””””””””””””””’””””””””””””””””””””””””’
‘VBA get SQL Statement from Sheet\/Named Range
””””””””””””””””””””””””’””””””””””””””””””””””””’
sqlstring = Range(“SQLstatement”)

””””””””””””””””””””””””””””””””””
‘Get the recordset – this command will execute the SQL Statement’
””””””””””””””””””””””””””””””””””
mrs.Open sqlstring, Conn

””””””””””””””””””””””””””””””””””
‘Return the Header Names
‘”””””””””””””””””””””””””””””””””’
‘If you don’t need the headers or are using your own, comment the block out

‘Where Headers will be pasted:
‘Sheet1 = Sheet identifier – use Sheets(“name of sheet”) to use sheet name instead
‘Cells (4 – indicates row 4, + 3 indicates to start in column C) \u2026 Edit this as needed

For icols = 0 To mrs.Fields.Count – 1
Sheet1.Cells(4, icols + 3).Value = mrs.Fields(icols).Name
Next

””””””””””””””””””””””””””””””””””<\/strong>
‘OPTIONAL – SPEED UP IMPORT
””””””””””””””””””””””””””””””””””<\/strong>
‘If retrieving lots of records, it may speed it up if you set calculation to manual during the import process.
‘Setting the screen updating to false may also speed up the import. Comment these out if preferred.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

””””””””””””””””””””””””’””””””””””””””””””””””””’””””””””””””””””””””””””’
‘OPTIONAL – CLEAR DATA IN ROWS BEFORE PASTING RECORDSET OR DO OTHER PREP
””””””””””””””””””””””””’””””””””””””””””””””””””’””””””””””””””””””””””””’
‘Add your code here.

””””””””””””””””””””””””””””””””””
<\/strong>‘Paste the Rows\/Records
‘”””””””””””””””””””””””””””””””””’<\/strong>
‘Importing rows returned – the range below will be where the data starts – line this up with the headers, one row below.
Sheet1.Range(“C5”).CopyFromRecordset mrs

””””””””””””””””””””””””””””””””””<\/strong>
‘Close the Recordset
””””””””””””””””””””””””””””””””””<\/strong>
mrs.Close

””””””””””””””””””””””””””””””””””<\/strong>
‘Close Connection
””””””””””””””””””””””””””””””””””<\/strong>
Conn.Close

””””””””””””””””””””””””””””””””””<\/strong>”””””””””’
‘Turn automatic calculation and screen updating back on.
””””””””””””””””””””””””””””””””””<\/strong>”””””””””’
Application.Calculation = xlCalculationAutomatic
Application.Calculate
Application.ScreenUpdating = True

End Sub<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
Wrapping Up<\/strong><\/h2>\n\n\n\n
I hope this helps you out. Below is the final look of my file for this example. I have added a button for the users to click in order to run the VBA code and execute the SQL by assigning the macro. I have formatted so the data returns in a table for easy manipulation and analysis. I have also updated the general formatting and named the sheets. <\/p>\n\n\n\n
To save you time, a copy of the file is available for purchase – just reach out to me<\/a> for information.<\/p>\n\n\n\n
I also have another sample template file available that allows you to put multiple criteria in a list and constructs the query accordingly, searching all items. It even allows for the use of wildcards <\/strong>by <\/strong>automatically formulating LIKE statements! <\/strong>Exciting stuff! Contact me<\/a> if interested in purchasing.<\/p>\n\n\n\n
$\"Tool$
Tool for Execution of SQL Queries in Excel (For Sale)<\/figcaption><\/figure>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
I have found several use cases for my team and I to save time and utilize Excel alone for pulling data via SQL, as opposed to running queries in Access, SSRS, SSMS or Toad and then exporting that data into Excel for manipulation and analysis. There is not only the benefit of saving time by […]<\/p>\n","protected":false},"author":1,"featured_media":3641,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[226,21,217,30],"tags":[32,25,221,20,115,225,223,187,188,176,224,24],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/bonbonsguide.com\/wp\/wp-content\/uploads\/2022\/03\/image-10.png?fit=831%2C755&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7QB1z-Wm","_links":{"self":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/posts\/3618"}],"collection":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/comments?post=3618"}],"version-history":[{"count":24,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/posts\/3618\/revisions"}],"predecessor-version":[{"id":4949,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/posts\/3618\/revisions\/4949"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/media\/3641"}],"wp:attachment":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/media?parent=3618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/categories?post=3618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/tags?post=3618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}