Excel 2010 VBA: Save file using value from cell to determine path and filename

Question

I am trying to write some code that will save several tabs as a pdf document in folder specified by files within excell. I would like for cells within the document to dictate where this file is saved. I am not sure if this is possibly, but if it is any help would be good! I am currently getting a Run-time error '1004' during the save process of my code.

And yes, I do have the folders created that are being referenced.

Sub asdf()

Dim Fname As String
Dim Fpath As String
Dim YrMth As String

Fname = Sheets("Sheet1").Range("A1").Text

YrMth = Sheets("Sheet1").Range("A2").Text & "\" & Sheets("Sheet1").Range("A3").Text

Fpath = "C:\Documents and Settings\My Documents\" & YrMth & "\Group\" & Fname & ".pdf"

ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet4")).Select

Application.DisplayAlerts = False

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=Fpath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

End Sub

Jon Crowell · Accepted Answer · 2013-06-17 21:25:00Z

1

Your code works for me, but not with the path you've specified.

Declare a new string variable:

dim myDocsPath as String

Get the path using:

myDocsPath = Environ$("USERPROFILE") & "\My Documents\"

and then change your definition for Fpath to:

Fpath = myDocsPath & YrMth & "\Group\" & Fname & ".pdf"

If I change the end of myDocsPath to & "\My foo Documents\" I get the same 1004 error you are getting.

edited Jun 17, 2013 at 21:25

answered Jun 17, 2013 at 20:50

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2437803 Over a year ago

Thank you for your insights Head of Catering!

Swinki Trzy · Accepted Answer · 2017-06-21 17:21:16Z

1

Try replace line in your code

Fpath = "C:\Documents and Settings\My Documents\" & YrMth & "\Group\" & Fname & ".pdf"

with

Dim WshShell As Object
Dim MyDocsFolder As String
Set WshShell = CreateObject("WScript.Shell")
MyDocsFolder = WshShell.SpecialFolders("MyDocuments") & "\"

Fpath = MyDocsFolder & YrMth & "\Group\" & Fname & ".pdf"

Edit: The core of this solution is in line:

MyDocsFolder = WshShell.SpecialFolders("MyDocuments") & "\"

which returns system path to My Documents, irrespectively from local system settings like language or nonstandard location of My Documents folders. Then it adds a backslash at the end.

It is more elegant (and the code becomes more portable) if you ask system about special folders than hardcode such data in your script.

More on Windows special folders in VBA you can find https://www.rondebruin.nl/win/s3/win027.htm

edited Jun 21, 2017 at 17:21

answered Jun 21, 2017 at 14:50

Swinki Trzy

112 bronze badges

2 Comments

GrumpyCrouton Over a year ago

Telling someone to change their code without explaining why their code is wrong and your code is right, isn't a very useful answer for future readers.

Swinki Trzy Over a year ago

You're right. It seemed to me to be obvious and self-documented. So I added an explanation which should shed some light.

Collectives™ on Stack Overflow

Excel 2010 VBA: Save file using value from cell to determine path and filename

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related