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.

 

[luceze]

In Excel go to data\ImportExternalData\NewDatabaseQuery then select MS Access Database. Browse to your database select it from the list at the left then click OK. You should then be able to follow the wizard to return the data to your worksheet.

HTH,
Eric

 

[SuicidED]

You could use the transfer spreadsheet in Access to send the query to excel.

 

[Rmck87]

How would i do that? I'm not familiar with Excel at all. So a little guidance would be of great appreciation! Thanks.

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

 

[luceze]

Did you want to use the transfer spreadsheet method or the MSQuery method? You run MSQuery from Excel and transfer spreadsheet from Access VBA.

 

[Rmck87]

luceze: if i were to transfer the spreadsheet, would i have to transfer every time i wanted to open the saved query... or would i be able to open the query without having to transfer. Even though the query will be changing often...?

suicidED: i need some more information on your idea.

im going to try them both, to see which one i like the best. Thanks.

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

 

[SuicidED]

Write a macro in access with the action as TransferSpreadsheet. In the bottom boxes
Transfer Type - Export
Spreadsheet Type - Choose
Table Name - Your query name
File Name - Path where you want to put the spreadsheet
Has Field Names - Yes (probably)

Run the macro

 

[Rmck87]

Luceze: your idea works, but i want to make it a little easier for someone to bring up the query. How can i do this. Is there any way that i can have them select a query without having to select the columns and order by and sort by fields? Because the query will already have all of the criteria ready. Just need to print it out in excell.

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

 

[xlbo]

This should get you goinf:
Sub Use_DAO_to_RunQueryInAccess()
Dim dbs As Database, rst As Recordset, wks As Workspace, ws As Worksheet

Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)

Set dbs = wks.OpenDatabase("D:\Home\Access\GB_Universe.mdb", True)

Set rst = dbs.OpenRecordset("QueryName&quot
'If Err.Number < 0 Then GoTo ErrorExit
Set ws = Worksheets(&quot;sheet1&quot
With ws
.Activate
.Cells.ClearContents

For iCol = 1 To rst.Fields.Count
.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

.Cells(1, 1).CurrentRegion.Font.Bold = True
.Cells(2, 1).CopyFromRecordset rst
End With
'End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
wks.Close
Set wks = Nothing
Set ws = Nothing
Exit Sub
ErrorExit:
' error trapping...
End Sub

This opens a specific access database, runs a query (that you specify) and returns the records to &quot;Sheet1&quot; in excel

You'll need to set a reference to your latest version of DAO in VBE - Tools>References

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[luceze]

Using the transfer spreadsheet method you would have to do the transfer everytime you wished to move the data to excel. Linking the data to the query from Excel as I mentioned above will keep the data current but depending on how you set up the link design changes, such as adding fields to your Access query, may not be reflected in your excel data.

 

[luceze]

Wow! Lots of fast posts. Geoff, as always, has a better solution than I could ever give. I like your method better and will now try it in my own spreadsheets.

Eric

 

[Rmck87]

Geoff: first question, where do i place this sub? ie... macro, form, vb code, excell? second question, How do i make a refrence to the 'latest version of DAO in VBE - Tools>References '? Thanks for the help!

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

 

[xlbo]

Rmck87:
This should be pasted into a standard module in your excel workbook

change the &quot;set dbs&quot; line to reference the path to the database that you want to use the query in
change the &quot;set rst&quot; line to include the name of the query to run

To create the reference, goto your VBE
Goto Tools>References
Scroll down till you find Microsoft DAO and tick the latest version you can find

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Rmck87]

I'm sorry, im very inexperienced with Excell, and thank you for your patience, but what is a standard module? is that just one of the columns on a sheet? Then another question is, how will i run the Query in Excell. Or will it run whenever i run it in Access? or will i have to load it? 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.

 

[ETID]

Is there an advantage to writing code,...instead of using the built in [Data>Get External data] functionality that is already built into excel,...?

 

[Rmck87]

I found and ticked the latest version, and i found and created a module in Excell. But, it doesn't seem to like the idea of the clause 'Dim dbs As Database' and message box comes up saying &quot;Compiler Error:
User-defined type not defined&quot;

I also still do not know how to run the module.

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

 

[ETID]

Then why not use the buit in tool for this?

 

[Rmck87]

And how do i do that ETID?

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

 

[ETID]

To Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date access data comes in (if you set the refresh on open option)

Note: if your access query has prompt paramters in it, move them to the MsQuery interface,...

 

[xlbo]

If it doesn't like Dim dbs as database, try setting a reference to Microsoft Access Objects (also in Tools>References)

ETID - doesn't look like it - didn't realise you could run predefined queries using MSQuery

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244