8 Replies Latest reply on Mar 17, 2015 7:59 PM by Guilherme Ferreira

    Save processbook contents in a text file

    ssinha39

       

       

      Hello,

       

      I am trying to save information from Processbook to text file. If I hard-code the location of the path to save the text file, VBA works fine but the requirement is to provide the location using 'Save as' dialog. To achieve this I tried "Application.GetSaveAsFilename()" which works well in Excel VBA but the same function doesn't work in Processbook, it opens the save as dialog box but clicking on save button doesn't do anything.

       

       .

       

      Could someone please suggest a way to achieve this/ Attached is the screenshot and code for your reference. Thank You

       
      Dim saveFilePath As Variant
      saveFilePath = Application.GetSaveAsFilename()
      If saveFilePath <> False Then
          Call MsgBox("Teste", vbInformation, "Save Path")
      End If
      

       

       

       

        • Re: Save processbook contents in a text file
          Marcos Vainer Loeff

          Hello Shashank,

           

          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!

            • Re: Save processbook contents in a text file
              ssinha39

              Thank you Marcos, code works fine for me too

              • Re: Save processbook contents in a text file
                Guilherme Ferreira

                Hi Marcos!

                 

                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

                Application.FileDialog(msoFileDialogFolderPicker).Show

                It doesn't seem to exist in PB thow...

                 

                Regards,

                  • Re: Save processbook contents in a text file
                    dng

                    Hi Guilherme,

                     

                    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.

                      • Re: Save processbook contents in a text file
                        Guilherme Ferreira

                        Hi Daphne!

                         

                        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!

                         

                        Regards,

                          • Re: Save processbook contents in a text file
                            dng

                            Hi Guilherme,


                            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.

                            2015-03-17 11_47_45-PI ProcessBook - [saveas.PDI_].png

                             

                            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.

                              • Re: Save processbook contents in a text file
                                Guilherme Ferreira

                                Thank you Daphne!

                                 

                                That's a lot more than a start!

                                 

                                Best regards,

                                  • Re: Save processbook contents in a text file
                                    Guilherme Ferreira

                                    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

                                    Function LoadFiles()

                                        Dim file As String

                                        txt_Directory.Text = directory

                                        txt_FilePath.Text = directory & "\"

                                        file = Dir(directory & "\", vbDirectory)

                                        lst_Files.Clear

                                        While (file <> "")

                                            If InStr(file, ".") = 0 Then 'It's a folder

                                                lst_Files.AddItem (file)

                                            ElseIf IsPDI(file) Then

                                                lst_Files.AddItem (file)

                                            End If

                                            file = Dir

                                        Wend

                                    End Function

                                     

                                    '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

                                            IsPDI= True

                                        Else

                                            IsPDI= False

                                        End If

                                        Exit Function

                                     

                                    ErrHandler:

                                        IsPDI= False

                                    End Function