Passing Parameter Values from Excel to SQL Server

In this video we will perform what should be a relatively simple task but isn't intuitive at all. I'll show you how to pass the values from Excel cells to a dynamic SQL Server query that will execute and return the values to Excel. This will make a nice little Excel data portfolio project for those of you looking to learn. Check it out! I will say there are many ways to do this, but this method is fine for personal use only, not for a distributable deliverable due to the lack of stored proc usage.

★☆★ Join this channel to get access to perks: ★☆★

Watch the entire playlist to learn additional techniques using stored procedures:

#SQL #analytics #tsql #excel #dataanalyst #data

★☆★ FOLLOW ME BELOW: ★☆★

1:48 Demonstrate worksheet
3:45 SQL Query Overview
4:45 Start worksheet Build
5:47 Record Macro
6:17 Connect to Data
8:17 Explain & Build VBA
16:06 Insert Button for Refresh
17:16 Impromptu Debugging
17:54 It Works!!
18:30 Adjust Column Width Property
19:24 Confirm Results
20:00 Date Picker Ad-In
20:48 Password Protect VBA
21:28 Wrap it Up B!

[rpopecpa]

Awesome, awesome, awesome!! Wow, this is the best Excel/Power Query/VBA/SQL video I've ever seen. Many thanks.

[ihotvlu]

Thank you for this magic tutorial. Please make more videos. ❤❤

[alpeshshah]

Mindblowing, thank you for showing us how to combine vba and power query to get end result, opens up a greater scope now for me to produce fantastic reports.

[jalbert]

I needed to know how to do this and got lucky enough to learn it from Smoak himself. To quote my favorite Russian comedian, "What a country!"

[CecLevel]

Thanks! Very easy...appreciate you man.

[matusjaniga]

Great job Anthony, It is a briliant solution :) bravo

[fcodfxh]

Man you saved the day with this video! Thanks!!!

[chi-bucks]

Always learning something new from you. I appreciate.

[msamysobih]

Thanks, I have been searching for this for a long time 🤝

[juanmigueltobos]

That was what I was looking for, thx !!!

[YorkUniversityIT]

While does work and is GREAT for certain kinds of inputs, for the specific scenario of a monthly report you would be better off inferring the date range you need based on the current date, which can be done entirely in the SQL with no need to send an parameter.

[MJacobsen]

Great video, thanks! Any ideas for taking it to the next step?
I could really use tips on how to disable the "you are now executing code" popup, and secondly to embed username & password in the document. This way the document can be setup and then passed on to a colleague who can get new data anytime. (the SQL user embedded would of course only have readonly rights).
Hope to hear from you, greetings from Denmark :)

[avaltewarehous]

Thank you so much, Anthony, this technique has enabled me to make my query dynamic. However, now I am left with a new head-scratcher I wonder if you could help with.

I used PowerQuery to pivot the SQL data set, and return the pivoted data into my workbook. But when I refresh the query using updated parameters, it wipes out the pivot, and just returns back to the unpivoted data table from the database. I am guessing this is because there's no code in the VBA macro to put the data into the pivoted form, but when I try to record a macro of me using PowerQuery to pivot the data set, Excel does not record anything. So I am kind of stuck here. Have you ever encountered this issue, or have any suggestions? Thanks!

[Daulet]

Hi, i have a question. I have a dashboard in excel that has 20mb file size. If i switch to SQL and do not store the data in several sheets does it reduce file size significantly? I mean is it possible to create excel file that don't store all necessary data inside the file and fetches the data automatically from SQL everytime when it's opened.

[kewltopix]

Curious why use vba when you can just pass the parameters to power query via a function to read the cell where the parameter values are located. Unless it’s SP which power query wants a select. Haven’t tested this method yet myself. Is there a performance gain?

[emmanuel.aggrey]

please can i have this excel file to modifiy according to my need without starting from strach thank you.

[VatsalSingh]

I did all the steps same but getting Run-time error '1004': Application-defined or object-defined error. Any idea what could be happening?

[dyeihrp]

Thanks for the great explanation, but where are the codes? suptest

[abdullahquhtani]

Good BUT😊!!
I think it’ll be great if you used stored procedure instead 😏

[nuclear]

Do you know how to bypass problem with data format, excel cell with date is formating to UK standard and SQL Date variable needs US one?