Hi all,

I'm trying to create a function to open any file from Excel with the
default associated programme. For this, I've used a (very) slightly
tweaked version of the ShellExecute code in Microsoft's KB article
170918 (below).

The code works fine when opening most files. However, when the file in
question is an .xls file, VBA seems to get confused and hangs. No
error message, nothing - the code just gets stuck at:

StartDoc = ShellExecute(Scr_hDC, "Open", DocName,"", "C:\",
SW_SHOWNORMAL)

I have a vague inkling that this is something to do with Excel trying
to run itself while it is busy trying to run itself, but not being a
programmer, I couldn't articulate it any better than that. Does anyone
have any suggestions, short of having two separate bits of code for
Excel and non-Excel files?

Best regards,

Tristan Jakob-Hoff


'START QUOTE


Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As _
String, ByVal lpszFile As String, ByVal lpszParams As String, _
ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Const SW_SHOWNORMAL = 1

Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&

Function StartDoc(DocName As String) As Long
Dim Scr_hDC As Long
Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, "Open", DocName, _
"", "C:\", SW_SHOWNORMAL)
End Function

Public Sub OpenDoc2(Filename As String)
Dim r As Long, msg As String
r = StartDoc(Filename)
If r <= 32 Then
'There was an error
Select Case r
Case SE_ERR_FNF
msg = "File not found"
Case SE_ERR_PNF
msg = "Path not found"
Case SE_ERR_ACCESSDENIED
msg = "Access denied"
Case SE_ERR_OOM
msg = "Out of memory"
Case SE_ERR_DLLNOTFOUND
msg = "DLL not found"
Case SE_ERR_SHARE
msg = "A sharing violation occurred"
Case SE_ERR_ASSOCINCOMPLETE
msg = "Incomplete or invalid file association"
Case SE_ERR_DDETIMEOUT
msg = "DDE Time out"
Case SE_ERR_DDEFAIL
msg = "DDE transaction failed"
Case SE_ERR_DDEBUSY
msg = "DDE busy"
Case SE_ERR_NOASSOC
msg = "No association for file extension"
Case ERROR_BAD_FORMAT
msg = "Invalid EXE file or error in EXE image"
Case Else
msg = "Unknown error"
End Select
MsgBox msg
End If
End Sub


'END QUOTE

On Jan 2, 11:58 am, "
wrote:
Hi all,

I'm trying to create a function to open any file from Excel with the
default associated programme. For this, I've used a (very) slightly
tweaked version of the ShellExecute code in Microsoft's KB article
170918 (below).

The code works fine when opening most files. However, when the file in
question is an .xls file, VBA seems to get confused and hangs. No
error message, nothing - the code just gets stuck at:

StartDoc = ShellExecute(Scr_hDC, "Open", DocName,"", "C:\",
SW_SHOWNORMAL)

I have a vague inkling that this is something to do with Excel trying
to run itself while it is busy trying to run itself, but not being a
programmer, I couldn't articulate it any better than that. Does anyone
have any suggestions, short of having two separate bits of code for
Excel and non-Excel files?

I think you're probably mostly correct - my guess is that the "open"
command is being sent to the instance of Excel in which your
ShellExecute function is running, and that instance is busy running,
er, ShellExecute!

I believe that the choice of whether to use an existing instance of an
application or to start a new one is governed by Registry settings for
the document class, probably somewhere in HKEY_CLASSES_ROOT. But that's
not an area where I'm very comfortable so I'll leave it at the
suggestion that you continue your research in that direction...

HTH,

Mike

Check if it's an .xls file first, and if so, use Workbooks.Open to open it.
If not, go to shell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ups.com...
Hi all,

I'm trying to create a function to open any file from Excel with the
default associated programme. For this, I've used a (very) slightly
tweaked version of the ShellExecute code in Microsoft's KB article
170918 (below).

The code works fine when opening most files. However, when the file in
question is an .xls file, VBA seems to get confused and hangs. No
error message, nothing - the code just gets stuck at:

StartDoc = ShellExecute(Scr_hDC, "Open", DocName,"", "C:\",
SW_SHOWNORMAL)

I have a vague inkling that this is something to do with Excel trying
to run itself while it is busy trying to run itself, but not being a
programmer, I couldn't articulate it any better than that. Does anyone
have any suggestions, short of having two separate bits of code for
Excel and non-Excel files?

Best regards,

Tristan Jakob-Hoff


'START QUOTE


Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As _
String, ByVal lpszFile As String, ByVal lpszParams As String, _
ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Const SW_SHOWNORMAL = 1

Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&

Function StartDoc(DocName As String) As Long
Dim Scr_hDC As Long
Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, "Open", DocName, _
"", "C:\", SW_SHOWNORMAL)
End Function

Public Sub OpenDoc2(Filename As String)
Dim r As Long, msg As String
r = StartDoc(Filename)
If r <= 32 Then
'There was an error
Select Case r
Case SE_ERR_FNF
msg = "File not found"
Case SE_ERR_PNF
msg = "Path not found"
Case SE_ERR_ACCESSDENIED
msg = "Access denied"
Case SE_ERR_OOM
msg = "Out of memory"
Case SE_ERR_DLLNOTFOUND
msg = "DLL not found"
Case SE_ERR_SHARE
msg = "A sharing violation occurred"
Case SE_ERR_ASSOCINCOMPLETE
msg = "Incomplete or invalid file association"
Case SE_ERR_DDETIMEOUT
msg = "DDE Time out"
Case SE_ERR_DDEFAIL
msg = "DDE transaction failed"
Case SE_ERR_DDEBUSY
msg = "DDE busy"
Case SE_ERR_NOASSOC
msg = "No association for file extension"
Case ERROR_BAD_FORMAT
msg = "Invalid EXE file or error in EXE image"
Case Else
msg = "Unknown error"
End Select
MsgBox msg
End If
End Sub


'END QUOTE

Jon Peltier wrote:

Check if it's an .xls file first, and if so, use Workbooks.Open to open it.
If not, go to shell.

- Jon


Thanks Jon,

Yes - it seems to be the easiest (only?) solution. I have slimmed the
code to the following:

' START QUOTE

Sub OpenDoc(DocCol As Integer)

Dim Filename As String
Dim Scr_hDC As Long

DocCol = DocCol + 2

With ThisWorkbook.Sheets("Documentation") ' Hidden sheet containing
dynamic list of filenames
Filename = .Cells(4, DocCol).Value
End With

If UCase(Right(Filename, 3)) = "XLS" Then
Workbooks.Open Filename:=Filename
Else
Scr_hDC = GetDesktopWindow()
ShellExecute Scr_hDC, "Open", Filename, "", "C:\", SW_SHOWNORMAL
End If

End Sub

' END QUOTE


One other question, though - is there any way to background the
ShellExecute events so that the rest of my code continues executing
while another programme (i.e. MS Word) is waiting for a user to respond
to a dialog box? There are a lot of Macro security warnings in some of
these documents, and if a user absent-mindedly opens a document and
forgets to deal with the security warning, code execution on the
spreadsheet will stop until they get around to it.

I know Excel is essentially a single-threading entity, but does that
apply to API calls as well?

Best regards,

Tristan

PS: Not the first time you've helped me and my company out, Jon - I
taught myself the fundaments of Office automation from your site, for
which thanks!)

Jon Peltier wrote:

Check if it's an .xls file first, and if so, use Workbooks.Open to open it.
If not, go to shell.

- Jon


Thanks Jon,

Yes - it seems to be the easiest (only?) solution. I have slimmed the
code to the following:

' START QUOTE

Sub OpenDoc(DocCol As Integer)

Dim Filename As String
Dim Scr_hDC As Long

DocCol = DocCol + 2

With ThisWorkbook.Sheets("Documentation") ' Hidden sheet containing
dynamic list of filenames
Filename = .Cells(4, DocCol).Value
End With

If UCase(Right(Filename, 3)) = "XLS" Then
Workbooks.Open Filename:=Filename
Else
Scr_hDC = GetDesktopWindow()
ShellExecute Scr_hDC, "Open", Filename, "", "C:\", SW_SHOWNORMAL
End If

End Sub

' END QUOTE


One other question, though - is there any way to background the
ShellExecute events so that the rest of my code continues executing
while another programme (i.e. MS Word) is waiting for a user to respond
to a dialog box? There are a lot of Macro security warnings in some of
these documents, and if a user absent-mindedly opens a document and
forgets to deal with the security warning, code execution on the
spreadsheet will stop until they get around to it.

I know Excel is essentially a single-threading entity, but does that
apply to API calls as well?

Best regards,

Tristan

PS: Not the first time you've helped me and my company out, Jon - I
taught myself the fundaments of Office automation from your site, for
which thanks!)

Tristan,
Whilst I would agree with the other posters that testing for .xls and using
automation from Excel files is better, it seems that the cause of the
problem is GetDesktopWindow().
RetVal = ShellExecute(GetDesktopWindow(), "open", "C:\test.xls",
vbNullString, CurDir, SW_SHOWNORMAL)

Whilst this works for other apps (well, only tested with WinWord, but..), it
causes Excel to hang. If you use the FindWindow API instead for the Excel
app, if works:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, _
ByVal
lpWindowName As String) _
As Long

RetVal = ShellExecute(FindWindow("XLMAIN", Application.Caption), "open",
"C:\test.xls", vbNullString, CurDir, SW_SHOWNORMAL)

Or if Excel 2002 and higher:
RetVal = ShellExecute(Application.hwnd, "open", "C:\test.xls", vbNullString,
CurDir, SW_SHOWNORMAL)

However, this does have the effect of starting another instance of Excel
(even with Application.IgnoreRemoteRequests = False), which may not what you
desire.

NickHK

wrote in message
ups.com...
Hi all,

I'm trying to create a function to open any file from Excel with the
default associated programme. For this, I've used a (very) slightly
tweaked version of the ShellExecute code in Microsoft's KB article
170918 (below).

The code works fine when opening most files. However, when the file in
question is an .xls file, VBA seems to get confused and hangs. No
error message, nothing - the code just gets stuck at:

StartDoc = ShellExecute(Scr_hDC, "Open", DocName,"", "C:\",
SW_SHOWNORMAL)

I have a vague inkling that this is something to do with Excel trying
to run itself while it is busy trying to run itself, but not being a
programmer, I couldn't articulate it any better than that. Does anyone
have any suggestions, short of having two separate bits of code for
Excel and non-Excel files?

Best regards,

Tristan Jakob-Hoff


'START QUOTE


Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As _
String, ByVal lpszFile As String, ByVal lpszParams As String, _
ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Const SW_SHOWNORMAL = 1

Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&

Function StartDoc(DocName As String) As Long
Dim Scr_hDC As Long
Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, "Open", DocName, _
"", "C:\", SW_SHOWNORMAL)
End Function

Public Sub OpenDoc2(Filename As String)
Dim r As Long, msg As String
r = StartDoc(Filename)
If r <= 32 Then
'There was an error
Select Case r
Case SE_ERR_FNF
msg = "File not found"
Case SE_ERR_PNF
msg = "Path not found"
Case SE_ERR_ACCESSDENIED
msg = "Access denied"
Case SE_ERR_OOM
msg = "Out of memory"
Case SE_ERR_DLLNOTFOUND
msg = "DLL not found"
Case SE_ERR_SHARE
msg = "A sharing violation occurred"
Case SE_ERR_ASSOCINCOMPLETE
msg = "Incomplete or invalid file association"
Case SE_ERR_DDETIMEOUT
msg = "DDE Time out"
Case SE_ERR_DDEFAIL
msg = "DDE transaction failed"
Case SE_ERR_DDEBUSY
msg = "DDE busy"
Case SE_ERR_NOASSOC
msg = "No association for file extension"
Case ERROR_BAD_FORMAT
msg = "Invalid EXE file or error in EXE image"
Case Else
msg = "Unknown error"
End Select
MsgBox msg
End If
End Sub


'END QUOTE