Transfer spreadsheet in Access 97

QUINABA

Board Regular
Joined
Jul 18, 2002
Messages
127
Is it possible for Access to export a table to Excel and when you open the spreadsheet a macro would automatically run?

Thanks!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Dugantrain

Active Member
Joined
Feb 10, 2003
Messages
354
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

Board Regular
Joined
Jul 18, 2002
Messages
127
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

Active Member
Joined
Feb 10, 2003
Messages
354
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

Board Regular
Joined
Jul 18, 2002
Messages
127

ADVERTISEMENT

Ok, thanks!

At least you got a headstart on the party. :biggrin:
 

Dugantrain

Active Member
Joined
Feb 10, 2003
Messages
354
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.
Code:
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:

Code:
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

Board Regular
Joined
Jul 18, 2002
Messages
127
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,323
Messages
5,769,454
Members
425,549
Latest member
Bartekelese

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top