Combining 2 VB functions

  • Thread starter Thread starter Johanna Gronlund
  • Start date Start date
J

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
 
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.
 
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
 
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"
 
Back
Top