Name of PDF From Cell Value

Legacy 436357 · Aug 12, 2019

Hello,

I have a cell value that I would like as the PDF file name plus today's date included in the name.

For instance:

HAN6597 8/12/2019

Is this possible to do? Also what if the same customer code (in cell H2) and same date are sent to PDF more than once? Does the PDF get replaced or ignored?

The 'New Invoice' is where the name of the PDF is from the code below. This is where the customer code and today's date should be.

Thank you so much for your help,
xj

Rich (BB code):

 Private Sub CommandButton1_Click()
     Dim NxtRw As Long
     Application.EnableEvents = False
     If Range("J3").Value = "" Then
        NxtRw = 3
     Else
        NxtRw = Range("J2").End(xlDown).Offset(1).Row
     End If
     Range("J" & NxtRw).Value = Range("H6").Value
     Range("K" & NxtRw).Value = Range("H3").Value
     Range("L" & NxtRw).Value = Format(Now(), "MM/dd/yyyy HH:MM:SS")
   
     Application.EnableEvents = True
      Range("B2:H53").ExportAsFixedFormat Type:=xlTypePDF, Filename:=CurrentPath & "C:\Users\xj\Desktop\Invoices\New Invoice", _
     Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
      :=False, OpenAfterPublish:=True
      
     
  End Sub

Twollaston · Aug 12, 2019

It's possible to name the pdf based on a cell, you could include the time as well, to avoid having duplicate files overwritten or skipped

I used something like this (where L5 is the Name of the PDF):

Dim wFile As String, DateTime As String
DateTime = Format(Now, "mm-dd-yyyy__h.mmam/pm")
wFile = ActiveSheet.Range("L5").Value & "_" & DateTime
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "" & wFile & ".pdf"

Legacy 436357 · Aug 12, 2019

Thank you can that be applied to my file path?

DanteAmor · Aug 12, 2019

Try this

If the file already exists, always replace the file.

Code:

Private Sub CommandButton1_Click()
  Dim NxtRw As Long, CurrentPath As String, wFile As String
  On Error GoTo EnEvents
  If Range("J3").Value = "" Then
    NxtRw = 3
  Else
    NxtRw = Range("J2").End(xlDown).Offset(1).Row
  End If
  Application.EnableEvents = False
  
  Range("J" & NxtRw).Value = Range("H6").Value
  Range("K" & NxtRw).Value = Range("H3").Value
  Range("L" & NxtRw).Value = Format(Now(), "mm/dd/yyyy HH:MM:SS")
  
  CurrentPath = "C:\Users\xj\Desktop\Invoices\"
 
  wFile = [H6] & [H3] & Format(Now(), "[COLOR=#0000ff]mm-dd-yyyy HH-MM-SS[/COLOR]") & ".pdf"   '[COLOR=#008000]must be hypen[/COLOR]
  Range("B2:H53").ExportAsFixedFormat Type:=xlTypePDF, Filename:=CurrentPath & wFile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True


EnEvents:
  Application.EnableEvents = True
End Sub

Twollaston · Aug 12, 2019

I'm not sure I always have to mess around with paths to get them to work, you could try it like this

Adding these in your code
Dim wFile As String, DateTime As String
DateTime = Format(Now, "mm-dd-yyyy__h.mmam/pm")
wFile = ActiveSheet.Range("L5").Value & "_" & DateTime

And then changing from:

Range("B2:H53").ExportAsFixedFormat Type:=xlTypePDF, Filename:=CurrentPath & "C:\Users\xj\Desktop\Invoices\New Invoice", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

To:

Range("B2:H53").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & wFile & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

Legacy 436357 · Aug 12, 2019

Thank you DanteAmor that is very close may I request it be slightly different?

I changed the H6 cell to H2 where the customer code resides and this is the name now:

HAN659710508-12-2019 15-21-48

I would like it to be:

HAN6597 105 08-12-2019 15:21:48

The first part is the customer code
The second part is the invoice number
The third part is the date of course
The fourth part is the time

Thank you

Twollaston · Aug 12, 2019

Change From:
wFile = [H6] & [H3] & Format(Now(), "mm-dd-yyyy HH-MM-SS") & ".pdf" 'must be hypen

To:
wFile = [H6] & " " & [H3] & " " & Format(Now(), "mm-dd-yyyy HH-MM-SS") & ".pdf" 'must be hypen

You can't use a colon in a file name
but instead of
HAN6597 105 08-12-2019 15:21:48
you could do
HAN6597 105 08-12-2019 15.21.48

or add AM/PM to make it more recognizable as a time

For Periods:
wFile = [H6] & " " & [H3] & " " & Format(Now(), "mm-dd-yyyy HH.MM.SS") & ".pdf" 'must be hypen
^^shows: HAN6597 105 08-12-2019 15.21.48

I normally do mine like this(with no seconds, having periods, and showing am/pm:
wFile = [H6] & " " & [H3] & " " & Format(Now(), "mm-dd-yyyy HH.MMam/pm") & ".pdf" 'must be hypen
^^ shows: HAN6597 105 08-12-2019 3.21pm

Legacy 436357 · Aug 12, 2019

That is great thanks Twollaston I actually like format that you normally do.

I was wondering at this point there aren't many invoices but eventually there will be quite a bit. Should I be setting these up in a different folder for each customer or perhaps each month? I am requesting your valuable experience.

Legacy 436357 · Aug 12, 2019

That's okay I can manage like it is. I appreciate all of the help today.

I hope I can return the favors one day.

Twollaston · Aug 12, 2019

xjohnson said:
That is great thanks Twollaston I actually like format that you normally do.

I was wondering at this point there aren't many invoices but eventually there will be quite a bit. Should I be setting these up in a different folder for each customer or perhaps each month? I am requesting your valuable experience.

You could create a folder for every customer code
and then add a variable like
CustCode = [H6]

and then update your folder path to

Rich (BB code):

CurrentPath = "C:\Users\xj\Desktop\Invoices\" & CustCode & "\"

You would have to create the folders first though, which can be kinda annoying if you have a lot of customer
if you have only a low amount of customer and alot of invoices, it would be pretty worthwhile i think

Or you could create a folder for each month

Rich (BB code):

MonthFolder = Format(Now, "mmmm") 
CurrentPath = "C:\Users\xj\Desktop\Invoices\" & MonthFolder & "\"

Note: "mmmm" means it's showing in the path as the full month name example. August
"mmm" means the path will show under the monthly abbreviation example. Aug

So you have to save your folder name accordingly or the path won't work

Name of PDF From Cell Value

Legacy 436357

Guest

Excel Facts

Twollaston

Board Regular

Legacy 436357

Guest

DanteAmor

Well-known Member

Twollaston

Board Regular

Legacy 436357

Guest

Twollaston

Board Regular

Legacy 436357

Guest

Legacy 436357

Guest

Twollaston

Board Regular

Similar threads

Forum statistics

Share this page

Name of PDF From Cell Value

Legacy 436357

Guest

Excel Facts

Twollaston

Board Regular

Legacy 436357

Guest

DanteAmor

Well-known Member

Twollaston

Board Regular

Legacy 436357

Guest

Twollaston

Board Regular

Legacy 436357

Guest

Legacy 436357

Guest

Twollaston

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock