If customers file exists then add 2 etc after new file name

[ipbr21054]

Hi,,

I have a userform with TextBox1 & TextBox 2 & CommandButton1
The code in use is shown below.

I open the userform & TextBox 2 is populated from another worksheet.
The value entered is always 6 characters & will never be the same ,example 1A2B3C etc etc

I type the customers name in TextBox1,example JAMES BOND
I then run the CommandButton1 code.

If i go the the folder where the pdf is saved i see the following file for that customer.
JAMES BOND 1A2B3C.pdf

The above is fine & works well.

This is where i need some additional code.
A previous Customer makes another purchase,example JAMES BOND

The value this time is 8A6S22
The saved file this time is as follows.
JAMES BOND 8A6522.pdf

Im thinking that the additional code should look for the customers name only & if present add 2 after his name, if two files for the same customer is present then add 3 etc etc

So example if 1 instance is there.
JAMES BOND 2 "THEN THE CODE"

If 2 files are there then.
JAMES BOND 3 "THEN THE CODE"

If the code to be used struggles to look at customers name only & not the code maybe use something like this JAMES BOND 2 *1A2B3C*
The code then only looks at the characters BEFORE the * "Make Sense"

Many Thanks

Private Sub CommandButton1_Click()
  Dim sPath As String
  Dim strFileName As String
  
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
    .Range("A3") = Me.TextBox2.Text ' ENTERS PCB NUMBER TO WORKSHEET
  End With
  Unload PrinterForm
                                                                             
  With ActiveSheet
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & .Range("A3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    
  End With
  
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
  strFileName = sPath & Range("B3").Value & " " & Range("A3").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
    
 
End Sub

 

[Skyybot]

Would it not be easier to, when creating a new customer, start a folder with that customers' name and save each pdf for that customer in that folder?

 

[ipbr21054]

Thats a possibilty yes.
What do you advise for the code then please

 

[Skyybot]

Thats a possibilty yes.
What do you advise for the code then please

Here is some code that you can test to see where/how you want to enter it into your code. You will have to create a folder in your C:\ drive called Customers for this code to work but once you see how it works you can change the path to anything you like and change the code accordingly.

Sub test()
Dim sPath, customer As String
customer = InputBox("Enter customer name.", "Customer")
sPath = savePath(customer)
MsgBox "You are currently saving this to: " & sPath
End Sub
Function savePath(custName As String)

Dim fs, f, s

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\Customers")
Set s = f.SubFolders

For Each folder In s
    If folder.Name = custName Then
        sPath = "C:\Customers\" & custName
        savePath = sPath
        Exit Function
    End If
Next folder

s.Add custName
sPath = "C:\Customers\" & custName
savePath = sPath

End Function

 

[ipbr21054]

Thanks for that but i would rather not use tthe input field box & wish to continue with my existing form

 

[Skyybot]

Thanks for that but i would rather not use tthe input field box & wish to continue with my existing form

You don't have to use the InputBox. You can just assign a value to customer. I just put that in there to get a customer name. You can assign anything to that variable (cell value, for example)

 

[Skyybot]

You don't have to use the InputBox. You can just assign a value to customer. I just put that in there to get a customer name. You can assign anything to that variable (cell value, for example)

example: if customer name is in Sheet1 cell A1

cusomer = Worksheets(1).Cells("A1")

 

[ipbr21054]

Hi,
No names are stored on the sheets.
The only time a name is stored is when saving a pdf.
I think I’m going to continue with what I have but think about how the file is saved with my current set up.

 

[Skyybot]

Hi,
No names are stored on the sheets.
The only time a name is stored is when saving a pdf.
I think I’m going to continue with what I have but think about how the file is saved with my current set up.

No problem. But according to your original code your customer name is here:

.Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET

 

[Skyybot]

Here is some code that you can test to see where/how you want to enter it into your code. You will have to create a folder in your C:\ drive called Customers for this code to work but once you see how it works you can change the path to anything you like and change the code accordingly.

Sub test()
Dim sPath, customer As String
customer = InputBox("Enter customer name.", "Customer")
sPath = savePath(customer)
MsgBox "You are currently saving this to: " & sPath
End Sub
Function savePath(custName As String)

Dim fs, f, s

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\Customers")
Set s = f.SubFolders

For Each folder In s
    If folder.Name = custName Then
        sPath = "C:\Customers\" & custName
        savePath = sPath
        Exit Function
    End If
Next folder

s.Add custName
sPath = "C:\Customers\" & custName
savePath = sPath

End Function

It's the Function that's important here, not the sub. You would call the funtion from your sub.