Save as PDF with a cell value as filename

muraterguen

New Member
Joined
Jan 8, 2016
Messages
23
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
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

Board Regular
Joined
Nov 6, 2009
Messages
214
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.
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
 
Last edited:

muraterguen

New Member
Joined
Jan 8, 2016
Messages
23

ADVERTISEMENT

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

Well-known Member
Joined
Apr 18, 2016
Messages
546
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....


Code:
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...
 
Last edited:

Rhodie72

Well-known Member
Joined
Apr 18, 2016
Messages
546

ADVERTISEMENT

Syntax corrected.
Code:
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
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 8, 2016
Messages
23
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.....
 

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,208
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top