Power Query + VBA/Macro Vs. VBA/Macro only

1
votes

I will briefly explain what I have and need here, and later if I can, I will edit this post and add a reproducible example.

My project: Query data from Oracle databases into one worksheet in Excel, then use a LOOKUP procedure to copy data into an editable table in a second worksheet. The second worksheet needs to be in a table format for filtering, and have a drop down option to filter the data by date ranges. The data needs to be refreshed 1-2 times a week only by 1-2 approved staff members.

Concerns:
Per suggestion I installed Power Query for Excel 2010, which required dependencies before it could work. The convenience factor is great and it makes it so that SQL queries can be edited without messing around in VBA code. However, the dependencies setup (Oracle client for data connections) limits casually deploying this as a solution.

The data connections and queries and the data lookup could all be done in VBA and assigned macros.

Questions: Should I use Power Query to query the data and then a VBA for the second sheet LOOKUP and date range filtering -- or should this all be written in VBA Excel Macros?

Which is more future proof friendly? Are there any advantages for using Power Query that would make this task more edit friendly for non-coders?

Thanks!

1
sqlexcelexcel-2010powerqueryvba
You will need the Oracle client installed regardless of whether you use Power Query, or ADO in VBA. That's required for a DB connection. As for editing SQL, you can store your queries on a worksheet (hidden if required) where they can easily be adjusted. – Tim Williams

1 Answers

1
votes

This probably can be solved with PowerQuery only, without VBA. I wouldn't recommend you storing queries in Excel table, the best is to move it on a server. A view or a function would be suitable. Querying the database, editing this view/function will work for only for only approved users. This is more secure and will require only 1 Excel workbook. In PowerQuery, you can refer old copy of the table at the moment you refresh it, therefore you can keep entered data and get new.

Your project seem to me as an ad-hoc solution.