1

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!

Improve this question
1
  • 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. Commented Apr 13, 2017 at 17:00

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You are right that the solution is ad hoc. I mostly need an easy way for the analyst to change the TNS info in the future when they move to a new database. I won't be around then and they will most likely have a hard time getting someone else to change that.
If I keep PowerQuery, can I use vba to copy the queried table, add in a drop down date range filter, and a refresh button? Is there a way to do all of that without vba in PowerQuery?
@JonStarnes PowerQuery is generally a datasource for a table, so you can do whatever you want with the resulting table, but keep in mind it will be refreshing (adding and deleting rows). Just assing a named range for the column you need.
What I have not is one worksheet that is the query table, and the second worksheet is a copy that will be editable. So the original query sheet stays in tact and the copy is the one that staff work from.
@JonStarnes Why do you need separate copy of the query table? Does your staff just add new rows, or they also edit existing data?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.