Passing Parameter Values from Excel to SQL Server
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- 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: ★☆★
/ @anthonysmoak
Watch the entire playlist to learn additional techniques using stored procedures:
• SQL Meets Excel: Advan...
#SQL #analytics #tsql #excel #dataanalyst #data
Download the Database used in the video: learn.microsof...
★☆★ FOLLOW ME BELOW: ★☆★
Blog ► anthonysmoak.com/
Instagram ► / @anthonysmoakdata
Facebook ► / anthonybsmoak
Tableau Public ► bit.ly/3JMKsLY
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!
Awesome, awesome, awesome!! Wow, this is the best Excel/Power Query/VBA/SQL video I've ever seen. Many thanks.
Thanks Ryan, you just made my day! Make sure to check out the additional videos in this series if you need a bit more flexibility. th-cam.com/play/PLRNrM8SIqgQZ4u5yGSlVNeGgej1lcwLwr.html
@@AnthonySmoak I love the select all feature! I often times use multiple criteria where at least one of my criteria is select all. Nice!!
Thank you for this magic tutorial. Please make more videos. ❤❤
Thank you!
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.
I really appreciate this comment. Thank you!
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!"
I know a J Albert who happens to be a "helluva engineer". Is the one in the same?
@@AnthonySmoak Does a Whittingham timewarp in the woods?
@@jalbert9675 Indeed. I'll actually be catching up with him and Terence tomorrow.
@@AnthonySmoak Tell those two old married farts I said hello.
Thanks! Very easy...appreciate you man.
I really appreciate the SUPER thanks!!!!!! Thank you 🤜
Man you saved the day with this video! Thanks!!!
Glad it helped!
Great job Anthony, It is a briliant solution :) bravo
Thank you for the comment!
Very helpful - thanks!!
Glad to hear it, thanks!
Always learning something new from you. I appreciate.
Thank you for watching and commenting Ubani.
Thanks, I have been searching for this for a long time 🤝
Glad you found my video useful. Make sure to watch the other videos in the playlist for the more secure stored procedure method!
That was what I was looking for , thx !!!
Glad to hear it. Make sure to watch the 2nd video if you want to learn how to use a more secure method involving stored procedures. th-cam.com/video/7GOznSVBDmc/w-d-xo.html
magic! thanks!
Watch the rest of the series to build upon what you learned in this vid. Thanks for the comment!
This is fantastic!
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 :)
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.
Thanks for the comment. Yes, SQL is certainly capable of handling flexible dates. Think of this example as a teaching exercise to showcase the parameter capabilities between Excel and SQL if needed.
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!
Greatest
You're too kind, thanks!
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.
I don't know your particular case but If you have the opportunity to use a real database like SQL Server then do so. However there is a limit to how much data can be stored in Excel. Also use Power BI or Tableau for dashboarding since they are optimized to handle large datasets. In Power BI you can connect directly to the data source without importing data by using DirectQuery. support.microsoft.com/en-us/office/create-a-memory-efficient-data-model-using-excel-and-the-power-pivot-add-in-951c73a9-21c4-46ab-9f5e-14a2833b6a70
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?
I am not sure of the exact method you are referring to in order to get values back from SQL Server based upon Excel cell values (send me a link if you have one, thanks). But I do know there are multiple ways to accomplish what I've shown. It comes down to preference. I am just simply more comfortable with SQL and VBA code as opposed to DAX/M/ Power Query GUI methods. Willing to keep an open mind if I see an exact process.
@@AnthonySmoak the way I do it is to name a cell with a named range like cellWithDate, then create a function in power query
let
Source = (rangeName) => Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
in
Source
then name the function somn like fnGetDate > call the function where the value is needed like fnGetDate("cellWithDate")
Change the cell value, hit refresh, no vba needed
please can i have this excel file to modifiy according to my need without starting from strach thank you.
I did all the steps same but getting Run-time error '1004': Application-defined or object-defined error. Any idea what could be happening?
Make sure you are referring to the correct cells in the VBA code. Make sure your code is in a module as well.
@@AnthonySmoak Not sure if it is right things to do or otherwise, "ActiveWorkbook.RefreshAll" worked for me. Thanks that you took time to respond.
@@AnthonySmoak I also got the same Run-time error "1004': Application-defined or object-defined error". I am able to verify via message box that it gets the cell value and also it is in module as well. Not sure why
@@paolo.macatangay same here
@@AnthonySmoakSorry, I still get the Run-time error "1004': Application-defined or object-defined error"
Good BUT😊!!
I think it’ll be great if you used stored procedure instead 😏
Yes a stored procedure will have better protection from sql injection. It's just harder to execute in this manner. This is a quick and easy way to get it done. I'd use this technique only for personal use.
@@AnthonySmoak thank you 🙏🏻
@@abdullahquhtani4247 Just for you I am going to make another video showing how to make the call with a stored procedure. Same process just a different statement passed to the Query formula in VBA.
@@AnthonySmoak Thank you so much. Highly appreciated 🌹🌹👍🏼
As promised, make sure to like and comment! th-cam.com/video/7GOznSVBDmc/w-d-xo.html
Thanks for the great explanation, but where are the codes? suptest
Thank you! Sorry I haven't posted my specific code any place but the important part is that the technique is flexible enough that you can make up your own example.
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?
Try to change the date format settings in Windows. Or perform manipulation in VBA to format the date to your liking. Watch the 3rd video in this series where change the default formatting in a SQL stored procedure.
Hi Anthony, well explained. can you help me with my scenario? Example - Take oracle sample databease tables. If I have an excel sheet with only employee IDs, how can I pull their department and location from database into excel next to employee ids
I'm not sure of a practical way here. You can import the sheet into Oracle and run the query at the source. Or try to format the data so it is usable for the SQL IN clause.
Thank you. That was just an example. In real I have a weekly task to get details for certain range of values in excel from database. I was thinking if I can use power query and just refresh weekly instead of repeating
@@sreedharraor See if this technique would work if you want to use Power Query and start with a list of items: th-cam.com/video/OTBBX485Fg8/w-d-xo.html
@@AnthonySmoak thank you. What a coincidence!!!! When I searched, I found this video and it really helped me. I’m able to bring the data using where clause in power query
hi anthony, is there a way i can choose multiple Cities?
Certainly, modify the CASE statement I use in this video to handle two cities in your dataset: th-cam.com/video/GOvNQtfLTHo/w-d-xo.html
Please share with us this file. Thanks in advance.
This is the bad solution, it spams security warnings and is slow. Just use Microsoft Query as DataSource and bind your params directly to a cells. No need to code any line of VBA. Its tricky to get it running first as the tutorials of MS do not work like they say ;)
Make sure to watch the other two videos where we execute the SQL with a stored proc which is a bit more secure from SQL injection. Regardless, this is an ad hoc solution, not meant for serious production uses. Take it or leave it 😂
@@AnthonySmoak No offense meant but writing VBA with SQLs is more the Office 97 era than the current ones ;)
@@alexanderschu6933 Send me a link to your new high tech solution, maybe I'll make a new video for this series ;)
have run time error 438 and the bug sits with Active.Workbook.Queries("LimitBalance").Refresh, could you please help?