VBA Export Excel to PDF
I'm getting a '1004' error when running this macro.
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/filename.pdf", _
OpenAfterPublish:=False
End Sub
Image of the Error
This same code works fine if the name of the file is "PDF.pdf". It seems like I can only export the PDF with the name "PDF.pdf".
The code below returns no errors. Why is this happening?
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/pdf.pdf", _
OpenAfterPublish:=False
End Sub
excel vba excel-vba pdf
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
|
show 3 more comments
I'm getting a '1004' error when running this macro.
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/filename.pdf", _
OpenAfterPublish:=False
End Sub
Image of the Error
This same code works fine if the name of the file is "PDF.pdf". It seems like I can only export the PDF with the name "PDF.pdf".
The code below returns no errors. Why is this happening?
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/pdf.pdf", _
OpenAfterPublish:=False
End Sub
excel vba excel-vba pdf
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
So add another bit of code to rename the pdf.pdf after it has been exported...
– Solar Mike
Nov 12 '18 at 22:24
I've only ever used Windows, but can you start a filepath with a forward slash like that on a Mac?
– dwirony
Nov 12 '18 at 22:31
@dwirony the forward slash works fine in Mac. The file "PDF.pdf" was created manually before the macro was run – while "Filename.pdf" doesn't exist. Is there anything in the Document properties that could be causing the error?
– Gonzalo
Nov 12 '18 at 22:52
Are you sure that it is not just that filename.pdf is open somewhere?
– RichardBJ
Nov 12 '18 at 23:20
@RichardBJ initially I also thought about that. I checked it and all files are closed
– Gonzalo
Nov 12 '18 at 23:26
|
show 3 more comments
I'm getting a '1004' error when running this macro.
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/filename.pdf", _
OpenAfterPublish:=False
End Sub
Image of the Error
This same code works fine if the name of the file is "PDF.pdf". It seems like I can only export the PDF with the name "PDF.pdf".
The code below returns no errors. Why is this happening?
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/pdf.pdf", _
OpenAfterPublish:=False
End Sub
excel vba excel-vba pdf
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
I'm getting a '1004' error when running this macro.
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/filename.pdf", _
OpenAfterPublish:=False
End Sub
Image of the Error
This same code works fine if the name of the file is "PDF.pdf". It seems like I can only export the PDF with the name "PDF.pdf".
The code below returns no errors. Why is this happening?
Sub macro_PDF()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="/Users/username/Desktop/tests/pdf.pdf", _
OpenAfterPublish:=False
End Sub
excel vba excel-vba pdf
excel vba excel-vba pdf
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
edited Nov 13 '18 at 7:19
Pᴇʜ
23.8k62952
23.8k62952
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
asked Nov 12 '18 at 22:20
GonzaloGonzalo
147
147
So add another bit of code to rename the pdf.pdf after it has been exported...
– Solar Mike
Nov 12 '18 at 22:24
I've only ever used Windows, but can you start a filepath with a forward slash like that on a Mac?
– dwirony
Nov 12 '18 at 22:31
@dwirony the forward slash works fine in Mac. The file "PDF.pdf" was created manually before the macro was run – while "Filename.pdf" doesn't exist. Is there anything in the Document properties that could be causing the error?
– Gonzalo
Nov 12 '18 at 22:52
Are you sure that it is not just that filename.pdf is open somewhere?
– RichardBJ
Nov 12 '18 at 23:20
@RichardBJ initially I also thought about that. I checked it and all files are closed
– Gonzalo
Nov 12 '18 at 23:26
|
show 3 more comments
So add another bit of code to rename the pdf.pdf after it has been exported...
– Solar Mike
Nov 12 '18 at 22:24
I've only ever used Windows, but can you start a filepath with a forward slash like that on a Mac?
– dwirony
Nov 12 '18 at 22:31
@dwirony the forward slash works fine in Mac. The file "PDF.pdf" was created manually before the macro was run – while "Filename.pdf" doesn't exist. Is there anything in the Document properties that could be causing the error?
– Gonzalo
Nov 12 '18 at 22:52
Are you sure that it is not just that filename.pdf is open somewhere?
– RichardBJ
Nov 12 '18 at 23:20
@RichardBJ initially I also thought about that. I checked it and all files are closed
– Gonzalo
Nov 12 '18 at 23:26
So add another bit of code to rename the pdf.pdf after it has been exported...
– Solar Mike
Nov 12 '18 at 22:24
So add another bit of code to rename the pdf.pdf after it has been exported...
– Solar Mike
Nov 12 '18 at 22:24
I've only ever used Windows, but can you start a filepath with a forward slash like that on a Mac?
– dwirony
Nov 12 '18 at 22:31
I've only ever used Windows, but can you start a filepath with a forward slash like that on a Mac?
– dwirony
Nov 12 '18 at 22:31
@dwirony the forward slash works fine in Mac. The file "PDF.pdf" was created manually before the macro was run – while "Filename.pdf" doesn't exist. Is there anything in the Document properties that could be causing the error?
– Gonzalo
Nov 12 '18 at 22:52
@dwirony the forward slash works fine in Mac. The file "PDF.pdf" was created manually before the macro was run – while "Filename.pdf" doesn't exist. Is there anything in the Document properties that could be causing the error?
– Gonzalo
Nov 12 '18 at 22:52
Are you sure that it is not just that filename.pdf is open somewhere?
– RichardBJ
Nov 12 '18 at 23:20
Are you sure that it is not just that filename.pdf is open somewhere?
– RichardBJ
Nov 12 '18 at 23:20
@RichardBJ initially I also thought about that. I checked it and all files are closed
– Gonzalo
Nov 12 '18 at 23:26
@RichardBJ initially I also thought about that. I checked it and all files are closed
– Gonzalo
Nov 12 '18 at 23:26
|
show 3 more comments
1 Answer
1
active
oldest
votes
I have run/debugged this a few times on my PC and of course, the name of the file makes no difference. Filename.pdf exports just fine. This error seems to have two potential causes. (1) Is that the file path itself was not found, the other (2) is that the file already exists. So check that first like this:
Sub macro_PDF()
Filename = "c:/fmob/filename.pdf"
If Dir(Filename) <> vbNullString Then
Kill Filename
End If
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Filename, _
OpenAfterPublish:=False
End Sub
I encountered one situation where the macro could not write the file even after deleting longhand (i.e., using explorer) and this seems to have resulted from a bug of some sort with PDF Acrobat not closing all file handles correctly. Presumably rebooting would fix that.
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
I really liked the idea about adding theDircommand. I copied and pasted your code to test it. TheDircommand is not finding the file – I guess that there's an issue with the route – so I added the following code to make sure i'm pointing to the right file. However it's still not working right :-(Dim path As String Dim FileName As String path = ThisWorkbook.path FileName = "filename.pdf" If Dir(path & FileName) <> vbNullString Then Kill (path & "/" & FileName) End If
– Gonzalo
Nov 14 '18 at 18:13
I found that in a Mac I have to replace the slashes ("/") by colons (":") insideFilename.path. The macro always creates a new PDF or it overwrites the existing file. TheKill Filenamenever works. I don't understand why the same code creates a new file in the destination folder but can't find that file if it already exists. any help? thnx
– Gonzalo
Nov 14 '18 at 22:20
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
);
);
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%2f53270930%2fvba-export-excel-to-pdf%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
I have run/debugged this a few times on my PC and of course, the name of the file makes no difference. Filename.pdf exports just fine. This error seems to have two potential causes. (1) Is that the file path itself was not found, the other (2) is that the file already exists. So check that first like this:
Sub macro_PDF()
Filename = "c:/fmob/filename.pdf"
If Dir(Filename) <> vbNullString Then
Kill Filename
End If
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Filename, _
OpenAfterPublish:=False
End Sub
I encountered one situation where the macro could not write the file even after deleting longhand (i.e., using explorer) and this seems to have resulted from a bug of some sort with PDF Acrobat not closing all file handles correctly. Presumably rebooting would fix that.
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
I really liked the idea about adding theDircommand. I copied and pasted your code to test it. TheDircommand is not finding the file – I guess that there's an issue with the route – so I added the following code to make sure i'm pointing to the right file. However it's still not working right :-(Dim path As String Dim FileName As String path = ThisWorkbook.path FileName = "filename.pdf" If Dir(path & FileName) <> vbNullString Then Kill (path & "/" & FileName) End If
– Gonzalo
Nov 14 '18 at 18:13
I found that in a Mac I have to replace the slashes ("/") by colons (":") insideFilename.path. The macro always creates a new PDF or it overwrites the existing file. TheKill Filenamenever works. I don't understand why the same code creates a new file in the destination folder but can't find that file if it already exists. any help? thnx
– Gonzalo
Nov 14 '18 at 22:20
add a comment |
I have run/debugged this a few times on my PC and of course, the name of the file makes no difference. Filename.pdf exports just fine. This error seems to have two potential causes. (1) Is that the file path itself was not found, the other (2) is that the file already exists. So check that first like this:
Sub macro_PDF()
Filename = "c:/fmob/filename.pdf"
If Dir(Filename) <> vbNullString Then
Kill Filename
End If
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Filename, _
OpenAfterPublish:=False
End Sub
I encountered one situation where the macro could not write the file even after deleting longhand (i.e., using explorer) and this seems to have resulted from a bug of some sort with PDF Acrobat not closing all file handles correctly. Presumably rebooting would fix that.
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
I really liked the idea about adding theDircommand. I copied and pasted your code to test it. TheDircommand is not finding the file – I guess that there's an issue with the route – so I added the following code to make sure i'm pointing to the right file. However it's still not working right :-(Dim path As String Dim FileName As String path = ThisWorkbook.path FileName = "filename.pdf" If Dir(path & FileName) <> vbNullString Then Kill (path & "/" & FileName) End If
– Gonzalo
Nov 14 '18 at 18:13
I found that in a Mac I have to replace the slashes ("/") by colons (":") insideFilename.path. The macro always creates a new PDF or it overwrites the existing file. TheKill Filenamenever works. I don't understand why the same code creates a new file in the destination folder but can't find that file if it already exists. any help? thnx
– Gonzalo
Nov 14 '18 at 22:20
add a comment |
I have run/debugged this a few times on my PC and of course, the name of the file makes no difference. Filename.pdf exports just fine. This error seems to have two potential causes. (1) Is that the file path itself was not found, the other (2) is that the file already exists. So check that first like this:
Sub macro_PDF()
Filename = "c:/fmob/filename.pdf"
If Dir(Filename) <> vbNullString Then
Kill Filename
End If
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Filename, _
OpenAfterPublish:=False
End Sub
I encountered one situation where the macro could not write the file even after deleting longhand (i.e., using explorer) and this seems to have resulted from a bug of some sort with PDF Acrobat not closing all file handles correctly. Presumably rebooting would fix that.
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
I have run/debugged this a few times on my PC and of course, the name of the file makes no difference. Filename.pdf exports just fine. This error seems to have two potential causes. (1) Is that the file path itself was not found, the other (2) is that the file already exists. So check that first like this:
Sub macro_PDF()
Filename = "c:/fmob/filename.pdf"
If Dir(Filename) <> vbNullString Then
Kill Filename
End If
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Filename, _
OpenAfterPublish:=False
End Sub
I encountered one situation where the macro could not write the file even after deleting longhand (i.e., using explorer) and this seems to have resulted from a bug of some sort with PDF Acrobat not closing all file handles correctly. Presumably rebooting would fix that.
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
edited Nov 13 '18 at 12:56
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
answered Nov 13 '18 at 12:44
RichardBJRichardBJ
1665
1665
I really liked the idea about adding theDircommand. I copied and pasted your code to test it. TheDircommand is not finding the file – I guess that there's an issue with the route – so I added the following code to make sure i'm pointing to the right file. However it's still not working right :-(Dim path As String Dim FileName As String path = ThisWorkbook.path FileName = "filename.pdf" If Dir(path & FileName) <> vbNullString Then Kill (path & "/" & FileName) End If
– Gonzalo
Nov 14 '18 at 18:13
I found that in a Mac I have to replace the slashes ("/") by colons (":") insideFilename.path. The macro always creates a new PDF or it overwrites the existing file. TheKill Filenamenever works. I don't understand why the same code creates a new file in the destination folder but can't find that file if it already exists. any help? thnx
– Gonzalo
Nov 14 '18 at 22:20
add a comment |
I really liked the idea about adding theDircommand. I copied and pasted your code to test it. TheDircommand is not finding the file – I guess that there's an issue with the route – so I added the following code to make sure i'm pointing to the right file. However it's still not working right :-(Dim path As String Dim FileName As String path = ThisWorkbook.path FileName = "filename.pdf" If Dir(path & FileName) <> vbNullString Then Kill (path & "/" & FileName) End If
– Gonzalo
Nov 14 '18 at 18:13
I found that in a Mac I have to replace the slashes ("/") by colons (":") insideFilename.path. The macro always creates a new PDF or it overwrites the existing file. TheKill Filenamenever works. I don't understand why the same code creates a new file in the destination folder but can't find that file if it already exists. any help? thnx
– Gonzalo
Nov 14 '18 at 22:20
I really liked the idea about adding the
Dircommand. I copied and pasted your code to test it. The Dir command is not finding the file – I guess that there's an issue with the route – so I added the following code to make sure i'm pointing to the right file. However it's still not working right :-( Dim path As String Dim FileName As String path = ThisWorkbook.path FileName = "filename.pdf" If Dir(path & FileName) <> vbNullString Then Kill (path & "/" & FileName) End If – Gonzalo
Nov 14 '18 at 18:13
I really liked the idea about adding the
Dircommand. I copied and pasted your code to test it. The Dir command is not finding the file – I guess that there's an issue with the route – so I added the following code to make sure i'm pointing to the right file. However it's still not working right :-( Dim path As String Dim FileName As String path = ThisWorkbook.path FileName = "filename.pdf" If Dir(path & FileName) <> vbNullString Then Kill (path & "/" & FileName) End If – Gonzalo
Nov 14 '18 at 18:13
I found that in a Mac I have to replace the slashes ("/") by colons (":") inside
Filename.path. The macro always creates a new PDF or it overwrites the existing file. The Kill Filenamenever works. I don't understand why the same code creates a new file in the destination folder but can't find that file if it already exists. any help? thnx– Gonzalo
Nov 14 '18 at 22:20
I found that in a Mac I have to replace the slashes ("/") by colons (":") inside
Filename.path. The macro always creates a new PDF or it overwrites the existing file. The Kill Filenamenever works. I don't understand why the same code creates a new file in the destination folder but can't find that file if it already exists. any help? thnx– Gonzalo
Nov 14 '18 at 22:20
add a comment |
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.
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%2f53270930%2fvba-export-excel-to-pdf%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
So add another bit of code to rename the pdf.pdf after it has been exported...
– Solar Mike
Nov 12 '18 at 22:24
I've only ever used Windows, but can you start a filepath with a forward slash like that on a Mac?
– dwirony
Nov 12 '18 at 22:31
@dwirony the forward slash works fine in Mac. The file "PDF.pdf" was created manually before the macro was run – while "Filename.pdf" doesn't exist. Is there anything in the Document properties that could be causing the error?
– Gonzalo
Nov 12 '18 at 22:52
Are you sure that it is not just that filename.pdf is open somewhere?
– RichardBJ
Nov 12 '18 at 23:20
@RichardBJ initially I also thought about that. I checked it and all files are closed
– Gonzalo
Nov 12 '18 at 23:26