Passing Parameter Values from Excel to SQL Server

Подписаться 23 тыс.

Просмотров 28 тыс.

50% 1

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.microsoft.com/en-us/sql...
★☆★ 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!

Опубликовано:

18 дек 2022

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 78

@rpopecpa Год назад

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

@AnthonySmoak Год назад

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. ru-vid.com/group/PLRNrM8SIqgQZ4u5yGSlVNeGgej1lcwLwr

@rpopecpa Год назад

@@AnthonySmoak I love the select all feature! I often times use multiple criteria where at least one of my criteria is select all. Nice!!

@chi-bucks Год назад

Always learning something new from you. I appreciate.

@AnthonySmoak Год назад

Thank you for watching and commenting Ubani.

@user-ih7ot7vl8u 4 месяца назад

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

@AnthonySmoak 4 месяца назад

Thank you!

@alpeshshah891 8 месяцев назад

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.

@AnthonySmoak 8 месяцев назад

I really appreciate this comment. Thank you!

@kaiser6242 Год назад

This is fantastic!

@msamysobih680 Год назад

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

@AnthonySmoak Год назад

Glad you found my video useful. Make sure to watch the other videos in the playlist for the more secure stored procedure method!

@juanmigueltobos9301 Год назад

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

@AnthonySmoak Год назад

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-7GOznSVBDmc.html

@user-fc8od3fx4h 6 месяцев назад

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

@AnthonySmoak 3 месяца назад

Glad it helped!

@CecLevel 2 месяца назад

Thanks! Very easy...appreciate you man.

@AnthonySmoak 2 месяца назад

I really appreciate the SUPER thanks!!!!!! Thank you 🤜

@costoncheatham7025 2 месяца назад

Very helpful - thanks!!

@AnthonySmoak 2 месяца назад

Glad to hear it, thanks!

@matusjaniga1165 5 месяцев назад

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

@AnthonySmoak 5 месяцев назад

Thank you for the comment!

@kareldenecker1989 3 месяца назад

magic! thanks!

@AnthonySmoak 3 месяца назад

Watch the rest of the series to build upon what you learned in this vid. Thanks for the comment!

@jalbert9675 8 месяцев назад

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!"

@AnthonySmoak 8 месяцев назад

I know a J Albert who happens to be a "helluva engineer". Is the one in the same?

@jalbert9675 8 месяцев назад

@@AnthonySmoak Does a Whittingham timewarp in the woods?

@AnthonySmoak 8 месяцев назад

@@jalbert9675 Indeed. I'll actually be catching up with him and Terence tomorrow.

@jalbert9675 8 месяцев назад

@@AnthonySmoak Tell those two old married farts I said hello.

@MJacobsen 6 месяцев назад

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 8 месяцев назад

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!

@abielwubet2587 11 месяцев назад

Greatest

@AnthonySmoak 11 месяцев назад

You're too kind, thanks!

@YorkUniversityIT 10 месяцев назад

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.

@AnthonySmoak 10 месяцев назад

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.

@Daulet2009 Год назад

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.

@AnthonySmoak Год назад

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

@emmanuel.aggrey Год назад

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

@nuclear1747 Год назад

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?

@AnthonySmoak Год назад

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.

@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?

@AnthonySmoak Год назад

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.

@kewltopix Год назад

@@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

@sreedharraor Год назад

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

@AnthonySmoak Год назад

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.

@sreedharraor Год назад

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

@AnthonySmoak Год назад

@@sreedharraor See if this technique would work if you want to use Power Query and start with a list of items: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-OTBBX485Fg8.html

@sreedharraor Год назад

@@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

@Immortal34345 Год назад

hi anthony, is there a way i can choose multiple Cities?

@AnthonySmoak 11 месяцев назад

Certainly, modify the CASE statement I use in this video to handle two cities in your dataset: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-GOvNQtfLTHo.html

@user-dy6ei2hr6p 11 месяцев назад

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

@AnthonySmoak 11 месяцев назад

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.

@VatsalSingh007 Год назад

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

@AnthonySmoak Год назад

Make sure you are referring to the correct cells in the VBA code. Make sure your code is in a module as well.

@VatsalSingh007 Год назад

@@AnthonySmoak Not sure if it is right things to do or otherwise, "ActiveWorkbook.RefreshAll" worked for me. Thanks that you took time to respond.

@paolo.macatangay Год назад

@@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

@MostafaElgohary-vw6tp Год назад

@@paolo.macatangay same here

@MostafaElgohary-vw6tp Год назад

@@AnthonySmoakSorry, I still get the Run-time error "1004': Application-defined or object-defined error"

@workplacevids9977 7 месяцев назад

Please share with us this file. Thanks in advance.

@abdullahquhtani4247 Год назад

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

@AnthonySmoak Год назад

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.

@abdullahquhtani4247 Год назад

@@AnthonySmoak thank you 🙏🏻

@AnthonySmoak Год назад

@@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.

@abdullahquhtani4247 Год назад

@@AnthonySmoak Thank you so much. Highly appreciated 🌹🌹👍🏼

@AnthonySmoak Год назад

As promised, make sure to like and comment! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-7GOznSVBDmc.html

@alexanderschu6933 3 месяца назад

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 ;)

@AnthonySmoak 3 месяца назад

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 😂

@alexanderschu6933 3 месяца назад

@@AnthonySmoak No offense meant but writing VBA with SQLs is more the Office 97 era than the current ones ;)

@AnthonySmoak 3 месяца назад

@@alexanderschu6933 Send me a link to your new high tech solution, maybe I'll make a new video for this series ;)