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. kzread.info/head/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!!

Always learning something new from you. I appreciate.

@AnthonySmoak

Жыл бұрын

Thank you for watching and commenting Ubani.

This is fantastic!

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!

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

@AnthonySmoak

4 ай бұрын

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.

@AnthonySmoak

8 ай бұрын

I really appreciate this comment. Thank you!

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. kzread.info/dash/bejne/aXuD3NCMhqWqncY.html

Thanks! Very easy...appreciate you man.

@AnthonySmoak

2 ай бұрын

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

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

@AnthonySmoak

3 ай бұрын

Glad it helped!

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

@AnthonySmoak

5 ай бұрын

Thank you for the comment!

Very helpful - thanks!!

@AnthonySmoak

2 ай бұрын

Glad to hear it, thanks!

magic! thanks!

@AnthonySmoak

3 ай бұрын

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

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.

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

Greatest

@AnthonySmoak

11 ай бұрын

You're too kind, thanks!

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!

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.

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

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

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

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.

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: kzread.info/dash/bejne/gYh2pLptaJisl5s.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

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: kzread.info/dash/bejne/eYOqsLOtlq-6eNI.html

Please share with us this file. Thanks in advance.

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.

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"

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! kzread.info/dash/bejne/aXuD3NCMhqWqncY.html

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