Transfer spreadsheet in Access 97

[QUINABA]

Is it possible for Access to export a table to Excel and when you open the spreadsheet a macro would automatically run?

Thanks!

 

[Dugantrain]

To answer your question, yes, it is possible. The TransferSpreadsheet method will allow you to transfer data to Excel. I find that it has severe limitations, though; I prefer to use Excel Automation (using the Excel Object Model) within Access to either create a new Excel file or find an existing one, grab a recordset, and then put that data into Excel. Takes a fair amount of code, though, whereas DoCmd.TransferSpreadsheet is only a couple of lines of code. As far as the Macro, if it's an Excel Macro, it would automatically run if you called it from the Workbook's On_Open event.

 

[QUINABA]

Thanks for your reply.

I'm pretty much a begginer in Access. The extent of my knowledge is just creating tables and queries and exporting them to Excel. Your solution sounds very interesting and I would like to learn more. Could you provide some examples?

 

[Dugantrain]

Yes, I'll post some examples on Monday; all of my files are at work and I'm at home with a rotten case of food-poisoning (or a hangover, it's hard to tell).

 

[QUINABA]

Ok, thanks!

At least you got a headstart on the party.

 

[Dugantrain]

First is an example of the Excel Automation. There's some pretty advanced concepts here, like Late-Bound Objects, passing arguments, etc., but it'll give you an idea.

Option Compare Database
Option Explicit

Public Function fCreate_Excel()
'Creates an Excel file and populates it with query data.
'Uses Late-Binding as there is no way to know ahead of time which version of Excel
'the user will have and which Excel Object Library they will require.
On Error GoTo errCreate_XL
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.UserControl = False
xlApp.SheetsInNewWorkbook = 1 'Only one Worksheet. Why does XL default to 3? What a 'hassle!
xlApp.Workbooks.Add
Set xlWS = xlApp.Worksheets(1)
Call fXL_Export_Recs(xlApp, xlWS) 'Populate Worsheet with data
xlApp.UserControl = True
Set xlWS = Nothing  'Release all objects
Set xlWB = Nothing
Set xlApp = Nothing
Exit Function
errCreate_XL:
    xlApp.WindowState = -4140 'Minimize Excel
    Set xlWS = Nothing 'Release all objects
    Set xlWB = Nothing
    Set xlApp = Nothing
    MsgBox "An Error Occurred While Attempting To Create Your Excel File."
End Function

Public Function fXL_Export_Recs(xlExport_App As Object, xlExport_WS As Object)
'Routine to grab data and place it in Excel
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fldcount, iCol As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryNDRS_Results")
fldcount = rs.Fields.Count 'Populate XL with column Headings
For iCol = 1 To fldcount
    xlExport_WS.cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
xlExport_WS.cells(2, 1).copyfromrecordset rs 'Copy recordset to XL
Set rs = Nothing
Set db = Nothing
End Function

Here's an example of the more lightweight TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, , "Your_Query", "Your_Destination", True

Fill in "Your_Query" with the name of the table/query that you're exporting and "Your_Destination" with the filepath and filename of the Excel Spreadsheet that you're exporting to. Automation is the big winner here as, rather than saving to a pre-defined Spreadsheet on your hard drive and having to know its location, Automation will build the Spreadsheet on the fly in memory and then it's up to you to save it wherever you'd like.

 

[QUINABA]

Thank you very much!

I'll study your code and will try to apply for my application. Hopefully I can make it work.

Thanks for your feedback!
It's very much appreciated!