Open specific spreadsheet from Access

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.

re: Open specific spreadsheet from Access

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" <jrobinson@khulisa.com> wrote in message
news:db60328.0310270056.41180e3c@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.

re: Open specific spreadsheet from Access

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!

re: Open specific spreadsheet from Access

"Tom Wickerath" <AOS168RemoveThisSpamBlock@comcast.net> wrote in
news:mv2dnY-IhbnqeQGiRVn-vg@comcast.com:
[color=blue]
> 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[/color]
\Genericreport.xls"[color=blue]
> retVal = Shell(GetExeFileSpec("Excel.Application") & Space$(1) &[/color]
filePath, vbNormalFocus)[color=blue]
>
> ExitProc:
> Exit Sub
> ProcError:
> MsgBox "Error " & Err.Number & ": " & Err.Description, , "Error in[/color]
cmdOpenExcel_Click[color=blue]
> event procedure..."
> Resume ExitProc
> End Sub
>
> _______________________________________
>
> "Jenni" <jrobinson@khulisa.com> wrote in message
> news:db60328.0310270056.41180e3c@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.[/color]

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)

re: Open specific spreadsheet from Access

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" <jrobinson@khulisa.com> wrote in message
news:3f9f5556$0$195$75868355@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!