Run Time Error 13 when User Cancels

[dcrane37]

I'm a newbie in this forum and a newbie with VBA. I need some help and I don't know where else to turn. I inherited this piece of code from Word97.
The code for the most part functions, but there's an interesting quirk I can't figure out. If the user runs this macro and clicks on a file to open, the code executes perfectly. If the user presses the cancel button, then runs the macro again they receive the Run time Error 13 Data type mismatch error. I can't figure this out. Any ideas?

Thanks.



[VBA] Sub OpenFiles()
Dim cdlFileOpen As CommonDialog
Dim iPos As Integer
Dim iLastPos As Integer
Dim strFileName As String
Dim strPath As String
Dim WorkDirectoryName$
Dim IniFileName$
Dim CurDir As String


'Create common dialog box object
Set cdlFileOpen = FileOpen.CommonDialog1

cdlFileOpen.DialogTitle = "Flash Word 97: Open Work Files"

' Set CancelError is True
cdlFileOpen.CancelError = True
On Error GoTo ErrHandler

' Set flags
cdlFileOpen.flags = cdlOFNAllowMultiselect ' & cdlOFNExplorer ' & cdlOFNLongNames

' Set filters
cdlFileOpen.Filter = "All Files (*.*)|*.*|Work files (*.wrk)|*.wrk|Advance Files (*.adv)|*.adv|Rejected Files (*.rjt)|*.rjt|Done Files (*.don)|*.don|Trash Files (*.tsh)|*.tsh"

' Specify default filter (*.wrk from above list)
cdlFileOpen.FilterIndex = 2

'Make sure the file name is blank
cdlFileOpen.FileName = ""

'Set the open directory
IniFileName$ = WordBasic.[GetPrivateProfileString$]("DLS", "Shared_INI", "WIN.INI")
WorkDirectoryName$ = WordBasic.[GetPrivateProfileString$]("Global Settings", "WorkDirectory", IniFileName$)

CurDir = Options.DefaultFilePath(wdCurrentFolderPath)

If CurDir = "c:\windows\desktop" Then
cdlFileOpen.InitDir = WorkDirectoryName$
Else
cdlFileOpen.InitDir = CurDir
End If

' Display the Open dialog box
cdlFileOpen.ShowOpen

'Save path info
iLastPos = InStr(1, cdlFileOpen.FileName, " ")
If iLastPos = 0 Then
'Only one file was selected, look for slash as path terminator
Do
iLastPos = iPos
iPos = InStr(iLastPos + 1, cdlFileOpen.FileName, "\")
Loop Until iPos = 0
End If

strPath = Left(cdlFileOpen.FileName, iLastPos - 1)

'Check path for trailing slash. Results from dialog vary for some OSs.
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
Debug.Print "Path=" & strPath & "|"

iPos = InStr(iLastPos + 1, cdlFileOpen.FileName, " ")
While iPos <> 0
'Parse the name of the next file to open
strFileName = LTrim(Mid(cdlFileOpen.FileName, iLastPos + 1, iPos - iLastPos - 1))
Debug.Print " File='" & strFileName & "'"

'Open the file
Documents.Open FileName:=strPath & strFileName, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=True, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

'Set current position in name string and look for next file
iLastPos = iPos
iPos = InStr(iLastPos + 1, cdlFileOpen.FileName, " ")
Wend

'Process last file
strFileName = Mid(cdlFileOpen.FileName, iLastPos + 1)
Debug.Print " File='" & strFileName & "'"
Documents.Open FileName:=strPath & strFileName, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=True, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
Exit Sub

ErrHandler:
Select Case Err.Number
Case 32755
'User pressed the Cancel button, do nothing and just exit
Case Else
'Display error message
MsgBox Err.Number & ":" & Err.Description
End Select
Exit Sub
End Sub
[/VBA]

[fumei]

Hmmm, try commenting out the CancelError = True, and see what happens.

[MOS MASTER]

Hi & Welcome to VBAX!

A hmm from me as well!

Is this part of a VB project? (Because you're using the common dialog and some new file extensions I don't know off)

Try to release the object reference on the end of execution. A known error causer..

So before the Exit sub (twice the way this code is written) put:[VBA]
Set cdlFileOpen = Nothing[/VBA]

Later..

[dcrane37]

Joost,

Thanks for responding. Adding those lines did not work.

Dave

[MOS MASTER]

Joost,

Thanks for responding. Adding those lines did not work.

Dave

Gerry Added a comment as well please try his to.

The problem is your code is not easily rebuild. Could you please tell us what you think it should do perhaps I could recode it to a more Word like program code.

[dcrane37]

Gerry,

When I commented that line out. It bypassed the cancel part of the error handler.
I received a Word error 5: Invalid Procedure call or Arguement error. However,
subsequent runs of ther macro were fine, except for that error. So, there's some goofy about how the user clicking on Cancel is being trapped. It still seems like some setting is "dirty" or not beng cleared despite the CdlFileOpen = Nothing lines that I added.
IT dies on Set cdlFileOpen = FileOpen.CommonDialog1 this line when the user clicks Cancel and I trap for that.

Dave

[fumei]

Actually, it would be better to follow Joost's suggestion. Write step-by-step what you want this to do. It needs to be rebuilt in, as Joost put it, more Word like program code.

For example, there is a PathSeparator object that you can use - it essentially a slash. You can also use the Dir function. You can use .Path for file paths. You can use the FileSystemObject to do folder and file operations.

What, EXACTLY, are you trying to do? You seem to be opening files...but I can't see what you are doing with it. then you open and "process" the next file. What are you processing???

If we knew exactly what is supposed to happen we could also get rid of extraneous items. For example, I am pretty sure some of the items in the document opening you can remove.

ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=True, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

Do you need all this stuff? If not, don't have it in.

What are you trying to do?

[dcrane37]

You asked for it...Here's what I want it to do.
The user clicks on a a button that runs this macro. The first time the macro gets run it checks a .ini file for the initial fileopen directory path(Workdirectoryame$). It then builds a fileOpen dialog that allows the user to open multiple files. The FileOpen setup (filter list , etc.) are fine within the existing macro. The files selected are added to the MRU list and the path that the user changes to gets stored. So, when these files are closed or saved and the macro is run again the last used directory path is now the default FileOpen path. Trap the Cancel error number if the user clicks on cancel
when the FileOpen box is up.
I think that's about it.
Thanks.
Dave

[MOS MASTER]

Hi Dave,

Want you want is doable but the options depend heavily on the target app that requires this code!

You said the code was inherited from Word '97 (BTW a lot of the code is in Wordbasic)

For me the most important thing to know is in What version of Word you will be using this code. If it is XP or 2003 the options are much greater then if it has to work in all versions of Word.

If I have this lost bit of information I'll cook something up for you soon.

[dcrane37]

Joost,


The macro was transferred(converted) from Word 6 to Word 97. Which is why there's still some WordBasic code in there. It has to work with Word 97.

Dave

[MOS MASTER]

Ah ok..I understand.

Well we just have to do it the hard way then. I'll report back soon. (Have to finish something else first)

[dcrane37]

Joost,

Thanks for your help BTW. Any idea on why the CdlFileOpen object is not functioning properly. Is this a bug with Word97 VBA or with the comdlg.ocx?

I've had three VB guys look at it in house and they are stumped also.

Dave

[MOS MASTER]

Dave there is a known bug with the comdlg.ocx and a fix as I recall I tried searching for that Yesterday but I can't find it. Will look again later..

[dcrane37]

Joost,


I tried serarching for the error im comdlg32.ocx, but could not find the exact issue.
I'll keep looking.

Dave

[Tommy]

Hi dcrane37,

Even though I think you have some of the best help already What OS are you using? I ask bcause of "C:\windows\desktop" the desktop locations change from OS to OS. On win xp it is "C:\Documents and Settings\" & user & "\Desktop"

[dcrane37]

Tommy,


It will be running on Windows 98SE for the time being.

Dave

[Tommy]

Just to get it to work change

Dim cdlFileOpen As CommonDialog
to
Dim cdlFileOpen As Object

If you are going to use other OS's I can post the code to get the desktop for each one if required

[dcrane37]

Tommy,


That fixed the Cancel issue. Thank you. Is there a beter method for checking the current
path than what is in the current code?

Dave

[MOS MASTER]

Just to get it to work change

Dim cdlFileOpen As CommonDialog
to
Dim cdlFileOpen As Object

If you are going to use other OS's I can post the code to get the desktop for each one if required

Yes Tommy...Late binding that fixes the problem with different versions of the Ocx!!

Nice lateral thinking!

[Tommy]

dcrane37 I rewrote your code, I found some issues that bother me.(Dim CurDir As String - this is a reserved word it gets the current directory) I also changed WordBasic.[GetPrivateProfileString$] to System.PrivateProfileString it's VBA equal. So for what it is worth because I do not know if it works with Word 97.

I got the "GetOpenFileName" from Killian, the documentation is excellent.

I tested as best as I could in Word 2000, I did not have any of the file extensions you provided so I opend several word docs so at this point in time I think it works The only problem I had was the IniFileName$ & WorkDirectoryName$ they were blank but the Word.Basic was also so I don't know

The following code goes into a module:
[VBA]
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
'### define type to pass to GetOpenFileName function
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Public Function ReadTxtFile() As Boolean

Dim OFName As OPENFILENAME
Dim WorkDirectoryName$
Dim IniFileName$
Dim CDir As String
OFName.lStructSize = Len(OFName)
OFName.lpstrFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & "Work files (*.wrk)" & Chr(0) & _
"*.wrk" & Chr(0) & "Advance Files (*.adv)" & Chr(0) & "*.adv" & Chr(0) & "Rejected Files (*.rjt)" & Chr(0) _
& "*.rjt" & Chr(0) & "Done Files (*.don)" & Chr(0) & "*.don" & Chr(0) & "Trash Files (*.tsh)" & Chr(0) & "*.tsh" & Chr(0)
'Specify default filter (*.wrk from above list)
OFName.nFilterIndex = 2
'create a buffer for the file
OFName.lpstrFile = Space(6000)
'set the maximum length of a returned file
OFName.nMaxFile = 6001
'Create a buffer for the file title
OFName.lpstrFileTitle = Space(6000)
'Set the maximum length of a returned file title
OFName.nMaxFileTitle = 6001
'Set the title
OFName.lpstrTitle = "Flash Word 97: Open Work Files"
'allow multiple file selection and show "new" explorer
OFName.flags = "&H80200"
'Set the open directory
IniFileName$ = System.PrivateProfileString("DLS", "Shared_INI", "WIN.INI")
WorkDirectoryName$ = System.PrivateProfileString("Global Settings", "WorkDirectory", IniFileName$)
' IniFileName$ = WordBasic.[GetPrivateProfileString$]("DLS", "Shared_INI", "WIN.INI")
' WorkDirectoryName$ = WordBasic.[GetPrivateProfileString$]("Global Settings", "WorkDirectory", IniFileName$)
CDir = Options.DefaultFilePath(wdCurrentFolderPath)
If InStr(1, CDir, "Desktop") > 0 Then
'Set the initial directory
OFName.lpstrInitialDir = WorkDirectoryName$
Else
'Set the initial directory
OFName.lpstrInitialDir = CDir
End If
'Show the 'Open File'-dialog
If GetOpenFileName(OFName) Then
OpenSelectedFiles Trim$(OFName.lpstrFile)
'return true if successful
ReadTxtFile = True
Else
'Cancel was picked
ReadTxtFile = False
End If

End Function
Public Function OpenSelectedFiles(iFileList As String)
Dim CPath As String 'path string
Dim mI As Long
Dim arrFls() As String
'get directory with files
arrFls = Split(iFileList, Chr(0))
'get directory
CPath = arrFls(0)
If Right$(CPath, 1) <> "\" Then CPath = CPath & "\"
'for each file show a message box
For mI = 1 To UBound(arrFls)
If arrFls(mI) <> vbNullString Then Application.Documents.Open CPath & "\" & Trim$(arrFls(mI))
Next
End Function

[/VBA]

Usage:

[VBA]
Private Sub CommandButton1_Click()
If ReadTxtFile Then
MsgBox "Success"
End If
End Sub
[/VBA]

MOS MASTER Thanks again I just wasn't happy with it but I wasn't sure if this would work in 97