Your problem is related to the known issue 23922OSI8 - ProcessBook hangs when the "Save" button is selected on the Application.GetSaveAsFileName method. It was found on version 3.2.0 and it was not solved yet.
Nevertheless, there is a workaround as the article describes:
Upon conducting a series of tests I figured out that the first argument in the GetSaveAsFileName is causing the issue. If we had the filename without any extension for the InitialFileName argument then everything seem to work well. But, when we had the extension for the first argument then the ProcessBook hangs at the "Save As" dialog box doing nothing. The CPU usage spikes up to 100%. I had to forcibly kill the ProcessBook.
This code for me works fine:
Public Sub saveFilePath() Dim saveFilePath As Variant saveFilePath = Application.GetSaveAsFilename("SaveAsTest", "*.*") Debug.Print strFileName End Sub
The other options you have it to use another library or create your own window that the end-user should write the file name to be created in a folder.
Hope it helps!
Thank you Marcos, code works fine for me too
I was having the same problem as Shashank and tried using your workaround. Although it will work most of the times, if the user decides to replace an existing file by selecting it on the dialog box, the extension will automatically be added and processbook will get stuck.
So I've decided to create my own window.
Would you have any code for browsing and selectiong a folder (so user doesn't need to write the full path down)?
For Excel, there is
It doesn't seem to exist in PB thow...
I haven't found a way to show the file dialog out of the box (similar to Application.FileDialog for Excel) yet, so we might have to reinvent the wheel here. For your window form, is there any restrictions on where the user can select an existing file from (e.g. only current directory)? If so, we can possibility design some window forms to do the required operation.
An alternative, less elegant workaround would be to allow the user to pick between "SaveAs" or "Replace". While SaveAs will call Application.GetSaveAsFilename, "Replace" can call Application.GetOpenFilename(). GetOpenFilename is not affected by the known issue, so you can use the method as is. E.g.
saveFilePath = Application.GetOpenFilename(Title:="Replace")
Note that the button will still read "Open", even though the title has been changed to "Replace". This will be a quick workaround because you won't have to write your own window form.
There should be no restriction on where the user can save the files. That's why I was trying to open the dialogs, and let them browse anywhere.
The alternative solution you've provided doesn't avoid the user to "accidentally" replace a file using the "SaveAs" method. Since these applications are to be used on some supervisory machines, I don't want to leave this possibility open. If end user can make an application crash, one will! =)
Maybe I will have to create my own window and make the user write down the full path (I can use current path as initial value). This will demand a "little more effort" from them, but will guarantee no crashing!
This is true, the alternative solution has the possibility of making the application crash if not used properly. I put together a sample user form in ProcessBook VBA to illustrate the possibility to create our own folder explorer.
The code behind the Save As button on the main display window is as follow. When clicked, the "folder explorer" window (fmSaveAs) opens. The form will remain open until the user closes it by clicking the "Save As" button inside the form. Then, the save as file name will be saved to the main display window.
'Show the SaveAs Dialog box Private Sub btnSaveAs_Click() Dim fileName As String fmSaveAs.Show fileName = fmSaveAs.tbFilePath.Value Debug.Print fileName End Sub
The following is the code behind the user form:
'Name of the Directory Dim directory As String 'Go to previous directory (up one level) Private Sub btnPreviousDir_Click() If InStr(directory, "\") Then directory = Left(directory, InStrRev(directory, "\") - 1) LoadFiles End If End Sub 'Close the form when the "Save As" button is clicked Private Sub btnSaveAs_Click() If (tbFilePath.Value = "") Then MsgBox ("Please select or enter a file") Else fmSaveAs.Hide End If End Sub 'Clicking on any files populate the file path field Private Sub lbFiles_Click() tbFilePath.Text = directory & "\" & lbFiles.Value End Sub 'Double clicking the folder goes into that directory Private Sub lbFiles_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim tempDir As String tempDir = directory & "\" & lbFiles.Value If (FolderExists(tempDir)) Then directory = tempDir End If LoadFiles End Sub 'When form loads, set directory and load the file list Private Sub UserForm_Activate() directory = CurDir() LoadFiles End Sub 'Populate the list box with files from the selected directory Function LoadFiles() Dim file As String tbCurrentDirectory.Text = directory & "\" file = dir(directory & "\", vbDirectory) lbFiles.Clear While (file <> "") lbFiles.AddItem (file) file = dir Wend End Function 'Check if the selected directory is a folder 'Returns true if it's a folder, false otherwise. Public Function FolderExists(tempdirectory As String) As Boolean On Error GoTo ErrHandler If dir(tempdirectory & "\", vbDirectory) = vbNullString Then FolderExists = False Else FolderExists = True End If Exit Function ErrHandler: MsgBox ("This is not a folder.") FolderExists = False End Function
I hope this gives you a start in your development process. Please let me know if you have any questions or comments.
Thank you Daphne!
That's a lot more than a start!
The only change I've made to Daphne Ng's code was to filter PDI files and folders on my list.
'Populare the list with only folders and PDI files
Dim file As String
txt_Directory.Text = directory
txt_FilePath.Text = directory & "\"
file = Dir(directory & "\", vbDirectory)
While (file <> "")
If InStr(file, ".") = 0 Then 'It's a folder
ElseIf IsPDI(file) Then
file = Dir
'Check if file is a PDI
Public Function IsPDI(FilePath As String) As Boolean
On Error GoTo ErrHandler
If UCase(Right(FilePath, 4)) = ".PDI" Then