How do i automatically export data from Access to Excel

Hi, wasn't sure where to post this, so general seemed the way to go. I am required to produce a weekly Excel spreadsheet containing the data held within two tables on my database, i have set up a query that collates and arranges all the neccessary data into the right format so it can be simply copied and pasted into excel. This may just be wishful thinking on my part but i would love if i could automate this process. Come this friday I am handing over control of the database to a group of end users that will need to produce this in my stead, I would very much like for the system to automatically export this data once a week rather than having the users to go into the tables/queries of the system to pull the data up. In lieu of an automatic system being feasible, would it be possible to create a form command that would export the data across and again remove the need for the user to go into the tables/queries. I look forwards to any responses and thank you all in advance Many Thanks

iPirate.

look into the docmd.transferspreadsheet command in vba. you can probably put it behind a form button.

Is your goal to produce a spreadsheet that looks like an Excel version of your table or query results? - if so, then yes - transferspreadsheet will do it (and it's fairly easy to set up).

If you're looking to push the data into a spreadsheet that has formatting such as headings, borders, colours, other textual information and calculated fields, you'll need to look at automation - which is quite a lot more complex to get started on, but still fairly straightforward after that.

Cheers for the responses Aj, right i'll have a look, but to be honest i'm a total amateur with anything VBA related so i'll be no more than a tourist prodding at commands within the code. Shrimp, it would be a case of automation as the spreadsheet needs formating by way of colours and filters. Do you know of any reference resources i might be able to find to help me with this? Many Thanks

iPirate

Hi, I had started my reply before I saw the latest posts, so I post it allthough you need more than this. This is what I would do: Set up a form. In it´s timer() event, put in something like this:

Private Sub Form_Timer() Dim exportdate As String 'check to see if it´s friday If Weekday(Now) = 6 Then exportdate = DateValue(Now) 'if so, transfer the spreadsheet, using the date as filename if you like. For example: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "yourtable", "c:\" & exportdate & ".xls" End If End Sub

Fuga.

Ah definetley helpful Fuga! Thanks for that.

Good! Hope it works. I didn´t actually run it, but it should be something like that. One thing: The timer is good to use when you have a form open all the time. The timer starts when you open the form, and if you close it it stops. If you rather want to open a form and export right away, you use the form_open() event, or you put a button on your form and use its click event. Here are two examples that I use. I call the subs from other subs, but you can call them from buttons or aother events. The second sub uses a little automation of excel. But to be honest, the simplest way is to create a macro in excel and the call it from access. In excel you can use the macro recorder. In the code below, that would mean you put exapp.run("yourmacro") after opening your workbook. Note also that the code deletes and creates new files. If you put your macro in one of the files it will be deleted. You need to have your makro in another file, and also open that one. Note that I have export specifications that I use.

Sub excelexport() On Error Resume Next Kill "c:\data\book1.xls" Kill "c:\data\book2.xls" DoCmd.TransferText acExportDelim, "MyExportspecifikation", "tbl3", "c:\data\book1.xls" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Indiv_current_posistion", "c:\data\book2.xls", True End Sub Sub currentposition() Dim intRcount As Integer Dim exapp As Excel.Application excelexport DoCmd.SetWarnings False Set exapp = CreateObject("excel.application") exapp.Workbooks.Open ("c:\data\Book2.xls") exapp.run("yourmacro") exapp.Quit End Sub

Fuga.

There's an excellent introduction here:

http://www.mvps.org/access/modules/mdl0006.htm

Getting a whole list of records into a sheet using automation does mean you have to iterate through the recordset and deal with them one by one (and field by field), instead of just exporting a whole table or query in one operation, but there are examples on how to do that here: http://www.mvps.org/access/modules/mdl0035.htm

Range("B4").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With

With objActiveWkb With Worksheets(1) .Range("B4").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End With End With

Excellent, thanks for all the help guys. You've bee nfantastic. Next time you make it to Bracknell i'll buy you a beer.

Time to play around with excel Macro's for a bit

iPirate

You can also set up a windows task to open the database when you want it. The you can setup an AutoExec macro in access to execute the code when it opens and close the database when complete. I am doing something very similar and this process works well for me.