Help with automatic save and name excel on PDF

[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 froma cell 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

I hope some one can help me with this.

 

[John_w]

1:

    Dim parts() As String
    parts = Split(CeldaNombre.Value, " ")
   
    NombreArchivo = parts(1) & Format(Now, "_ddmmyy") & ".pdf"

2:

    Dim i As Long
    NombreArchivo = ""
    For i = 1 To UBound(parts)
        NombreArchivo = NombreArchivo & Left(parts(i), 1)
    Next
    NombreArchivo = NombreArchivo & Format(Now, "_ddmmyy") & ".pdf"

 

[Capy_Uther]

Srry sir im too new on this and i tried to use what you tell me but script doesnt run XD. This is my entire script

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

 

[John_w]

The description about which Range variable contains "Nombre: David Trujillo Gil" aren't consistent in your 2 posts.

Your OP said the name is in the CeldaNombre range variable.

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 froma cell on the excel
Im going to writte 2 examples:

1.- If that cell contains "Nombre: David Trujillo Gil"

However, now you say the name is in the CeldaNombreA range variable:

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"

My code was based on your OP and therefore I used CeldaNombre.

However, your code

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

is using both cells. What is in CeldaNombre (cell A10)? And should it be used to generate the file name?

 

[Capy_Uther]

The description about which Range variable contains "Nombre: David Trujillo Gil" aren't consistent in your 2 posts.

Your OP said the name is in the CeldaNombre range variable.


However, now you say the name is in the CeldaNombreA range variable:



My code was based on your OP and therefore I used CeldaNombre.

However, your code

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

is using both cells. What is in CeldaNombre (cell A10)? And should it be used to generate the file name?

Srry if i didnt explain my self

Celda.NombreA as you can see on the next image is just a cell with a number on it so its ok to print the whole number on the name of the PDF

The issue i have its ok CeldaNombre because as you can see on the next 2 images examples, this cell containt "Nombre:" followed by a name, this name would change all the time. So i want to use just the first name for the PDF as i said this will change all the time.

Rite now with the script i sent in the past post it works but all the time the scrip prints 7 characters of that name so if i run it with the example Nombre: SAUL JIMENEZ HERNANDEZ it prints "SAUL JI_Date" on the pdf name

And if i run it with the example :Nombre: RESGUARDO, INVESTIGACION Y SERVICIOS DE SEGURIDAD PRIVADA" it prints "RESGUAR_Date" on the pdf name



Example 1:

Example 2:

 

[John_w]

You didn't really answer my questions.

Celda.NombreA as you can see on the next image is just a cell with a number on it so its ok to print the whole number on the name of the PDF

But in your previous reply you said CeldaNombreA contains the name, for example "Nombre: David Trujillo Gil".

You seem to be confusing 2 different range variables, CeldaNombreA and CeldaNombre.

As a guess, because your posts haven't been very clear, maybe:

    Dim parts() 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
        parts = Split(CeldaNombre.Value, " ")
        NombreArchivo = Replace(parts(1), ",", "") & Format(Now, "_ddmmyy") & ".pdf"
    End If
    Debug.Print NombreArchivo

which outputs "David_160523.pdf", if A10 contains "Nombre: David Trujillo Gil". And it outputs "RESGUARDO_160523.pdf" if A10 contains "Nombre: RESGUARDO, INVESTIGACION Y SERVICIOS DE SEGURIDAD PRIVADA". Both these results are what you asked for.