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.



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

[fumei]

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

[MOS MASTER]

Hi & Welcome to VBAX! :hi:

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:
Set cdlFileOpen = Nothing

Later..:whistle:

[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. :whistle:

[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, :yes

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. :whistle:

[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) :whistle:

[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 :bow: 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! :yes

[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 : pray2: The only problem I had was the IniFileName$ & WorkDirectoryName$ they were blank but the Word.Basic was also so I don't know :dunno

The following code goes into a module:

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



Usage:


Private Sub CommandButton1_Click()
If ReadTxtFile Then
MsgBox "Success"
End If
End Sub


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

[MOS MASTER]

MOS MASTER :hi: Thanks again :) I just wasn't happy with it but I wasn't sure if this would work in 97 :beerchug:
Hi Tommy this looks very nice!

Well done! :yes

But no this won't run in 97 have a minute and I'll set it up for 97 to complete it! :whistle:

[MOS MASTER]

Hi Tommy, :yes

Ok minor change in OpenSelectedFiles:
arrFls needs to be a variant the way it is used so change to:

Dim arrFls As Variant


Further more Split is not supported in 97 it's supported >2000.

So add a custom one like:
Public Function Split(Expression As String, Optional ByVal Delimiter As _
String = " ", Optional ByVal Count As Long = -1, _
Optional ByVal Compare As Integer = 0) As Variant

Dim lPos1 As Long
Dim lPos2 As Long
Dim lIdx As Long
Dim lCnt As Long
Dim arResult() As String

'Initialize the variables
lCnt = 0
lPos1 = 1
ReDim arResult(99)

'Search for the delimiter.
lPos2 = InStr(1, Expression, Delimiter, Compare)
Do While lPos2 > 0 And ((lCnt <= Count) Or (Count = -1))
'Delimiter found, extract the substring between the delimiters.
arResult(lCnt) = Mid$(Expression, lPos1, lPos2 - lPos1)
lCnt = lCnt + 1
If (lCnt Mod 100) = 0 Then
'Increase array size if needed.
ReDim Preserve arResult(UBound(arResult) + 100)
End If
'Move to end of last delimiter found.
lPos1 = lPos2 + Len(Delimiter)
'Search for the next delimiter.
lPos2 = InStr(lPos1, Expression, Delimiter, Compare)
Loop

If lPos1 < Len(Expression) Then
'Extract last substring.
arResult(lCnt) = Mid$(Expression, lPos1)
lCnt = lCnt + 1
End If

'Resize the array to correct size.
If lCnt > 0 Then
ReDim Preserve arResult(lCnt - 1)
Else
ReDim arResult(-1 To -1)
End If

'Return the array.
Split = arResult

End Function


I'll attach my testdoc voor dcrane to have a go with it.

Later buddy! :whistle:

[Tommy]

OK, good deal, I am very interested in the differences :yes be nice to add it to my "arsenal" :clap:

I saw the post as I typed this LOL the Split of course :doh: I would have never thought to check the length of the delimiter. You passed a variant instead of an array of strings, Sweet :) , that would have been a nail biter :rofl:

[MOS MASTER]

Hi Tommy, :yes

I found me another problem! :rofl:

The code as it stands works well on multiple selections but not on a single selection! (At least not over here) (The API returnes full path as 0 array when only one file is selected...)

I've made the folowing adjustment to cater for only one item in selectedfiles:
Public Function OpenSelectedFiles(iFileList As String)
Dim CPath As String 'path string
Dim mI As Long
Dim arrFls As Variant

'get directory with files
arrFls = Split(iFileList, Chr(0))
'get directory
CPath = arrFls(0)

If Dir(CPath) <> "" Then
Application.Documents.Open Trim$(arrFls(0))

ElseIf 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 If
End Function


Can you test if it works well for you as well? :whistle:

[Tommy]

Works in 2000 and 2003. Good catch :thumb . I thought I had that one taken care of.:mkay :rofl:

LOL I guess I need to post a warning message about Brain Cramps :devil:

[MOS MASTER]

Works in 2000 and 2003. Good catch :thumb . I thought I had that one taken care of.:mkay :rofl:

LOL I guess I need to post a warning message about Brain Cramps :devil:

Well then we've covered all versions of Word for this one! :yes
And for the Brain Cramps......I always forget a lot of stuff...o Well! :p

[dcrane37]

Tommy,


Thanks for the code. I cannot compile it with Word 97. I error out on the line
arrFls = Split(iFileList, Chr(0)) . It doesn't like Split. I commented out the line and it compiled OK but no Open box ever appears. I did a F1 help for Split and in Word97
and this is the example they give for the 97 Split Property.

This example splits the active window into two equal-sized window panes.
ActiveWindow.Split = True
If the Document1 window is split, this example closes the active pane.
If Windows("Document1").Split = True Then
Windows("Document1").ActivePane.Close
End If

Dave

[Tommy]

:hi: dcrane37 right click on the "Tommy 97 (2).zip" posted by MOS Master. That was fixed, along with another problem we found.

Let me know if you need more help.

[MOS MASTER]

This example splits the active window into two equal-sized window panes.
ActiveWindow.Split = True
If the Document1 window is split, this example closes the active pane.
If Windows("Document1").Split = True Then
Windows("Document1").ActivePane.Close
End If


Hi Dave, :yes

Like Tommy said the 97 problems should be fixed in that attachments code but I wonder what the quoted code has to do with your question?

That Split Method is something entirely different then the Split Function. :dunno