Combining 2 VB functions

[Johanna Gronlund]

Hello,

I am trying to combine two working functions:
1. Updates a spreadsheet
2. Opens the spreadsheet

I want to have combine these two so that the users only need to press one
button that automatically updates and then opens the database.

I have pasted the text from VB if that helps:

Option Compare Database

Sub OpenExcelReport()

DoCmd.TransferSpreadsheet acExport, , "Qry_Enhanced Services Payments",
"G:\folder\Database\2008\Payments 08.XLS"
Shell "excel.exe "G:\folder\Database\2008\Payments 08.XLS", vbNormalFocus

End Sub



Sub OpenESReport()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"Qry_Enhanced Services Payments", "G:\folder\Database\2008\Payments 08.XLS",
True


End Sub

Also, I am not quite sure what the 'shell' part of the function does. If
anyone can put it in simple terms that would also be highly appreciated.

Many thanks in advance,

JG

 

[Klatuu]

The Shell function is used to launch a Windows application from within VBA
code.
The only problem I see is a syntax error in the Shell statement.

Shell "excel.exe "G:\folder\Database\2008\Payments 08.XLS", vbNormalFocus
Should be
Shell("excel.exe ""G:\folder\Database\2008\Payments 08.XLS""",
vbNormalFocus)

Shell expects one string, but Excel is having trouble finding the file
because there are spaces in the name and it only sees
G:\folder\Database\2008\Payments and can't find it. The double double quotes
send a sting to Shell that encloses the file name in quotes which is then
passed to Excel so it sees the entire file name.

 

[Johanna Gronlund]

Thanks - that's helpful.

Is there a way of having just one switchboard button to both update and open
the excel sheet? Do I need to change the code to combine the two functions,
write another one or change my switchboard button to include both codes
somehow?

Thanks,

JG

 

[Klatuu]

You could use one button to run a macro. You can include both in a macro.
Use the TransferSpreadsheet Action to do the export
Use the RunApp Action to open Excel. Just put this in the Commad Line box:
excel.exe "G:\folder\Database\2008\Payments 08.XLS"