Save as PDF with a cell value as filename

[muraterguen]

Hi,

I'm having a problem here which I searched in forums and I tried to fix it based on what I learned yet no success. I have actually asked this question to a thread, then figured that it was a solved case very old thread and wont get any attention for it. I need solution urgent so I might seek an answer in other forums as well such as ExcelGuru and ExcelForum( Cross post policy)

I wanted to create a macro selects some part of my worksheet and saves it in to the defined directory with the file name in A1.
A1 had the formula ( =TEXT(G2,"mmm/dd/yy")&" TO "&TEXT(P2,"mmm/dd/yy") ) as the file name display. I thought it was the problem so I moved the formula to A2, added Copy-Paste Values action to the macro (copy to A1 again) But still the same problem. It gives me an error saying Run time error 1004 File Not saved. When I click DEBUG, I see whole code block is colored yellow in VBA editor.

Here below is the code

I would appreciate if anyone could point out the mistake....


Sub PP()
'
' PP Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Range("A2").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1:U86").Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Murat Erguden\Dropbox\PLANET\5STAR CHA\PAYROLL\2017\ & Range("A1").Value ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Application.WindowState = xlNormal
End Sub

 

[gsistek]

I think the issue is with the filename itself. "/" is not allowed in a filename. Try changing the mmm/dd/yy to mmm-dd-yy in the formula.

 

[korhan]

I have this module already created and I am using it frequently. Check it to see if it fits your need or not. You can always modify it to suit your code. Mine is a procedure which takes two arguments. You can always create two variables instead within the code.

Sub CreatePDF(ByVal PDFName As String, ByVal market As String)
    
    ' Declare variables
    Dim path As String
    Dim wsPDF As Worksheet
    Dim nameFile As String  ' To hold the name of the PDF file
    
    ' Initialize variables and objects
    Set wsPDF = Worksheets("XXXXX")
    
    ' Set the path
    path = Application.ActiveWorkbook.path & "\" & "Watchlist\" & _
            CStr(Month(Date)) & "-" & CStr(Day(Date)) & "-" & _
            CStr(Year(Date)) & "\" & market ' To hold the path for the PDF
    
    ' Concatenate with PDF name
    nameFile = path & "\" & PDFName
    
    ' Create the PDF file
    With wsPDF
        .Select
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=nameFile, IgnorePrintAreas:=False
        End With
    End With
    
    ' Set PDF object to NULL
    Set wsPDF = Nothing
End Sub

 

[muraterguen]

I think the issue is with the filename itself. "/" is not allowed in a filename. Try changing the mmm/dd/yy to mmm-dd-yy in the formula.

That was the problem. Thanks a million!

 

[muraterguen]

Thank you Korhan. My problem was the "/" in the file name. Your code is way too complicated to me unless someone explains it to me row by row. I appreciate your time.

 

[Rhodie72]

Hi,

I'm having a problem here which I searched in forums and I tried to fix it based on what I learned yet no success. I have actually asked this question to a thread, then figured that it was a solved case very old thread and wont get any attention for it. I need solution urgent so I might seek an answer in other forums as well such as ExcelGuru and ExcelForum( Cross post policy)

I wanted to create a macro selects some part of my worksheet and saves it in to the defined directory with the file name in A1.
A1 had the formula ( =TEXT(G2,"mmm/dd/yy")&" TO "&TEXT(P2,"mmm/dd/yy") ) as the file name display. I thought it was the problem so I moved the formula to A2, added Copy-Paste Values action to the macro (copy to A1 again) But still the same problem. It gives me an error saying Run time error 1004 File Not saved. When I click DEBUG, I see whole code block is colored yellow in VBA editor.

Here below is the code

I would appreciate if anyone could point out the mistake....

Sub PP()
 '
 ' PP Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+P
 '
 Range("A2").Select
 Selection.Copy
 Range("A1").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Application.CutCopyMode = False
 Range("A1:U86").Select
 Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
 "C:\Users\Murat Erguden\Dropbox\PLANET\5STAR CHA\PAYROLL\2017\ & Range("A1").Value ".pdf" _
 , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
 :=False, OpenAfterPublish:=True
 Application.WindowState = xlNormal
 End Sub

I'm going to have a look...

 

[Rhodie72]

Syntax corrected.

Sub PP()
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    [A2].Copy [A1]
    [A1:U86].ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\Murat Erguden\Dropbox\PLANET\5STAR CHA\PAYROLL\2017\" & [A1].Value & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    Application.WindowState = xlNormal
End Sub

 

[Joe4]

Cross-posted:
Export as PDF with file name stored in a cell
and
Save as PDF - File name in a cell

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[muraterguen]

Cross-posted:
Export as PDF with file name stored in a cell
and
Save as PDF - File name in a cell

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

At the beginning of my text block (If you bothered to read) I have mentioned that I was going to post on other sites as well. The links were not provided because they did not exist by then.
I think this "sensitivity" over cross post issue a little too much. I understand you guys do not want to waste time for a question which might be already answered on another site but playing Cross Post Cop is also wasting time. Especially if the user mentioned his cross posting attempts and tried abiding by the forum rules.....