Saving pdf with a few textboxes and folders.

[Wicked_]

Hi there.

I currently got a code, for saving pdf files, that works good, but i want to improve it a bit, but im struggling real hard.

Currently, i got 1 textbox, 1 combobox and 2 checkboxes, which i fill in, then press the button, and it opens a dialog, then i find the folder i want, and it saves the pdf to that folder.
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 the operator 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
ActiveWorkbook.ActiveSheet.Protect ("")
End Sub

But i am trying to improve it.
Now i got 2 textboxes and 2 comboboxes (Textbox 1 and combobox1 does the same thing as in the old code)

What i want now is this:

I select the data from combobox 1 (same as before in the old code),
Then i type the data needed in textbox1 (same as before in the old code)
In combobox2 i choose some data that i have added (this one is important)
In Textbox2 i type some required numbers (this one is important)

So, the data from combobox2, is going to be linked to spesific folders on the computer.
Textbox2 is going to make a subfolder in combobox2 folder, or if it allready exsists, just create the pdf file within it.

So the dilemma here, is making the textbox 2 either creating a subfolder, then save the pdf in it, or check if it exists, and if so, open it and save the pdf in it.

I have tried mixing those codes, since combobox1 and textbox1 works good, but i am stuck, and it feels like the new code is a mess.

Code:

Sub PDFSMAIN()

    Dim sPath As String 'path of folder for the text file to be held
    Dim sPath2 As String 'path of folder for the text file to be held
    Dim sPath3 As String 'path of folder for the text file to be held
    Dim sPath4 As String 'path of folder for the text file to be held
    Dim sPath5 As String 'path of folder for the text file to be held
    Dim sPath6 As String 'path of folder for the text file to be held
    Dim sPath7 As String 'path of folder for the text file to be held
    Dim sPath8 As String 'path of folder for the text file to be held
    Dim sPath9 As String 'path of folder for the text file to be held
    Dim sPath10 As String 'path of folder for the text file to be held
    Dim sPath11 As String 'path of folder for the text file to be held
    Dim sPath12 As String 'path of folder for the text file to be held
    Dim sPath13 As String 'path of folder for the text file to be held
    Dim sPath14 As String 'path of folder for the text file to be held
    Dim sName As String 'name of file
    Dim sName2 As String 'name of file
    Dim sName3 As String 'name of file
    Dim sName4 As String 'name of file
    Dim sName5 As String 'name of file
    Dim sName6 As String 'name of file
    Dim sName7 As String 'name of file
    Dim sName8 As String 'name of file
    Dim sName9 As String 'name of file
    Dim sName10 As String 'name of file
    Dim sName11 As String 'name of file
    Dim sName12 As String 'name of file
    Dim sName13 As String 'name of file
    Dim sName14 As String 'name of file
   
    On Error GoTo errHandle
   
    'set the path
    sPath =  "O:\1\"
    sPath2 = "O:\2\"
    sPath3 = "O:\3\"
    sPath4 = "O:\4\"
    sPath5 = "O:\5\"
    sPath6 = "O:\6\"
    sPath7 = "O:\7\"
    sPath8 = "O:\8\"
    sPath9 = "O:\9\"
    sPath10 = "O:\10\"
    sPath11 = "O:\11\"
    sPath12 = "O:\12\"
    sPath13 = "O:\13\"
    sPath14 = "O:\14\"
       

   
 
   
   
    If UserForm6.CheckBox2.Value = True And UserForm6.CheckBox1.Value = True Then
                    sName = UserForm6.TextBox1.Text & " - " & "Dummy - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                Else
                If UserForm6.CheckBox2.Value = True Then
                    sName = UserForm6.TextBox1.Text & " - " & "Dummy - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                Else
                If UserForm6.CheckBox1.Value = True Then
                    sName = UserForm6.TextBox1.Text & " - " & "Bromm - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                Else
                    sName = UserForm6.TextBox1.Text & " - " & ActiveWorkbook.ActiveSheet.Range("AN1").Value
                    ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & sName & ".pdf", OpenAfterPublish:=False
                End If
                End If
                End If
   
   
 
   
    Open sPath & sName For Output As #1
   
     
       
    Close #1
   
    MsgBox "Success!", vbInformation, "Textbox Text Saved"
    'Unload Me

Exit Sub

errHandle:
    MsgBox Err.Description, vbCritical, "Failed"
 
End Sub

Can anyone help me with this? Please just ask if i am beeing unclear on something

Thanks in advance.

EDIT: The new code is imcomplete, just tried to see if it worked before i did more code on it, just if you wondered

 

[sijpie]

I can see if i can make sense of it, either tomorrow or else next week

 

[Wicked_]

Sweet, thank you

 

[sijpie]

Your userform holding the text & comboboxes, is that on a sheet, or a pop-up userform?

If i rephrase what you are trying to get done:

1. the user selects an operator from Combobox1 (I can not see where this value is used. So why does this need to be done?)
2. the user types into Textbox1 an identifier to be added as prefix to the pdf name
3. the user has a choice to tick two tickboxes which add some additional text to the file name
4. the user selects a folder from combobox2 (How are you filling this combobox? Why not use folder picker, or is this a very selective choice?)
5. the user types in a number into textbox2. This number relates to a subfolder (existing or to be made) of the folder selected in step4
6. the user runs the macro
7. The macro checks if all information required is supplied
8. The macro checks for the existence of the subfolder and creates if necessary
9. The macro creates the pdf with the name as supplied in Cell AN1 with all the prefixes required

If you are using a sheet userform at the moment, then I suggest you start using a pop-up userform. Slightly more work to set up, but far more control.

• Let me know if the flow (1-9) above is correct and answer the questions therein
• Let me know about the userform

 

[Wicked_]

Hi again.
The old way which i has (the old code), makes the user to select folder, but since we got multiple peoples using this, and with different computer skills, some tend to put the information in wrong folders, so i'm trying to simplify the function, to make it "dummy proof"

I added 3 pictures for you to see how i got it, and yes, its a userform, sorry for forgetting to mention that .

As you see on the pictures below, (i sensured the names and program, since its colleeges names and programs), we choose the operator name (ComboBox1), then type the piece number (TextBox1), (this is the same as in the old code, that works), then we choose the program (ComboBox2), and Order number (TextBox2).

Each Product in Combobox2 should have its own path to a root folder (different to each product( Example: Product 1 = O:/Folder1 Product 2 = O:/Folder2 etc etc)
Then in the Order number textbox, will be the sub folder for the Product path, so lets say i type "111111" in order number, and choose "Product1" it shall create a subfolder "O:/Folder1/111111/", but if the folder "111111" exists, it will just save the pdf within the "111111" folder without creating it.

The Persons and product is made within UserForm_Initialize.

Private Sub UserForm_Initialize()
With ComboBox1
         .AddItem "Person 1"
         .AddItem "Person 2"
         .AddItem "Person 3"
         .AddItem "Person 4"
         .AddItem "Person 5"
         .AddItem "Person 6"
         .AddItem "Person 7"
         .AddItem "Person 8"
         .AddItem "Person 9"
         .AddItem "Person 10"
        
        
    End With
    
    With ComboBox2
         .AddItem "Product 1"
         .AddItem "Product 2"
         .AddItem "Product 3"
         .AddItem "Product 4"
         .AddItem "Product 5"
         .AddItem "Product 6"
         .AddItem "Product 7"
         .AddItem "Product 8"
         .AddItem "Product 9"
         .AddItem "Product 10"
         .AddItem "Product 11"
         .AddItem "Product 12"
         .AddItem "Product 13"
        
        
    End With
End Sub

The Cell AN1 is what i thought was the simplest way to "print" the selected name, and used the cell to output the name to the pdf (as seen in the old code)


Hope that answered your questions Just ask you you need more info.

 

[sijpie]

OK, i think that is clear. I'll probably bump into other issues as I work on it.

Your programming is often very inefficient. By hardcoding usernames in the VBA you make it very inflexible. Personally I would set up a sheet with a vertical list of usernames, and another list of productnames. Give the header of each list a range name. Now you can add or remove names, products, etc. Do a sort to get them alphabetically. Then hide the sheet.

Then in your Userform_Initiate or Userform_Activate have the code to upload the items to the comboxes:

Private Sub UserForm_Activate()
    Dim vList As Variant
    Dim i As Integer
    
    'read the first list into array (for fast processing)
    vList = Range("Usernames").CurrentRegion
    'fill  combobox
    With ComboBox1
        For i = 2 To UBound(vList, 1) 'skip header
            .AddItem vList(i, 1)
        Next i
    End With
    
    'read the second list into array (for fast processing)
    vList = Range("Products").CurrentRegion
    'fill  combobox
    With ComboBox2
        For i = 2 To UBound(vList, 1) 'skip header
            .AddItem vList(i, 1)
        Next i
    End With
End Sub

This is far easier to maintain, also for someone in the future. No need to understand VBA, just unhide the sheet modify the list and hide sheet again. (I always have a few lines of code in the workbook_open sub to hide any administrative sheets, in case somebody didn't do that.)

 

[sijpie]

Another question:

you mention that you use cell AN1 as a storage for the file name. But what is the default name?
Assuming the user selected user: Andrew, Piece: 2, Product: product4, Order: 1122
no checkboxes selected.
Then the path will be O:\Folder4\1122\
and the filename would be: ???

 

[Wicked_]

Hi again.

Before, i played with typing the Person 1's name on the Value box of the combobox, to have a default name, but decided not to have it, and implemented the "error" to choose a operators name if the user dident select a name.

The filename is dependent if the boxes is checked or not, on my current code, if i just select Person 1 and type Piece 2 it saves to 2 - Person 1.pdf

the full pdf saving criteria is from my "old" code on my first box.

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

those are the name saving criteria i got, if you select any boxes or not.

Hope that answered your questions Dont hesitate asking more if needed

 

[sijpie]

The following should work. Follow the instructions carefully.

1. First create a sheet where you store the Usernames and the Products.
2. Next to the product column add the folder names for each product.
3. Give the header for the usernames the rangename UserNames
4. Give the header for the products the rangename Products

See the image below

5. You can hide the sheet now or later.
6. I have assumed (from your code snippets) that you have not renamed any of the textboxes or buttons in your userform, else you will need to modify that in the code below.
7. Add a Textbox3 to your userform, a fairly long one to hold the path and filename (see my userform, although I just made a quick one so ignore my layout)

8. In your userform code, comment everything out, or cut/paste to an empty module. This is because you may have other functionality in your userform that I don't know about
9. Add the following code to the userform

Option Explicit





Private Sub CommandButton1_Click()
    CheckPath
    ThisWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                                Filename:=TextBox3, _
                                                OpenAfterPublish:=False
    
    ' message box no longer required because the path was already viisble to the user
    ' MsgBox prompt:="File Saved to " & textbox3, Buttons"=vbInformation + vbOK, Title:="Saved File"
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub CheckBox1_Click()
    ShowPath
End Sub

Private Sub CheckBox2_Click()
    ShowPath
End Sub

Private Sub ComboBox1_Change()
    ShowPath
    CheckEnabled
End Sub

Private Sub ComboBox2_Change()
    ShowPath
    CheckEnabled
End Sub


Private Sub TextBox1_Change()
    Dim ch As String, i As Integer
    
    If Not IsNumeric(TextBox1.Value) Or InStr(TextBox1, ".") Then
         For i = 1 To Len(TextBox1)
            If Not IsNumeric(Mid(TextBox1.Value, i, 1)) Then
                TextBox1 = Replace(TextBox1, Mid(TextBox1.Value, i, 1), "")
                Exit For
            End If
         Next i
    End If
    ShowPath
    CheckEnabled
End Sub


Private Sub TextBox2_Change()
    ShowPath
    CheckEnabled
End Sub


Private Sub UserForm_Activate()
    Dim vList As Variant
    Dim i As Integer
    
    'read the first list into array (for fast processing)
    vList = Range("Usernames").CurrentRegion
    'fill  combobox
    With ComboBox1
        For i = 2 To UBound(vList, 1) 'skip header
            .AddItem vList(i, 1)
        Next i
    End With
    
    'read the second list into array (for fast processing)
    'The data read into the array will be Products in the _
     first column and Folders in the second column
    vList = Range("Products").CurrentRegion
    'fill  combobox
    With ComboBox2
        For i = 2 To UBound(vList, 1) 'skip header
            .AddItem vList(i, 1)
            .List(.ListCount - 1, 1) = vList(i, 2)
        Next i
    End With
    
    'set up the form
    CommandButton1.Enabled = False
    With TextBox3
        .Locked = True
        .BackColor = vbButtonFace
    End With
    ComboBox1.MatchRequired = True
    ComboBox2.MatchRequired = True
End Sub


Private Sub ShowPath()
    Dim sPath As String, sFName As String
    
    ' below ComboBox2.List(ComboBox2.ListIndex, 1) is being used: The folder name _
      has been loaded together with the product name in a hidden column in the combobox. _
      So by checking which product has been selected, you can find the associated folder name
    sPath = "O:\"
    If Len(ComboBox2) Then sPath = sPath & ComboBox2.List(ComboBox2.ListIndex, 1) & "\"
    sPath = sPath & TextBox2 & "\"
    sFName = TextBox1 & IIf(CheckBox1, " - Bromm", "") & IIf(CheckBox2, " - Dummy", "") & " - " & ComboBox1
    TextBox3 = sPath & sFName & ".pdf"
    
End Sub

Private Sub CheckEnabled()

    CommandButton1.Enabled = Len(ComboBox1) * Len(ComboBox2) * Len(TextBox1) * Len(TextBox2)

End Sub


Private Sub CheckPath()
    Dim sPath As String

    sPath = "O:\"
    sPath = sPath & ComboBox2.List(ComboBox2.ListIndex, 1) & "\"
    sPath = sPath & TextBox2 & "\"
    
    'Check if the folder exists, and if it doesn't, create folder with VBA MkDir
    If Len(Dir(sPath, vbDirectory)) = 0 Then
        MkDir sPath
    End If
End Sub

10. Now run the code.

11. You will see that as you fill out the boxes, the path and filename gets built. Also you will see that the Save button is only enabled once all four boxes have been filled. I was assuming that the Pieces textbox can only take numeric input, so I coded the Textbox1_Change to ignore any non numerical characters.

 

[Wicked_]

Hi.

Thanks alot! I will try it within a few hours, i'll report back when i have tried it.