Question Automatic export to Excel?

[iPirate]

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.

 

[ajetrumpet]

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

 

[Atomic Shrimp]

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.

 

[iPirate]

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

 

[Fuga]

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

Then you need to set the timer interval to, say, 24 hrs (in milliseconds).

This takes care of the exporting. but you might have to change the code a little to suit your needs.

Fuga.

 

[iPirate]

Ah definetley helpful Fuga! Thanks for that.

 

[Fuga]

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

Also do a search on the forum for excel automation. There is also a kb article on microsoft.com

Fuga.

 

[Atomic Shrimp]

There's an excellent introduction here:

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

Once you get that thing working (you need to set up the function linked at the top of that page too), you can add as much or as little code into the With objActiveWkb code block as you like.

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

The real beauty of Access to Excel automation is the relative ease of writing code. If you want to know how to, say, make a cell yellows, you just open Excel, set a macro recording, do the thing yourself, stop recording and view the macro - it looks something like this:

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

And this goes into your Access automation code as something like:

    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

 

[iPirate]

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

Thanks Again
iPirate

 

[Spichael]

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.