Solved VBA Code to save as a PDF

July 10, 2019 at 11:47:29
Specs: Windows 10 Enterprise
Hello,
I am trying to figure out why my code is not working the way I want to. Everything works fine except the location of where the file is saving. I already specify where I want the file to go and tried different things and it keeps saving it in the "Documents" folder.
Do you know why is this happening? Thanks

This is my script:

Sub SavePDF()
'
' SavePDF Macro

'Set path to Mechanics Inspection Log
fPath = "K:\Manufacturing\Manufacturing Engineering\3. Tools"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("B7").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub

message edited by Oscartm4


See More: VBA Code to save as a PDF

Report •

#1
July 10, 2019 at 13:20:17
✔ Best Answer
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("B7").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

Where is the fPath in this command? Are you looking for
Filename:=fPath & "\" & Range("B7").Value
?

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#2
July 11, 2019 at 05:27:51
I am a beginner with VBA, but it worked perfectly just by adding that.
Thanks a lot!!

Report •

#3
July 11, 2019 at 06:26:15
But do you know why it worked?

IOW, do you know why just including fPath = "K:\Manufacturing\Manufacturing Engineering\3. Tools" in your code didn't work?


message edited by DerbyDad03


Report •

Related Solutions

#4
July 11, 2019 at 07:21:10
Because I was just defining the path but not actually implementing it in the code, right?

Report •

#5
July 11, 2019 at 09:16:18
Right. To put it another way, you assigned a text string (in this case, the path) to a variable (fPath) and then didn't include the variable as part of the Filename argument.

You could also have eliminated the variable and used:

Filename:="K:\Manufacturing\Manufacturing Engineering\3. Tools\" & Range("B7").Value

Good job!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#6
July 11, 2019 at 10:00:00
If we're going down that route, he could have also gone with:
fPath = "K:\Manufacturing\Manufacturing Engineering\3. Tools\" & Range("B7").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ 
OpenAfterPublish:=True

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#7
July 11, 2019 at 13:15:00
But I wouldn't use a variable name of fPath in that case. ;-)

There are varying opinions as to whether or not the filename and extension (fubar.suk) is part of the filepath.

I choose that it is not.

message edited by DerbyDad03


Report •

#8
July 11, 2019 at 16:41:19
It is, and Wikipedia agrees. A path is a map to a single resource. It can be relative or absolute (or a URI), but that path includes the resource's name.

If you're asked to locate someone and hand them a package, you don't throw the package into their room as you walk down the hall, do you?

How To Ask Questions The Smart Way


Report •

#9
July 11, 2019 at 18:15:40
Yet even Wikipedia appears to be wishy-washy on that point.

"A path, the general form of the name of a file or directory, specifies a unique location in a file system. A path points to a file system location by following the directory tree hierarchy expressed in a string of characters in which path components, separated by a delimiting character, represent each directory."

The first sentence includes the word "file" but everything after that uses only the words "location" or "directory". The last part of the second sentence reads "a string of characters in which path components...represent each directory." Based on that wording, the components of the path only represent locations/folders/directories, not files.

In addition, Excel VBA has 2 properties related to this: Path and FullName

Me.Path will not return the filename, just the directory hierarchy. Me.FullName will return the directory hierarchy along with the file name.

If I am asked to locate someone and hand them a package, I will the follow the path into their room and all the way up to them. At that point I will be at the end of the path. I do not consider the package (file) to be part of the path to the person (final directory).

No biggy either way. My point was that I wouldn't use fPath as the variable name if the contents of the variable was going to include the file name. I'm pretty sure that the majority of code that people write call out the "path" and the "filename" separately (such as the OP did) typically with something like fPath & fName.

message edited by DerbyDad03


Report •

#10
July 21, 2019 at 22:26:23
DerbyDad03: The first sentence includes the word "file" but everything after that uses only the words "location" or "directory".
I'm not saying a path does not include directory information. It does, just sometimes implicitly. It just doesn't end before you reach the resource. Much like how directions to your house wouldn't stop at some intersection just before your house, paths don't end until you reach the resource in question. Because that's what paths are. They're a list of directions for the system to follow. Hence the name.

DerbyDad03: Based on that wording, the components of the path only represent locations/folders/directories, not files.
The quote you chose literally has, "name of a file," 11 words in. Next time maybe don't include evidence that counters your claim?

DerbyDad03: Me.Path will not return the filename, just the directory hierarchy. Me.FullName will return the directory hierarchy along with the file name.
Look, I love the work the VB* teams did, I really do. They made a (some would say overly) flexible scripting environment that gives basically full access to the most complex application suite most will ever run while being constrained by decisions and bugs found in Lotus 1-2-3, a program released in 1983. That's not easy. But they're also a team that calls directories folders, and developers from the shell team, the team that owns the term "folders" in Microsoft says that terminology is incorrect. Plus they're an app team. Their apps are huge, but they don't exactly have dominion over operating systems. The teams that do operating systems say a path ends with the resource you're trying to access. What I'm saying is the Office / VB* teams are known to be wrong about these things, and more authoritative sources don't back them up. Me.FullName is fine and unambiguous. Me.Path really should be Me.ParentPath.

DerbyDad03: I do not consider the package (file) to be part of the path to the person (final directory).
Nor should you, because the file in this metaphor is the person. The package is the data that goes in the file. (The package is Twinkies. The person is me. I love Twinkies.)

How To Ask Questions The Smart Way


Report •

#11
July 22, 2019 at 04:06:52
I'll make this quick.

Next time maybe don't include evidence that counters your claim?

Next time maybe don't ignore the rest of what I wrote.

Or

Next time maybe read what I wrote more carefully.

(I'm not sure which one fits, only you do.)

Moving on.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#12
July 22, 2019 at 09:25:36
Eleven words in. Eleven.

I'm not sure why the idea that a file's path includes directories confuses you. You didn't seem to start there in the conversation. Much like how any other set of directions isn't going to focus on the end goal, but it will include the end goal somewhere, and is instead composed of the steps required to get to your destination.

How To Ask Questions The Smart Way


Report •

#13
July 22, 2019 at 11:32:19
(I really am moving on after this...)

It appears that you misinterpreted the comments I made following the quote that I copied from the Wikipedia page that you offered. The words you say that I used to proof one point were actually written to support a totally different point. That is not uncommon in a forum such as this. The inability of a participant to interject as soon as the discussion heads in the wrong direction usually results in a disconnect that would probably have been avoided in a face-to-face conversation. There's no chance for a simple (friendly) "Hold on...that's not what I meant...let's get back on track."

How about we try that now?

Earlier in this thread I said: There are varying opinions as to whether or not the filename and extension is part of the filepath.

To which you replied: It is, and Wikipedia agrees.

To which I replied: Yet even Wikipedia appears to be wishy-washy on that point.

That last comment (wishy-washy Wikipedia) is the point that I was supporting in the paragraph that followed the quote. My goal was to point out the inconsistencies in that quote. Broken down into it's 2 sentences it looks like this. Obviously, the italics are mine.

1 - "A path, the general form of the name of a file or directory, specifies a unique location in a file system. "

2 - "A path points to a file system location by following the directory tree hierarchy expressed in a string of characters in which path components, separated by a delimiting character, represent each directory."

I'm sure that you can see that those 2 sentences tell 2 different stories, especially if we're trying to nail down the definition of a specific word. The first says that the path includes the file name while the second says that the path components represent each directory. The "path" is made up of "path components" and the 2nd sentence states - rightly or wrongly - that the components represent directories.

Yes, I acknowledged the fact that the Wiki definition included the word "file" but it wasn't mistakenly used to support the claim you think I was trying to make. It was my way of pointing out that the definition is either not as clear cut as some might like or perhaps just not understood as clearly as some might like. My point was that even the Wikipedia page is not as definitive as we would like.

We could both find numerous sites that only refer to directories when discussing the meaning of "path" just like we could both find numerous sites that will include mention of the filename. We could then argue about the words used and what they exactly mean, but that would be best done over a cold beverage as opposed to over the internet. ;-)

message edited by DerbyDad03


Report •

Ask Question