RunApp to open file

[agibsonsw]

Hello. Office 2003.
I'm trying to use the RunApp macro to open an Excel spreadsheet, with the command line:
"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" "C:\Documents and Settings\student2\My
Documents\"&[txtFileName]&format(date()," dd mmm yyyy")&".XLS"

I've tried several different versions (with and without .xls etc.) but can't get it to open the file. I suspect
that reading part of the file name from a text box is causing the problem.
Any suggestion? Thanks, Andy.

[HansV]

RunApp doesn't play nice with concatenated strings. Here is a method to get around it.

Create a new module in the Visual Basic Editor (Insert | Module)
Copy the following code into the module:

Code: Select all

Public Declare Function ShellExecute _
  Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hwnd As Long, _
  ByVal lpOperation As String, _
  ByVal lpFile As String, _
  ByVal lpParameters As String, _
  ByVal lpDirectory As String, _
  ByVal nShowCmd As Long) As Long

Public Const SW_SHOWNORMAL = 1
Public Const SW_SHOWMAXIMIZED = 3

Create the following On Click event procedure for the button (or whatever) that should open the workbook:

Code: Select all

Private Sub cmdOpenWorkbook_Click()
  ShellExecute Application.hWndAccessApp, "Open", _
    "C:\Documents and Settings\student2\My Documents\" & _
    Me.txtFileName & Format(Date," dd mmm yyyy") & ".xls", _
    0&, 0&, SW_SHOWNORMAL
End Sub

If you'd like Excel to be opened in a maximized window, change SW_SHOWNORMAL to SW_SHOWMAXIMIZED.

[agibsonsw]

Hello.
That's good, although a shame that I can't do this with a simple macro. Thanks, Andy.