Save as PDF and Automatic Name the File Issue

[Capy_Uther]

Hello to everyone like the topic say i made a button that print the excel file on PDF and auto save with a name, for the auto name the file im using the next line::

NombreArchivo = Mid(CeldaNombre.Value, 9, InStr(1, CeldaNombre.Value, " ", vbTextCompare) - 1) & _
"_" & Format(Now, "ddmmyy_") & Mid(CeldaNombreA.Value, 1) & ".pdf"

As you can see for the name im using the CeldaNombre.Value variable to take the information from the cell G4 on the excel
Im going to writte 2 examples:

1.- If that cell contains "Nombre: David Trujillo Gil" when i run that line it save the PDF with the name David T_Date.pdf What i can change on that line to auto save the line just with the fist name David_Date.pdf without the space and the T. And if that name changes i always want to auto save with the First Name and Date.

2.- I want to know how i can do the same thing but instead of first name take the 1st letter of all words and the date, using the same example Nombre: David Trujillo Gil then i want the auto save name the file DTG_Date.pdf and even if the name change do the same thing just the initials

The full script im using is the next one:

I hope some one can help me with this.

Sub BotonPDF()
Dim CeldaNombre As Range
Dim CeldaNombreA As Range
Dim NombreArchivo As String
Dim RutaArchivo As String

Set CeldaNombre = Hoja2.Range("A10")
Set CeldaNombreA = Hoja2.Range("G4")
If CeldaNombre.Value = "" Then
NombreArchivo = Format(Now, "ddmmyyyy_") & Mid(CeldaNombreA.Value, 1) & ".pdf"
Else
NombreArchivo = Mid(CeldaNombre.Value, 9, InStr(1, CeldaNombre.Value, " ", vbTextCompare) - 1) & _
"_" & Format(Now, "ddmmyy_") & Mid(CeldaNombreA.Value, 1) & ".pdf"
End If

RutaArchivo = Application.ActiveWorkbook.Path & _
Application.PathSeparator & _
NombreArchivo

Hoja2.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=RutaArchivo, _
Quality:=xlQualityStandard, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Hoja2.Range("A4").Select
End Sub

As you can see Set CeldaNombreA = Hoja2.Range("G4") this is the command that takes from cell G4 the information for the file name. If this cell contains "Nombre: David Trujillo Gil", for the name of the file i want to ignore "Nombre:" and name the file with just the first name of the person and date (this name will be changing).

And in the second example i want also ignore "Nombre:" and name the file with the initials of the full name in this case DTG and date but the name will be changing

Thanks for your fast respond

 

[DanteAmor]

Hi @Capy_Uther , thanks for posting on the forum.

As you can see Set CeldaNombreA = Hoja2.Range("G4") this is the command that takes from cell G4 the information for the file name. If this cell contains "Nombre: David Trujillo Gil", for the name of the file i want to ignore "Nombre:" and name the file with just the first name of the person

Considering cell G4, because I don't understand what you have in cell A10.

For the name:

Sub BotonPDF()
  Dim CeldaNombre As Range
  Dim CeldaNombreA As Range
  Dim NombreArchivo As String
  Dim RutaArchivo As String
  Dim nombre As Variant
 
  Set CeldaNombre = Hoja2.Range("A10")
  Set CeldaNombreA = Hoja2.Range("G4")
 
  '1 first name only
  nombre = Split(Mid(CeldaNombreA, 9), " ")(0)
  NombreArchivo = nombre & " " & Format(Now, "ddmmyyyy_") & ".pdf"
  RutaArchivo = Application.ActiveWorkbook.Path & Application.PathSeparator & NombreArchivo
 
  Hoja2.ExportAsFixedFormat Type:=xlTypePDF, filename:=RutaArchivo, _
    Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=True
  
  Hoja2.Range("A4").Select
End Sub

For the intials:

Sub BotonPDF()
  Dim CeldaNombre As Range
  Dim CeldaNombreA As Range
  Dim NombreArchivo As String
  Dim RutaArchivo As String
  Dim nombre As Variant, init As Variant
 
  Set CeldaNombre = Hoja2.Range("A10")
  Set CeldaNombreA = Hoja2.Range("G4")
 
  '2 initials
  For Each init In Split(Mid(CeldaNombreA, 9), " ")
    nombre = nombre & Left(init, 1)
  Next
  NombreArchivo = nombre & " " & Format(Now, "ddmmyyyy_") & ".pdf"
  RutaArchivo = Application.ActiveWorkbook.Path & Application.PathSeparator & NombreArchivo
 
  Hoja2.ExportAsFixedFormat Type:=xlTypePDF, filename:=RutaArchivo, _
    Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=True
  
  Hoja2.Range("A4").Select
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[Capy_Uther]

Hi @Capy_Uther , thanks for posting on the forum.



Considering cell G4, because I don't understand what you have in cell A10.

For the name:

Sub BotonPDF()
  Dim CeldaNombre As Range
  Dim CeldaNombreA As Range
  Dim NombreArchivo As String
  Dim RutaArchivo As String
  Dim nombre As Variant
 
  Set CeldaNombre = Hoja2.Range("A10")
  Set CeldaNombreA = Hoja2.Range("G4")
 
  '1 first name only
  nombre = Split(Mid(CeldaNombreA, 9), " ")(0)
  NombreArchivo = nombre & " " & Format(Now, "ddmmyyyy_") & ".pdf"
  RutaArchivo = Application.ActiveWorkbook.Path & Application.PathSeparator & NombreArchivo
 
  Hoja2.ExportAsFixedFormat Type:=xlTypePDF, filename:=RutaArchivo, _
    Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=True
 
  Hoja2.Range("A4").Select
End Sub

For the intials:

Sub BotonPDF()
  Dim CeldaNombre As Range
  Dim CeldaNombreA As Range
  Dim NombreArchivo As String
  Dim RutaArchivo As String
  Dim nombre As Variant, init As Variant
 
  Set CeldaNombre = Hoja2.Range("A10")
  Set CeldaNombreA = Hoja2.Range("G4")
 
  '2 initials
  For Each init In Split(Mid(CeldaNombreA, 9), " ")
    nombre = nombre & Left(init, 1)
  Next
  NombreArchivo = nombre & " " & Format(Now, "ddmmyyyy_") & ".pdf"
  RutaArchivo = Application.ActiveWorkbook.Path & Application.PathSeparator & NombreArchivo
 
  Hoja2.ExportAsFixedFormat Type:=xlTypePDF, filename:=RutaArchivo, _
    Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=True
 
  Hoja2.Range("A4").Select
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Wow thanks alot Sir i realy apreciate, one more question on the initials example is there a way to tell the script that if the name contains just 1 name not just take the initial, in that case grab the the whole first name example:

If CeldaNombre has the name Alfredo whit no last names like this "Nombre: Alfredo" the name of the pdf will be "A_Date.pdf", so i would like that the prograb grab intitals on cases whit more than 1 name but if theres a case like this where the person has only the first name the script use the entire name and not just the initial to name the PDF "Alfredo_Date.pdf".

Regards

 

[DanteAmor]

Try this:

Sub BotonPDF()
  Dim CeldaNombre As Range
  Dim CeldaNombreA As Range
  Dim NombreArchivo As String
  Dim RutaArchivo As String
  Dim datos As Variant, nombre As Variant, init As Variant
  
  Set CeldaNombre = Hoja2.Range("A10")
  Set CeldaNombreA = Hoja2.Range("G4")
  
  '
  datos = Split(Mid(CeldaNombreA, 9), " ")
  If UBound(datos) = 0 Then
    nombre = Split(Mid(CeldaNombreA, 9), " ")(0)
  Else
    For Each init In Split(Mid(CeldaNombreA, 9), " ")
      nombre = nombre & Left(init, 1)
    Next
  End If
  NombreArchivo = nombre & "_" & Format(Now, "ddmmyyyy") & ".pdf"
  RutaArchivo = Application.ActiveWorkbook.Path & Application.PathSeparator & NombreArchivo
  
  Hoja2.ExportAsFixedFormat Type:=xlTypePDF, filename:=RutaArchivo, _
    Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
  Hoja2.Range("A4").Select
End Sub