Macro to automate "New Query" from Access Database

[Pundit]

I have the Protection turned on in my Workbook in order to prevent Users from creating new Sheets. One of the things I need Users to do is load a specific Access Database into one of the Sheets; however I need to allow them to choose the file from wherever they have it stored and I also need to allow them to 'transform' the data ahead of the import. Normally, I would provide the Users with instructions that guide them to the "Data/New Query/From Database" menu. The PROBLEM is ... that option is greyed-out whenever Workbook Protection is turned on.

Since I can't allow the Protection to be turned off and that menu path is greyed-out, my only recourse is to write a macro to perform that workflow for them. The problem I am facing is that the macro needs to literally type-in the query criteria as it goes (i.e. Let Source= ... etc.) and I'm not able to insert references to variables in that string. I've tried EVERYTHING to automate these steps; but nothing is working. Any ideas?

 

[Pundit]

Never mind. Problem solved. What I needed to do was create a cell in my Workbook -- formatted with Merge&Center, Wrap Text, Top Aligned, & Aligned Left. Then, I wrote a formula to make that cell look EXACTLY the same as the Query text which the macro is creating. By having that cell as a formula, I can insert references to variables (such as file location and query criteria). Then, I just poll the User to check those boxes in a UserForm and my "Query Text" cell automatically updates to reflect those choices. Thus, my "Query Text" cell always has a properly formatted query with the criteria the User wants to use.

After unloading the UserForm, the macro continues with the "New Query/From Database" process and I simply insert a reference to my "Query Text" cell in the "Formula:=" piece of the macro.

The properly formatted text the query is looking for looks like this:

The "Query Text" cell I created with a formula produces exactly the same text:

In this example, I have my "Query Text" formula in Cell $F$1 on the Sheet named "Notes". So the key three lines of code in my macro are:

Dim Query_Formula As String
Query_Formula = Worksheets("Notes").Range("F1")
ActiveWorkbook.Queries.Add Name:="RFW_Archive", Formula:=Query_Formula

The rest of the code in the macro is just the stuff that Excel needs to complete the load process. If you record the macro and go through the "New Query/From Database", you'll get all that code. Then, all you need to do is create a "Query Text" cell that your code can refer to.