RE: Combining 2 VB functions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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"
--
Dave Hargis, Microsoft Access MVP


"Johanna Gronlund" wrote:

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" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Johanna Gronlund" wrote:

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
.

Relevant Pages

  • RE: Combining 2 VB functions
    ... The only problem I see is a syntax error in the Shell statement. ... but Excel is having trouble finding the file ... button that automatically updates and then opens the database. ...
    (microsoft.public.access.modulesdaovba)
  • Shell function. Open Excel swtiches
    ... I've written some vba code that uses the Shell ... function to open another instance of Excel which opens 'Report.xls' ... opening it when i need it, but i need it all the time. ...
    (microsoft.public.excel.programming)
  • Re: Challenge
    ... Are you wanting to drive word from Excel, just open word files or convert it ... To open word you can just use Shell, ... > on the selection runs a formula from the sheet and opens a relevant ... > Private Sub CmdOPEN_Click ...
    (microsoft.public.excel.programming)
  • Re: Macros between two seprate windows (excel)
    ... Do you really need to work with 2 instances of Excel? ... then use automation instead of Shell. ... run time error '1004.' ... ' Specifying 1 as the second argument opens the application in ...
    (microsoft.public.excel.programming)
  • Re: Check File Open Macro
    ... Dim oXL As Object 'This is needed to open a new instance of Excel. ... 'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL. ... Internet, To Do Lists, Travel Tickets & Hotel Reservations and THE LIST ...
    (microsoft.public.excel.programming)