Showing .pdf file in folder with my code

[Wicked_]

Hi.

I got this code (below), that users submitt some text and values, then it saves the file as a .pdf.
This works perfect, but, at the line ***foldername = .SelectedItems(1)*** it opens filebox and lets users select a folder to save the pdf file at, but is there a way to show the existing pdf's located in the folder the user selects?
Lets say i open folder Test, and in that folder, there is a 1.pdf and 2.pdf, when the user chooses the test folder, it will also show those files.

With my code, when they open the test folder, its blank, so if they are going to save the file incrementaly to the number, its hard to see what the last number.pdf was.

ActiveWorkbook.ActiveSheet.Range("AN1").Value = (ComboBox1.Value)

If ComboBox1.Text = "" Then
        MsgBox "Please choose a profile name!"
        Exit Sub
   Else
'      Code if not empty
   End If








    
    With flder
        .Title = "Select the folder containing data"
        .AllowMultiSelect = True
        If .Show = 0 Then GoTo NextCode
        foldername = .SelectedItems(1)
        
        Path = foldername & "\"
                
                If CheckBox2.Value = True And CheckBox1.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Dummy - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                
                If CheckBox2.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Dummy - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                If CheckBox1.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                    FileName1 = TextBox1.Text & " - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                End If
                End If
                End If
        
    End With


Set flder = Nothing
MsgBox "File Saved to " & FileName1, vbInformation, "Saved File"
Unload Me
NextCode:
Exit Sub

Thanks in advance

 

[spencer_time]

I am still learning myself, but I believe you could use the following:

Application.GetSaveAsFileName(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

Maybe something like:

Application.GetSaveAsFileName( , *.pdf)

EDIT 10:48: Disregard my suggestion, I tried it and it doesn't appear to be able to export a .pdf. The following is some code I have working to IMPORT a specialized type of file and is what I tried to modify to get a solution for you, perhaps you can make something from it:

Application.GetOpenFilename("Text Files (*.s2p), *.s2p", , "Import *.s2p files", , True)

 

[Wicked_]

Hi and thanks for the answer. I'll try if i can "salvage" some of that code

Somehow i managed to not include the top of the code. Posting entire code here, if it makes it easier hehe.

Sub SavePDF5()Dim Path, FileName1 As String


Dim flder As FileDialog
Dim foldername As String
Dim GetFolder As String


Set flder = Application.FileDialog(msoFileDialogFolderPicker)






'Profile
ActiveWorkbook.ActiveSheet.Unprotect ("")


ActiveWorkbook.ActiveSheet.Range("AN1").Value = (ComboBox1.Value)


If ComboBox1.Text = "" Then
        MsgBox "Please choose a profile name!"
        Exit Sub
   Else
'      Code if not empty
   End If








    
    With flder
        .Title = "Select the folder containing data"
        .AllowMultiSelect = True
        If .Show = 0 Then GoTo NextCode
        foldername = .SelectedItems(1)
        
        Path = foldername & "\"
                
                If CheckBox2.Value = True And CheckBox1.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Dummy - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                
                If CheckBox2.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Dummy - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                If CheckBox1.Value = True Then
                    FileName1 = TextBox1.Text & " - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                Else
                    FileName1 = TextBox1.Text & " - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
                End If
                End If
                End If
        
    End With


Set flder = Nothing
MsgBox "File Saved to " & FileName1, vbInformation, "Saved File"
Unload Me
NextCode:
Exit Sub

 

[John_w]

This works perfect, but, at the line ***foldername = .SelectedItems(1)*** it opens filebox and lets users select a folder to save the pdf file at, but is there a way to show the existing pdf's located in the folder the user selects?
Lets say i open folder Test, and in that folder, there is a 1.pdf and 2.pdf, when the user chooses the test folder, it will also show those files.

This can be done with the Shell BrowseForFolder method and specifying the BIF_BROWSEINCLUDEFILES flag so that the browser displays files and folders.

Add this code to a new module:

Option Explicit

'https://docs.microsoft.com/en-us/windows/win32/api/shlobj_core/ns-shlobj_core-browseinfoa

Private Const BIF_EDITBOX As Long = &H10
Private Const BIF_NEWDIALOGSTYLE As Long = &H40
Private Const BIF_NONEWFOLDERBUTTON As Long = &H200
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const BIF_USENEWUI As Long = BIF_EDITBOX Or BIF_NEWDIALOGSTYLE


Public Function Shell_Browse_Folder(startFolder As Variant) As Variant

    Dim WShell As Object
    Dim WShellFolder As Object
    Dim WShellFolderItem As Object
    Dim flags As Long
    
    Set WShell = CreateObject("Shell.Application")
    
    flags = BIF_BROWSEINCLUDEFILES Or BIF_NONEWFOLDERBUTTON 'Or BIF_USENEWUI
    
    Shell_Browse_Folder = True
    Do
        Set WShellFolder = WShell.BrowseForFolder(0, "Select a folder", flags, startFolder)
        If WShellFolder Is Nothing Then
            Shell_Browse_Folder = False
        Else
            Debug.Print WShellFolder.Self.Path
            Set WShellFolderItem = WShellFolder.Self
            If Not WShellFolderItem Is Nothing Then
                If WShellFolderItem.IsFolder Then
                    Shell_Browse_Folder = WShellFolderItem.Path
                Else
                    If MsgBox("You must select a folder, not a file. Browse again?", vbYesNo + vbExclamation, "Select a folder") = vbNo Then
                        Shell_Browse_Folder = False
                    End If
                End If
            End If
        End If
    Loop Until Shell_Browse_Folder = False Or TypeName(Shell_Browse_Folder) = "String"
    
End Function

The Shell_Browse_Folder function above opens a browser which shows files and folders, starting at the specified startFolder. The user is only allowed to select a folder and if they select a file a warning is displayed and they can browse again.

Call the Shell_Browse_Folder function instead of your msoFileDialogFolderPicker code, like this:

Sub Test()

    Dim startFolder As Variant
    Dim selectedFolder As Variant
    
    startFolder = "C:\Temp"
    selectedFolder = Shell_Browse_Folder(startFolder)
    
    If TypeName(selectedFolder) = "String" Then
        Debug.Print "Selected folder: " & selectedFolder
    Else
        Debug.Print "No folder selected"
    End If

End Sub