How to print excel file to pdf with the option “.ExportAsFixedFormat” via excel macro
0
I have wrote a code to print excel file to .PDF file with the page setups parameters.And also it eliminates the need of having a prompt dialog box also.
But I need to know if I need to name the .PDF file as same as the excel file name with below code but not the same destination path.As an example:= if excel file name is "Quality Report 1411185623689" This file is generated by a system therefore its name is changed everyday.
How do I solve this?
Sub Save_As_PDF()
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:DesktopReportsSame as excel file name", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
excel vba excel-vba pdf
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
asked Nov 14 '18 at 3:53
Nilusha M.
285
add a comment |
0
I have wrote a code to print excel file to .PDF file with the page setups parameters.And also it eliminates the need of having a prompt dialog box also.
But I need to know if I need to name the .PDF file as same as the excel file name with below code but not the same destination path.As an example:= if excel file name is "Quality Report 1411185623689" This file is generated by a system therefore its name is changed everyday.
How do I solve this?
Sub Save_As_PDF()
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:DesktopReportsSame as excel file name", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
excel vba excel-vba pdf
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
asked Nov 14 '18 at 3:53
Nilusha M.
285
1
You can name the PDF whatever you want to. What name do you want to give to the PDF? Will it vary each time? Is it a derivative of the generated name?
– alowflyingpig
Nov 14 '18 at 3:58
1
This is a duplicate. See this SO question
– GMalc
Nov 14 '18 at 4:06
@alowflyingpig ,I would really appreciate answering my question where I got stuck.Yes,my system generated excel file name will vary everyday such as "Quality Report 141256522356" like this some digits included in its name.so those digits will vary everyday when this file is generated by the system.I need to print the pdf with the same name of excel file.
– Nilusha M.
Nov 14 '18 at 5:23
add a comment |
0
0
0
I have wrote a code to print excel file to .PDF file with the page setups parameters.And also it eliminates the need of having a prompt dialog box also.
But I need to know if I need to name the .PDF file as same as the excel file name with below code but not the same destination path.As an example:= if excel file name is "Quality Report 1411185623689" This file is generated by a system therefore its name is changed everyday.
How do I solve this?
Sub Save_As_PDF()
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:DesktopReportsSame as excel file name", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
excel vba excel-vba pdf
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
asked Nov 14 '18 at 3:53
Nilusha M.
285
I have wrote a code to print excel file to .PDF file with the page setups parameters.And also it eliminates the need of having a prompt dialog box also.
But I need to know if I need to name the .PDF file as same as the excel file name with below code but not the same destination path.As an example:= if excel file name is "Quality Report 1411185623689" This file is generated by a system therefore its name is changed everyday.
How do I solve this?
Sub Save_As_PDF()
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:DesktopReportsSame as excel file name", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
excel vba excel-vba pdf
excel vba excel-vba pdf
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
asked Nov 14 '18 at 3:53
Nilusha M.
285
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
asked Nov 14 '18 at 3:53
Nilusha M.
285
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
edited Nov 14 '18 at 7:55
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 14 '18 at 3:53
Nilusha M.
285
asked Nov 14 '18 at 3:53
Nilusha M.
285
asked Nov 14 '18 at 3:53
Nilusha M.
285
285
1
You can name the PDF whatever you want to. What name do you want to give to the PDF? Will it vary each time? Is it a derivative of the generated name?
– alowflyingpig
Nov 14 '18 at 3:58
1
This is a duplicate. See this SO question
– GMalc
Nov 14 '18 at 4:06
@alowflyingpig ,I would really appreciate answering my question where I got stuck.Yes,my system generated excel file name will vary everyday such as "Quality Report 141256522356" like this some digits included in its name.so those digits will vary everyday when this file is generated by the system.I need to print the pdf with the same name of excel file.
– Nilusha M.
Nov 14 '18 at 5:23
add a comment |
1
You can name the PDF whatever you want to. What name do you want to give to the PDF? Will it vary each time? Is it a derivative of the generated name?
– alowflyingpig
Nov 14 '18 at 3:58
1
This is a duplicate. See this SO question
– GMalc
Nov 14 '18 at 4:06
@alowflyingpig ,I would really appreciate answering my question where I got stuck.Yes,my system generated excel file name will vary everyday such as "Quality Report 141256522356" like this some digits included in its name.so those digits will vary everyday when this file is generated by the system.I need to print the pdf with the same name of excel file.
– Nilusha M.
Nov 14 '18 at 5:23
1
1
You can name the PDF whatever you want to. What name do you want to give to the PDF? Will it vary each time? Is it a derivative of the generated name?
– alowflyingpig
Nov 14 '18 at 3:58
You can name the PDF whatever you want to. What name do you want to give to the PDF? Will it vary each time? Is it a derivative of the generated name?
– alowflyingpig
Nov 14 '18 at 3:58
1
1
This is a duplicate. See this SO question
– GMalc
Nov 14 '18 at 4:06
This is a duplicate. See this SO question
– GMalc
Nov 14 '18 at 4:06
@alowflyingpig ,I would really appreciate answering my question where I got stuck.Yes,my system generated excel file name will vary everyday such as "Quality Report 141256522356" like this some digits included in its name.so those digits will vary everyday when this file is generated by the system.I need to print the pdf with the same name of excel file.
– Nilusha M.
Nov 14 '18 at 5:23
@alowflyingpig ,I would really appreciate answering my question where I got stuck.Yes,my system generated excel file name will vary everyday such as "Quality Report 141256522356" like this some digits included in its name.so those digits will vary everyday when this file is generated by the system.I need to print the pdf with the same name of excel file.
– Nilusha M.
Nov 14 '18 at 5:23
add a comment |
1 Answer
1
active
oldest
votes
1
Untested, but assuming you want to name the PDF the same as the Excel file (ignoring file extension), but in a different folder (say some folder/directory called "C:DesktopReports" for example):
Option explicit
Sub SaveAsPDF()
Dim folderPath as string
folderPath = "C:DesktopReports" ' Change to whatever folder, but make sure it ends with a
If len(dir$(folderPath, vbDirectory)) = 0 then
Msgbox("'" & folderPath & "' is not a valid/existing directory. Abandoning export. Code will stop running now.")
Exit sub
End if
Dim Filename as string
Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1) & ".pdf"
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=folderPath & filename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
answered Nov 14 '18 at 4:18
chillin
1,229134
1
Sorry, there was an extra.in my answer, causing invalid syntax. Try again if you need to.
– chillin
Nov 14 '18 at 5:29
In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this?
– Nilusha M.
Nov 14 '18 at 5:32
1
Get rid of the.before the&-- or just copy-paste the code in my answer (I have fixed it there) and try again.
– chillin
Nov 14 '18 at 5:35
Oh Yes.It got worked.!
– Nilusha M.
Nov 14 '18 at 5:38
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53292961%2fhow-to-print-excel-file-to-pdf-with-the-option-exportasfixedformat-via-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Untested, but assuming you want to name the PDF the same as the Excel file (ignoring file extension), but in a different folder (say some folder/directory called "C:DesktopReports" for example):
Option explicit
Sub SaveAsPDF()
Dim folderPath as string
folderPath = "C:DesktopReports" ' Change to whatever folder, but make sure it ends with a
If len(dir$(folderPath, vbDirectory)) = 0 then
Msgbox("'" & folderPath & "' is not a valid/existing directory. Abandoning export. Code will stop running now.")
Exit sub
End if
Dim Filename as string
Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1) & ".pdf"
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=folderPath & filename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
answered Nov 14 '18 at 4:18
chillin
1,229134
1
Sorry, there was an extra.in my answer, causing invalid syntax. Try again if you need to.
– chillin
Nov 14 '18 at 5:29
In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this?
– Nilusha M.
Nov 14 '18 at 5:32
1
Get rid of the.before the&-- or just copy-paste the code in my answer (I have fixed it there) and try again.
– chillin
Nov 14 '18 at 5:35
Oh Yes.It got worked.!
– Nilusha M.
Nov 14 '18 at 5:38
add a comment |
1
Untested, but assuming you want to name the PDF the same as the Excel file (ignoring file extension), but in a different folder (say some folder/directory called "C:DesktopReports" for example):
Option explicit
Sub SaveAsPDF()
Dim folderPath as string
folderPath = "C:DesktopReports" ' Change to whatever folder, but make sure it ends with a
If len(dir$(folderPath, vbDirectory)) = 0 then
Msgbox("'" & folderPath & "' is not a valid/existing directory. Abandoning export. Code will stop running now.")
Exit sub
End if
Dim Filename as string
Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1) & ".pdf"
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=folderPath & filename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
answered Nov 14 '18 at 4:18
chillin
1,229134
1
Sorry, there was an extra.in my answer, causing invalid syntax. Try again if you need to.
– chillin
Nov 14 '18 at 5:29
In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this?
– Nilusha M.
Nov 14 '18 at 5:32
1
Get rid of the.before the&-- or just copy-paste the code in my answer (I have fixed it there) and try again.
– chillin
Nov 14 '18 at 5:35
Oh Yes.It got worked.!
– Nilusha M.
Nov 14 '18 at 5:38
add a comment |
1
1
1
Untested, but assuming you want to name the PDF the same as the Excel file (ignoring file extension), but in a different folder (say some folder/directory called "C:DesktopReports" for example):
Option explicit
Sub SaveAsPDF()
Dim folderPath as string
folderPath = "C:DesktopReports" ' Change to whatever folder, but make sure it ends with a
If len(dir$(folderPath, vbDirectory)) = 0 then
Msgbox("'" & folderPath & "' is not a valid/existing directory. Abandoning export. Code will stop running now.")
Exit sub
End if
Dim Filename as string
Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1) & ".pdf"
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=folderPath & filename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
answered Nov 14 '18 at 4:18
chillin
1,229134
Untested, but assuming you want to name the PDF the same as the Excel file (ignoring file extension), but in a different folder (say some folder/directory called "C:DesktopReports" for example):
Option explicit
Sub SaveAsPDF()
Dim folderPath as string
folderPath = "C:DesktopReports" ' Change to whatever folder, but make sure it ends with a
If len(dir$(folderPath, vbDirectory)) = 0 then
Msgbox("'" & folderPath & "' is not a valid/existing directory. Abandoning export. Code will stop running now.")
Exit sub
End if
Dim Filename as string
Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1) & ".pdf"
With ActiveSheet.PageSetup
.Orientation=xlLandscape
.Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=folderPath & filename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Exit Sub
answered Nov 14 '18 at 4:18
chillin
1,229134
edited Nov 14 '18 at 5:27
answered Nov 14 '18 at 4:18
chillin
1,229134
answered Nov 14 '18 at 4:18
chillin
1,229134
answered Nov 14 '18 at 4:18
chillin
1,229134
1,229134
1
Sorry, there was an extra.in my answer, causing invalid syntax. Try again if you need to.
– chillin
Nov 14 '18 at 5:29
In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this?
– Nilusha M.
Nov 14 '18 at 5:32
1
Get rid of the.before the&-- or just copy-paste the code in my answer (I have fixed it there) and try again.
– chillin
Nov 14 '18 at 5:35
Oh Yes.It got worked.!
– Nilusha M.
Nov 14 '18 at 5:38
add a comment |
1
Sorry, there was an extra.in my answer, causing invalid syntax. Try again if you need to.
– chillin
Nov 14 '18 at 5:29
In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this?
– Nilusha M.
Nov 14 '18 at 5:32
1
Get rid of the.before the&-- or just copy-paste the code in my answer (I have fixed it there) and try again.
– chillin
Nov 14 '18 at 5:35
Oh Yes.It got worked.!
– Nilusha M.
Nov 14 '18 at 5:38
1
1
Sorry, there was an extra
. in my answer, causing invalid syntax. Try again if you need to.– chillin
Nov 14 '18 at 5:29
Sorry, there was an extra
. in my answer, causing invalid syntax. Try again if you need to.– chillin
Nov 14 '18 at 5:29
In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this?
– Nilusha M.
Nov 14 '18 at 5:32
In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this?
– Nilusha M.
Nov 14 '18 at 5:32
1
1
Get rid of the
. before the & -- or just copy-paste the code in my answer (I have fixed it there) and try again.– chillin
Nov 14 '18 at 5:35
Get rid of the
. before the & -- or just copy-paste the code in my answer (I have fixed it there) and try again.– chillin
Nov 14 '18 at 5:35
Oh Yes.It got worked.!
– Nilusha M.
Nov 14 '18 at 5:38
Oh Yes.It got worked.!
– Nilusha M.
Nov 14 '18 at 5:38
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53292961%2fhow-to-print-excel-file-to-pdf-with-the-option-exportasfixedformat-via-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You can name the PDF whatever you want to. What name do you want to give to the PDF? Will it vary each time? Is it a derivative of the generated name?
– alowflyingpig
Nov 14 '18 at 3:58
1
This is a duplicate. See this SO question
– GMalc
Nov 14 '18 at 4:06
@alowflyingpig ,I would really appreciate answering my question where I got stuck.Yes,my system generated excel file name will vary everyday such as "Quality Report 141256522356" like this some digits included in its name.so those digits will vary everyday when this file is generated by the system.I need to print the pdf with the same name of excel file.
– Nilusha M.
Nov 14 '18 at 5:23