export file as PDF but with the name as mentioned in cell D27 in sheet "Faktuur"

FrankLinssen

New Member
Joined
Jun 28, 2015
Messages
5
The macro currently has the following

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"V:\ingrid\Book9.pdf", Quality:=xlQualityStandard, IncludeDocProperties:= _
True, IgnorePrintAreas:=False, OpenAfterPublish:=False

I want this to be changed as such that it takes the value from cell D27 in tab "Faktuur".

Also note that in cell D27 there is a formula (=Fact_nr), so the name of the to be exported PDF should be the outcome of the formula in cell D27 e.g. "ZK2019/SBBZo06"

I hope my question is clear.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
The "/" in "ZK2019/SBBZo06" is not a valid character in a file name, because it represents a subfolder separator. This code therefore replaces the "/" with "_" to create V:\ingrid\ZK2019_SBBZo06.pdf.

Code:
    Dim PDFfile As String
    PDFfile = "V:\ingrid\" & Replace(Worksheets("Faktuur").Range("D27").Value, "/", "_") & ".pdf"
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
Upvote 0
The "/" in "ZK2019/SBBZo06" is not a valid character in a file name, because it represents a subfolder separator. This code therefore replaces the "/" with "_" to create V:\ingrid\ZK2019_SBBZo06.pdf.

Code:
    Dim PDFfile As String
    PDFfile = "V:\ingrid\" & Replace(Worksheets("Faktuur").Range("D27").Value, "/", "_") & ".pdf"
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Thanks John.

Q1: after adjusting the macro I get a Run-time error '-2147024773 (8007007b)':
Document not saved

Any idea what could be causing this?

Q2: How should the above be written exactly in case of the Invoice-numbers already having the "_" instead of the "/"
Thanks in advance
 
Upvote 0
1. Don't know. Is it a valid file name? Add:
Code:
MsgBox PDFfile

2.
Code:
PDFfile = "V:\ingrid\" & Worksheets("Faktuur").Range("D27").Value & ".pdf"
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top