Showing .pdf file in folder with my code

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
81
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.


Code:
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 :)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
I am still learning myself, but I believe you could use the following:
Code:
Application.GetSaveAsFileName(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

Maybe something like:
Code:
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:
Code:
Application.GetOpenFilename("Text Files (*.s2p), *.s2p", , "Import *.s2p files", , True)
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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:
Code:
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:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top