Going from an Access database to Excel Using a Macro? 2

[BChumie]

Hello,

Is there a way to write a macro so that It will run a query in MS Access and then open up a new excel spreadsheet and paste my query contents into the new workbook? (Sorry if this is simple I am new to this stuff )

Any Help would be greatly appreciated.

Brad

 

[KyleS]

Do you want to make your query the workbook, or do you have a specific place you need it to go? (eg, cell "A27&quot

If you just want it to go into a new workbook that you specify, just paste this code behind your button


Sub MoveToExcel()
DoCmd.OutputTo acOutputQuery, "QueryNameHere", acFormatXLS, "C:\MyPathName"
End Sub

If you need more help (on ANY part of this) please post back so I can give you more detail.

Hope this helps, Kyle

 

[ETID]

You would not need to export the data to excel, (too many steps)...

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 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...fill formulas etc.

 

[BChumie]

Hey Kyle,

Thanks... that was exactly what I was looking for. Now is there anyway to automatically open the excel spreadsheet I just made?

In your debt,

Brad

 

[KyleS]

Sure, just add this code. ETID is right about using the Query, but I think it's a little more complicated especially if you're looking to do it all programatically, you have to open the workbook, then refresh, then do whatever it is you want to do, where if you export and write over the old file the data will update automatically. It's a matter of preference - I use either one, depending on the situation and who's going to have to trouble-shoot it if I'm not available.

Sub MoveToExcel()
DoCmd.OutputTo acOutputQuery, "QueryNameHere", acFormatXLS, "C:\MyPathName"
Dim tmpApp As New Excel.Application

tmpApp.Workbooks.Open FileName:="FileNameGoesHere", ReadOnly:=False, ignorereadonlyrecommended:=True
tmpApp.Visible = True
Set tmpApp = Nothing

End Sub

The bold part will open Excel and open the specific workbook. You'll need to make sure you have the reference set to Excel.

To do this, go into the design of a module and go to Tools-->References (In the menu) and make sure "Microsoft Excel 8.0 Object Library" is checked. (Version number will be different depending on what version of Excel you have) Kyle

 

[BChumie]

Thanks again for all the help. Both suggestions were very useful.

Brad

 

[ETID]

As far as refreshing the data....you can set that propertie in Excel ...just right click anywhere in the "returned data" area and from properties, select the refresh on open option.

 

[jbin99]

Guys - I noticed your conversation here and also have questions about exporting a query from access to excel. I have used the Docmd.OutputTo, which works fine for exporting an entire query to a specific file. I am having a problem figuring out how to sort and export the query by a variable since the SQL statements must be literal. In my situation I will have a query returning names that are assigned to various group numbers. Each month the number of people in a group, as well as the number of groups themselves will fluctuate. Is it possible to programmatically in Access send everyone in group 1 to an excel file (file1, worksheet2) and everyone in group 2 to another excel file (file2, worksheet2)? Does DoCmd.OutputTo allow conditions, if so can the condition contain a variable? Or is there a better way to do this.

 

[ETID]

If you read a little closer,...you may not need to write any code.


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 at this point...
I would recommend the MsQuery option.

in the editor for MsQuery you can set a parameter in the value area of a criteria (make sure you turn on criteria's from the menu) ...to set a parameter or prompt simply type the prompt in brackets,...I.E. if you want all records from a certain date till now then type <[Enter Date] as a criteria,...try it you'll see how it works very quickly.


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

 

[jbin99]

ETID, I'm building this into the context of an automated Access DB. The process requires no manual intervention. Is it possible to automate the excel live link to the query that you are talking about using Access VBA?

 

[ETID]

Have you tried to create a link from within Excel yet?

If not,..it's quick and easy...give it a try

you can also set the data to refresh on open of the xls
Then when you open the xls, the query or table that it is linked to runs in the back ground and updates the xls.


By linking...it means just that, it's live, point to any Access (and others) query(s) or table(s) that you choose.
Access need not be open.

you can set properties for read only, fill formulas and others....

No code needed...it's all taken care of by Excel

...or am I missing something?

 

[KyleS]

Hi guys,
Depending on what jbin99 is wanting to do there are a few ways of pulling this off. You can set the query to refresh on open or you can tell it to refresh through code (access or Excel VBA).

I would just create 2 copies of your query (1 for group 1 and 1 for group 2) and link them in where appropriate and once complete the amout of code you need will be minimal depending on what you want to do in Excel. Because this data will update regarless of wheter or not you Access dB has been used lately assuming you set the query to refresh on open. Kyle

 

[jbin99]

Guys - Thanks for the help. I'll try the live links and multiple queries. Actually, you gave me an idea. I may just try to read the original table that contains my key (group #) and possibly run the query with multiple cases based on each group number. Again, thanks for the help.

 

[KyleS]

ETID,

You are missing something... a Star here you go! Kyle

 

[ETID]

thanks,...but not sure if I was goin in the same direction with the approach to the problem