Open specific spreadsheet from Access

[Jenni]

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\E XCEL.EXE"
fPath2 = "C:\Documen ts and Settings\jennif err\My Documents\Gener ic
report.xls"

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

'This and all the other stuff I have tried generates the error
"Invalid proceedure call or argument"
OpenExcel = Shell("C:\Docum ents and Settings\jennif err\My
Documents\Gener ic report.xls", vbMaximizedFocu s)

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.

[Tom Wickerath]

Re: Open specific spreadsheet from Access

Hi Jenni,

Try this procedure:

Private Sub cmdOpenExcel_Cl ick()
On Error GoTo ProcError

Dim filePath As String
Dim retVal As Double

filePath = "C:\Documen ts and Settings\jennif err\My Documents\Gener icreport.xls"
retVal = Shell(GetExeFil eSpec("Excel.Ap plication") & Space$(1) & filePath, vbNormalFocus)

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

_______________ _______________ _________

"Jenni" <jrobinson@khul isa.com> wrote in message
news:db60328.03 10270056.41180e 3c@posting.goog le.com...
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\E XCEL.EXE"
fPath2 = "C:\Documen ts and Settings\jennif err\My Documents\Gener ic
report.xls"

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

'This and all the other stuff I have tried generates the error
"Invalid proceedure call or argument"
OpenExcel = Shell("C:\Docum ents and Settings\jennif err\My
Documents\Gener ic report.xls", vbMaximizedFocu s)

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.

[Jenni Robinson]

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!

[Lyle Fairfield]

Re: Open specific spreadsheet from Access

"Tom Wickerath" <AOS168RemoveTh isSpamBlock@com cast.net> wrote in
news:mv2dnY-IhbnqeQGiRVn-vg@comcast.com:
[color=blue]
> Hi Jenni,
>
> Try this procedure:
>
> Private Sub cmdOpenExcel_Cl ick()
> On Error GoTo ProcError
>
> Dim filePath As String
> Dim retVal As Double
>
> filePath = "C:\Documen ts and Settings\jennif err\My Documents[/color]
\Genericreport. xls"[color=blue]
> retVal = Shell(GetExeFil eSpec("Excel.Ap plication") & Space$(1) &[/color]
filePath, vbNormalFocus)[color=blue]
>
> ExitProc:
> Exit Sub
> ProcError:
> MsgBox "Error " & Err.Number & ": " & Err.Description , , "Error in[/color]
cmdOpenExcel_Cl ick[color=blue]
> event procedure..."
> Resume ExitProc
> End Sub
>
> _______________ _______________ _________
>
> "Jenni" <jrobinson@khul isa.com> wrote in message
> news:db60328.03 10270056.41180e 3c@posting.goog le.com...
> 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\E XCEL.EXE"
> fPath2 = "C:\Documen ts and Settings\jennif err\My Documents\Gener ic
> report.xls"
>
> 'This opens Excel, but not the spreadsheet I need to open
> OpenExcel = Shell(fPath1, vbMaximizedFocu s)
>
> 'This and all the other stuff I have tried generates the error
> "Invalid proceedure call or argument"
> OpenExcel = Shell("C:\Docum ents and Settings\jennif err\My
> Documents\Gener ic report.xls", vbMaximizedFocu s)
>
> 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.Fol lowHyperlink "File://C:\Documents and Settings\jennif err\My
Documents\Gener ic report.xls"

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

[Tom Wickerath]

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_RO OT
' 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(strEXEHos tClass, ".")

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

Select Case (UCase(strAppHo st))
Case "ACCESS":
Set appObj = CreateObject(st rEXEHostClass) ' Access.Applicat ion
strPath = appObj.SysCmd(9 ) '
(9=acSysCmdAcce ssDir) (includes"\")
strFileName = "MSACCESS.E XE"
strPrompt = "Microsoft Access"

''ReturnValue = SysCmd(action[, text][, value])
''The following set of constants provides information about Microsoft Access.
''acSysCmdRunti me Returns True (-1) if a run-time version of Microsoft Access is
running.
''acSysCmdAcces sVer Returns the version number of Microsoft Access.
''acSysCmdIniFi le Returns the name of the .ini file associated with Microsoft
Access.
''acSysCmdAcces sDir = 9 Returns the name of the directory where Msaccess.exe is
located.
''acSysCmdProfi le Returns the /profile setting specified by the user when
starting Microsoft Access from the command line.
''acSysCmdGetWo rkgroupFile Returns the path to the workgroup file (System.mdw).

Case "EXCEL":
Set appObj = CreateObject(st rEXEHostClass) ' Excel.Applicati on
strPath = appObj.Path & "\"
strFileName = "EXCEL.EXE"
strPrompt = "Microsoft Excel"

Case "WORD":
Set appObj = CreateObject(st rEXEHostClass) ' Word.Applicatio n
strPath = appObj.Path & "\"
strFileName = "WINWORD.EX E"
strPrompt = "Microsoft Word"

Case "INTERNETEXPLOR ER":
Set appObj = CreateObject(st rEXEHostClass) '
InternetExplore r.Application
strPath = appObj.Path ' (includes "\")
strFileName = "IEXPLORE.E XE"
strPrompt = "Microsoft Internet Explorer"

Case "POWERPOINT ":
Set appObj = CreateObject(st rEXEHostClass) ' PowerPoint.Appl ication
strPath = appObj.Path & "\"
strFileName = "POWERPNT.E XE"
strPrompt = "Microsoft PowerPoint"

Case "PHOTODRAW" : ' Causes Error: ActiveX component
can't create object.
Set appObj = CreateObject(st rEXEHostClass) ' PhotoDraw.Appli cation
'strPath = appObj.Path ' (includes
"\")
strPath = appObj.Path & "\"
strFileName = "PHOTODRW.E XE"
strPrompt = "Microsoft PhotoDraw"

Case "FRONTPAGE" : ' Can't test this until Front Page 2K is loaded
properly on this computer.
Set appObj = CreateObject(st rEXEHostClass) ' FrontPage.Appli cation
strPath = appObj.Path ' (includes
"\")
'strPath = appObj.Path & "\"
strFileName = "FRONTPG.EX E"
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@khul isa.com> wrote in message
news:3f9f5556$0 $195$75868355@n ews.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!