Combining codes

[Johanna Gronlund]

Hello,

I have two subs that I want to combine into one. This is so that I can run
them with one click of a button from the switchborad. Ideally, I would want
to do this by using code rather than macros. Can anyone help? I have pasted
the two codes below:

Sub OpenExcelReport()

DoCmd.TransferSpreadsheet acExport, , "Qry_Enhanced Services Payments",
"G:\Information Analysis - shared\Databases\Enhanced
Services\2008-09\Enhanced Services Payments 0809.XLS"
Shell "excel.exe ""G:\Information Analysis - shared\Databases\Enhanced
Services\2008-09\Enhanced Services Payments 0809.XLS""", vbNormalFocus


End Sub

Sub OpenESReport()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"Qry_Enhanced Services Payments", "G:\Information Analysis -
shared\Databases\Enhanced Services\2008-09\Enhanced Services Payments
0809.XLS", True


End Sub

Thanks very much,

Johanna

 

[John Spencer]

You could just call the two subs in the button's click event

OpenExcelReport
DoEvents 'May not be needed, but it can't hurt
OpenESReport

Or you could copy the code from each sub into the button's click event. You
may have timing issues with running the two subs that quickly.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Johanna Gronlund]

Thanks John,

I tried the first option and it worked. Thanks very much.

For some reason there is a problem when closing down the document (I don't
get this when the two codes are run separately). After the excel document is
closed, there is a message that 'the document is now available for editing'.
When I click 'cancel' the system seems to freeze and I also get a 'run time
error 3010' and I have to click end. Sorry if the description isn't very
clear - I am not sure what is happening. Any ideas what I could do to stop it?

Thanks,

Johanna