Open a query, run in acces, in Excell 5

[Rmck87]

Ok, i have a form that on the users selections runs and creates a query. Then it saves it. Then what i want to be able to do. Is when the user opens Excell he/she should be able to open the Query that was saved before in access. If anyone knows how to do this please let me know. Or if i am in the wrong im sorry, and please direct me to a better forum for this question. Thanks in advance!

-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

Yes ...you can set it to auto refresh when the user changes a cell value

 

[Rmck87]

xlbo: i believe you forgot to put a space or & symbol in the hilighted spot.

Sub GetQueries()
Dim dbs As Database
Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wks.OpenDatabase("c:\Home\Access\GB_Universe.mdb", True) Sheets("Sheet2&quot.cbQueries.Clear
For Each qdf In dbs.querydefs
Sheets("Sheet2&quot.cbQueries.AddItem (qdf.Name)
Next
dbs.Close
Set dbs = Nothing
End Sub


One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

ok... now it is saying that my macros are disabled. to go online and it will tell me how to enable my macros. the first one was working fine. But, for some reason its not workin now. how do i enable them?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

ETID: give me some more information on linking the parameters directly to the cells. I'm going to go for that and see which one i can get to work best first. Thanks

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

In the MSQuery editor put prompts in brackets for each fieled that has a criteria.

So instead of ...

Field_name
>12

you put...
Field_name
>[Enter Value]

do this for each variable.

then when you return data to excel..it will prompt you for the search values.


when the data is returned,...right click in the upper data area and select parameters, here is where you can select options such as use cell values, refresh on change etc.

 

[Rmck87]

ETID: what do you mean search values? and is it going to prompt me every time that i load the query?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

Search criteria in your query....

you normaly say (via sql) something like show me all records in my_table where this_field = a search value


so don't enter a hard value enter a bracketed prompt instead...and yes each time you run/refresh the query it will ask you for new values or get them from cells (if you define them) as outlined above.

 

[Rmck87]

Well, i dont want it to ask for the criteria, period. Because in the form the criteria is already being drawn out, and run to the specifications of the user in Access. So, that probably wont work. meaning there will already be a where clause in the code.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

What code???

 

[Rmck87]

I have a form, created in Access, that a user selects criteria for a SQL Statement. A query is created, with code, and runs whenever the user presses a specific button on the form. Then, the user will save the already run query as 'anyname' then they will exit Access. Open Excel, and i want them to open a spreadsheet in excell, then select their query that they saved as 'anyname' and it will refresh the query and it will show up on a spreadsheet, with the exact criteria that was created in Access. If you don't understand let me know i can break down and explain any parts you need to understand. Thank you for all of your help.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

xlbo: OK, i cannot run a single macro now. For some reason it is saying that it cannot without a signature, and that the security level is too high? Any ideas?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

Well...bypass that form all together and let the user define those criteria directly in the spreadsheet.

You can join multpile tables in msquery if needed.

 

[Rmck87]

ETID: I need to use the form. It's the only way that i can do it. It has to be completely user-friendly, with no complications. So, i don't believe that will work.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

Hmmm...Never say never....the cells that you let users change can be "data validated" so that they only can select/change the values from a predifine list or type.

 

[Rmck87]

meaning what? Would this be able to use the form?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

Luceze: Your idea doesn't sound so bad, and if i could just get my macros to work i would try it. But right now i am at a stand still with using macros until i figure out how to fix it.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

Yes, but you get back into coding...

If you recreate a spreadsheet version of the form it's probably going to be easier.

 

[Rmck87]

How much would i have to change my coding? Because i have already done a lot of work on this form. And if i am changing it from Access to Excel it might be more work than its worth. Because if either xlbo's idea or Luceze's idea works i might rather go with that, than to retype, or even copy and paste but recreate the buttons, and combos, and options.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

You would not have to change any code...there is none.
MsQuery will go directly to the tables or an "open ended query" (I.E. no criteria just joined tables)

so what ever you have so far will remain untouched.


But if you want to go the coded route
Both of those guys...are top notch...so their code will work
it's just a matter of details on your end.

 

[Rmck87]

I think i may go the coded route, and thanks for all of your help ETID. I appreciate the time you have taken to help me. Thanks again.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.