By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Open specific spreadsheet from Access

P: n/a
Hi, A quick question. I have been battling with this code all morning,
please help.

Here is the code

Dim fPath1 As String
Dim fPath2 As String

fPath1 = "C:\Program Files\Microsoft Office\Office\EXCEL.EXE"
fPath2 = "C:\Documents and Settings\jenniferr\My Documents\Generic
report.xls"

'This opens Excel, but not the spreadsheet I need to open
OpenExcel = Shell(fPath1, vbMaximizedFocus)

'This and all the other stuff I have tried generates the error
"Invalid proceedure call or argument"
OpenExcel = Shell("C:\Documents and Settings\jenniferr\My
Documents\Generic report.xls", vbMaximizedFocus)

I have tried all sorts of other combinations of code to open Generic
report.xls, but to no avail. I am sure it is a silly syntax error,
but I quite simply do not know what. Thanks in advance for any
comments.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi Jenni,

Try this procedure:

Private Sub cmdOpenExcel_Click()
On Error GoTo ProcError

Dim filePath As String
Dim retVal As Double

filePath = "C:\Documents and Settings\jenniferr\My Documents\Genericreport.xls"
retVal = Shell(GetExeFileSpec("Excel.Application") & Space$(1) & filePath, vbNormalFocus)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Error in cmdOpenExcel_Click
event procedure..."
Resume ExitProc
End Sub

_______________________________________

"Jenni" <jr*******@khulisa.com> wrote in message
news:db*************************@posting.google.co m...
Hi, A quick question. I have been battling with this code all morning,
please help.

Here is the code

Dim fPath1 As String
Dim fPath2 As String

fPath1 = "C:\Program Files\Microsoft Office\Office\EXCEL.EXE"
fPath2 = "C:\Documents and Settings\jenniferr\My Documents\Generic
report.xls"

'This opens Excel, but not the spreadsheet I need to open
OpenExcel = Shell(fPath1, vbMaximizedFocus)

'This and all the other stuff I have tried generates the error
"Invalid proceedure call or argument"
OpenExcel = Shell("C:\Documents and Settings\jenniferr\My
Documents\Generic report.xls", vbMaximizedFocus)

I have tried all sorts of other combinations of code to open Generic
report.xls, but to no avail. I am sure it is a silly syntax error,
but I quite simply do not know what. Thanks in advance for any
comments.
Nov 12 '05 #2

P: n/a
Hi Tom

Thanks for the reply, but I'm still breaking on

GetExeFileSpec

The message is Compile error, Sub or Function not defined.

Is this a reference I need to set, or something else I don't know. I'm a
newbie at this stuff, so I do appreciate the help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
"Tom Wickerath" <AO***********************@comcast.net> wrote in
news:mv********************@comcast.com:
Hi Jenni,

Try this procedure:

Private Sub cmdOpenExcel_Click()
On Error GoTo ProcError

Dim filePath As String
Dim retVal As Double

filePath = "C:\Documents and Settings\jenniferr\My Documents \Genericreport.xls" retVal = Shell(GetExeFileSpec("Excel.Application") & Space$(1) & filePath, vbNormalFocus)
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , "Error in cmdOpenExcel_Click event procedure..."
Resume ExitProc
End Sub

_______________________________________

"Jenni" <jr*******@khulisa.com> wrote in message
news:db*************************@posting.google.co m...
Hi, A quick question. I have been battling with this code all morning,
please help.

Here is the code

Dim fPath1 As String
Dim fPath2 As String

fPath1 = "C:\Program Files\Microsoft Office\Office\EXCEL.EXE"
fPath2 = "C:\Documents and Settings\jenniferr\My Documents\Generic
report.xls"

'This opens Excel, but not the spreadsheet I need to open
OpenExcel = Shell(fPath1, vbMaximizedFocus)

'This and all the other stuff I have tried generates the error
"Invalid proceedure call or argument"
OpenExcel = Shell("C:\Documents and Settings\jenniferr\My
Documents\Generic report.xls", vbMaximizedFocus)

I have tried all sorts of other combinations of code to open Generic
report.xls, but to no avail. I am sure it is a silly syntax error,
but I quite simply do not know what. Thanks in advance for any
comments.


Perhaps:

Application.FollowHyperlink "File://C:\Documents and Settings\jenniferr\My
Documents\Generic report.xls"

(without the line break, of course)
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #4

P: n/a
Hi Jenni,

I'm sorry. I forgot to include the function GetExeFileSpec in my original post! Please
let me know if you can get it to work now.

Tom Wickerath

Public Function GetExeFileSpec(strEXEHostClass As String) As String
' Comments : gets EXE path and file from registry
' Parameters : an entry in HKEY_CLASSES_ROOT
' Created : 01/10/2003 RSC
' Modified : 03/02/2003 RSC
' Modified : 05/23/2003 RSC added Internet Explorer, removed dependencies on
other stuff
' Modified : 05/23/2003 D. Conlin Added Power Point.
'
' NOTE: This function was sent by Tom Wickerath (Boeing Chemical Engineer and Access
instructor
' at Bellevue Community College, WA), who got it from Teresa Eade (President of
Pacific Northwest
' Access Developer's Group), who got it from Dick (?Richard S. C...?).
' --------------------------------------------------

Const PROCNAME = "GetExeFileSpec"

On Error GoTo PROC_ERR

Dim appObj As Object
Dim strLastError As String
Dim strAppHost As String
Dim strPrompt As String
Dim strPath As String
Dim strFileName As String
Dim strFileSpec As String
Dim iii As Long

iii = InStr(strEXEHostClass, ".")

If (iii > 1) Then
strAppHost = Left(strEXEHostClass, iii - 1)
Else
strAppHost = ""
End If

Select Case (UCase(strAppHost))
Case "ACCESS":
Set appObj = CreateObject(strEXEHostClass) ' Access.Application
strPath = appObj.SysCmd(9) '
(9=acSysCmdAccessDir) (includes"\")
strFileName = "MSACCESS.EXE"
strPrompt = "Microsoft Access"

''ReturnValue = SysCmd(action[, text][, value])
''The following set of constants provides information about Microsoft Access.
''acSysCmdRuntime Returns True (-1) if a run-time version of Microsoft Access is
running.
''acSysCmdAccessVer Returns the version number of Microsoft Access.
''acSysCmdIniFile Returns the name of the .ini file associated with Microsoft
Access.
''acSysCmdAccessDir = 9 Returns the name of the directory where Msaccess.exe is
located.
''acSysCmdProfile Returns the /profile setting specified by the user when
starting Microsoft Access from the command line.
''acSysCmdGetWorkgroupFile Returns the path to the workgroup file (System.mdw).

Case "EXCEL":
Set appObj = CreateObject(strEXEHostClass) ' Excel.Application
strPath = appObj.Path & "\"
strFileName = "EXCEL.EXE"
strPrompt = "Microsoft Excel"

Case "WORD":
Set appObj = CreateObject(strEXEHostClass) ' Word.Application
strPath = appObj.Path & "\"
strFileName = "WINWORD.EXE"
strPrompt = "Microsoft Word"

Case "INTERNETEXPLORER":
Set appObj = CreateObject(strEXEHostClass) '
InternetExplorer.Application
strPath = appObj.Path ' (includes "\")
strFileName = "IEXPLORE.EXE"
strPrompt = "Microsoft Internet Explorer"

Case "POWERPOINT":
Set appObj = CreateObject(strEXEHostClass) ' PowerPoint.Application
strPath = appObj.Path & "\"
strFileName = "POWERPNT.EXE"
strPrompt = "Microsoft PowerPoint"

Case "PHOTODRAW": ' Causes Error: ActiveX component
can't create object.
Set appObj = CreateObject(strEXEHostClass) ' PhotoDraw.Application
'strPath = appObj.Path ' (includes
"\")
strPath = appObj.Path & "\"
strFileName = "PHOTODRW.EXE"
strPrompt = "Microsoft PhotoDraw"

Case "FRONTPAGE": ' Can't test this until Front Page 2K is loaded
properly on this computer.
Set appObj = CreateObject(strEXEHostClass) ' FrontPage.Application
strPath = appObj.Path ' (includes
"\")
'strPath = appObj.Path & "\"
strFileName = "FRONTPG.EXE"
strPrompt = "Microsoft FrontPage"

Case Else:
strPath = ""
strFileName = ""
strLastError = "Not handled: " & strEXEHostClass

End Select

PROC_EXIT:
On Error Resume Next

appObj.Quit
Set appObj = Nothing

If (strLastError = "") Then
GetExeFileSpec = strPath & strFileName
Else
GetExeFileSpec = "Failure - " & strLastError

MsgBox strLastError
End If

On Error GoTo 0

Exit Function

PROC_ERR:
strLastError = PROCNAME & " -- " & Err.Number & ". " & Err.Description

Resume PROC_EXIT

End Function ' GetExeFileSpec( )

_________________________________________

"Jenni Robinson" <jr*******@khulisa.com> wrote in message
news:3f*********************@news.frii.net...

Hi Tom

Thanks for the reply, but I'm still breaking on

GetExeFileSpec

The message is Compile error, Sub or Function not defined.

Is this a reference I need to set, or something else I don't know. I'm a
newbie at this stuff, so I do appreciate the help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.