Really Simple Question (Hopefully!)

[jennilewis]

I am trying to get VBA to open an excel spreadsheet. It is perfectly happy when I use a file with a pathname that doesn't contain any spaces but when i try and open a file from e:\common files it won't work.
I'm sure this is really simple but I just can't work out how to make it open!
Thanks

 

[skea]

Try this function.

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 Function OpenFile(sFileName As String)
OpenFile = ShellExecute(Application.hWndAccessApp, "Open",sFileName, "", "e:\common files", 1)
End Function

On the Onclick button

OpenFile ("YourFileName.xls")

Hope it helps.

 

[jennilewis]

Not quite sure where I am supposed to put that! My VBA knowledge is very basic!

I currently have:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

Dim stAppName As String

stAppName = "excel.exe E:\Common files\Database\non"
Call Shell(stAppName, 1)

Exit_Command25_Click:
Exit Function

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Function

 

[KenHigg]

Maybe:

stAppName = "excel.exe 'E:\Common files\Database\non'"

???

 

[jennilewis]

Fantastic!
Thank you

 

[skea]

Ken, Your routines brings up two copies of the extension as in
"E:\Common files\Database\non\YourFileName.xls.xls"



Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

Dim stAppName As String

stAppName = "excel.exe E:\Common files\Database\non\YourFileName.xls"
Call Shell(stAppName, 1)

Exit_Command25_Click:
Exit Function

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Function

 

[jennilewis]

Grrrr, this worked yesterday but today it says it expects a variable or procedure not a module

 

[skea]

Where is the error tracked to.

 

[KenHigg]

Maybe:

stAppName = "excel.exe 'E:\Common files\Database\non\mySpreadsheet.xls'"

or:

stAppName = "c:\Myexcelpath\excel.exe 'E:\Common files\Database\non\mySpreadsheet.xls'"

or just:

stAppName = "'E:\Common files\Database\non\mySpreadsheet.xls'"

???

 

[ghudson]

Searching the forum is a great way to find the answers to your questions.

I have a working example of the ShellExecute function in my Browse [Find a directory or file] sample. It will demonstrate how to open a selected file and a lot more.