VB code to save as PDF with a changeable file name

[ColWerner]

Hi,

I've written a macro to send an excel sheet to save as a PDF. I've got a specific (changable) name that I'd like it to save as but I can't quite work out what command to give it so that it pastes the name (which I've copied in excel in the macro - called "Filename_Paste") as the new file name.

For example my PDF saves onto my desktop as "Filename_Paste.PDF" whereas I'd like to save as "Simon and Betty Anderson 3-6-2020" which is what I've just copied in the macro using Filename_Paste as a named range.

I'm also trying to work out how would I get the file to save on the users desktop, at the moment it works for my desktop but this will not work when I hand it to one of my colleagues machines.

Code is as follows:

Sub Macro4()
'
' Macro4 Macro
'
'
    Range("Filename_Copy").Select
    Selection.Copy
    Range("Filename_Paste").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Filename_Paste").Select
    Application.CutCopyMode = False
     
    Selection.Copy
    Application.CutCopyMode = False
    ChDir "C:\Users\jb\Desktop"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\jb\Desktop" & "Filename_Paste", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Range("A1").Select
End Sub

Any help would be appreciated!

Col

[jdc2000]

Methods to get path to user desktop:

http://learnexcelmacro.com/wp/2012/1...h-excel-macro/

To change the name of the saved file, replace the Filename_Paste with the actual name that you want. Where is the "Simon and Betty Anderson 3-6-2020" information stored?

[ColWerner]

Methods to get path to user desktop:

http://learnexcelmacro.com/wp/2012/1...h-excel-macro/

To change the name of the saved file, replace the Filename_Paste with the actual name that you want. Where is the "Simon and Betty Anderson 3-6-2020" information stored?

Thanks "Simon and Betty Anderson 3-6-2020" is stored in the named range called "Filename_Paste"

[jdc2000]

Where in the Range would find it? Is it in a cell, and, if so, how would you locate that cell?
Once you know where the data is located, you can change the code to use that data as the file name.

[ColWerner]

Yes is in a cell, cell D43 and I named that cell as a named range "Filename_Paste". The actual data that sits in that cell is "Simon and Betty Anderson 3-6-2020" but that will change every time I hit the Macro.

I'm just confused on how to apply the code to my situation at hand..

[jdc2000]

Possibly useful example:

Sub Macro4()
'
' Macro4 Macro
'
'
    Dim strFileName as String
    strFileName = CStr(ActiveSheet.Cells(4, 43))

    Range("Filename_Copy").Select
    Selection.Copy
    Range("Filename_Paste").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Filename_Paste").Select
    Application.CutCopyMode = False
     
    Selection.Copy
    Application.CutCopyMode = False
    ChDir "C:\Users\jb\Desktop"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\jb\Desktop" & strFileName, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Range("A1").Select
End Sub

[MarkvS]

Is this similar as my problem? I use MS Exel v16.94 (Dutch) on a MacBook. And while the macro works perfectly with the variable 'Myname', the Mac doesn't. And also it seems I cannot change the directory/map in which the alternative name (Member-ID-XXXX) to be placed.

FolderPath = Application.ActiveWorkbook.Path
Myname = FolderPath & Application.PathSeparator & "Member-ID-" & MemberID & ".pdf"
If Dir(Bestandsnaam) <> "" Then
MsgBox ("The File " & Myname & " already exists")
GoTo Einde
Else
Range("A1:O44").Select
If Left(Application.OperatingSystem, 3) = "Mac" Then
ChDir (FolderPath)
Application.DefaultFilePath = FolderPath
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="Member-ID-XXXX", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=1, OpenAfterPublish:=True
MsgBox ("This score form is saved as 'Member-ID-XXXX' somewhere on your Mac. Find it and replace the XXXX by the proper Member-ID")
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Myname, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=1, OpenAfterPublish:=True
End If
End If

[MarkvS]

Little correction: This part of my macro works perfectly in Windows. On my Mac it gives error -1004 and stops if I use the variable 'Myname' in the part 'FileName:='. If I use a name between quotes, like "Member-ID" (or "Member-ID.pdf") it works okay, but than I cannot state the map/directory on the mac where to place that file

[Zvoni]

Check if correct Directory-Separators are used.
On Mac it should be forward-Slash.
And you have nonsense in your third line
it should be

If Dir(myname) <> "" Then
        MsgBox ("The File " & myname & " already exists")

Not "Bestandsnaam"

Debug FolderPath and MyName

Sub Export()
    FolderPath = Application.ActiveWorkbook.Path
    Debug.Print FolderPath  'HERE
    MyName = FolderPath & Application.PathSeparator & "Member-ID-" & MemberID & ".pdf"
    Debug.Print MyName  'HERE
    If Dir(myname) <> "" Then
        MsgBox ("The File " & myname & " already exists")
        'GoTo Einde
    Else
        Range("A1:O44").Select
        If Left(Application.OperatingSystem, 3) = "Mac" Then
            ChDir (folderpath)
            Application.DefaultFilePath = folderpath
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Member-ID-XXXX", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=1, OpenAfterPublish:=True
            MsgBox ("This score form is saved as 'Member-ID-XXXX' somewhere on your Mac. Find it and replace the XXXX by the proper Member-ID")
        Else
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myname, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=1, OpenAfterPublish:=True
        End If
    End If
End Sub

[MarkvS]

I seem to have a problem with an Excel-Macro in a sheet I want to use on Windows and Mac. The problem only occurs on my Mac (MS Excel v16.94, Dutch).
It seems that the Excel-macro on my Mac cannot work with a variable name in the “ActiveSheet.ExportAsFixedFormat”-line in the specification of Filename:=Myname. It gives the error -1004. If I use a name between quotes, like Filename:="Member-ID" (or "Member-ID.pdf") it works okay.
On my Windows-PC (Excel 365) it works perfectly.

So a made a difference when using Mac or Windows Operating system.
On my Mac it now places the file “Member-ID-XXXX” in a map/directory that I cannot point out, despite my attempt to change the DefaultFilePath.
(and of course the variables are properly defined)

FolderPath = Application.ActiveWorkbook.Path
Myname = FolderPath & Application.PathSeparator & "Member-ID-" & MemberID & ".pdf"
If Dir(Bestandsnaam) <> "" Then
MsgBox ("The File " & Myname & " already exists")
GoTo End
Else
Range("A1:O44").Select
If Left(Application.OperatingSystem, 3) = "Mac" Then
ChDir (FolderPath)
Application.DefaultFilePath = FolderPath
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="Member-ID-XXXX", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=1, OpenAfterPublish:=True
MsgBox ("This score form is saved as 'Member-ID-XXXX' somewhere on your Mac. Find it and replace the XXXX by the proper Member-ID")
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Myname, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=1, OpenAfterPublish:=True
End If
End If

[Zvoni]

Don't crosspost, if you've already posted your problem

https://www.vbforums.com/showthread....=1#post5670598

[MarkvS]

Thanx. I forgot to replace the name 'bestandsnaam' by 'Myname'. That is corrected now.
In the names the / - sign is used on my Mac.

[MarkvS]

sorry. I'm new here and at first I couldn't find a way to post a new thread. If you can, you may delete one of my posts.

[Zvoni]

Ahh....wait a second:
Where is
Application.ActiveWorkbook.Path pointing to on a Mac?
Maybe it points to a Write-restricted location?
Have you tried the Home-Directory?

The only way your "generic" approach (FileName:=MyName) might fail is either invalid FileName (incl. Path) or Write-restriction

[MarkvS]

With this code / is used on my Mac and \ is uses on my windows-pc.
And at my last try also the use of Filename:="Member-ID-XXXX" i.s.o. Filename:=Myname doesn't work anymore

[MarkvS]

It shouldn't be write-restricted. But if it is: how can I solve that problem? Or let the macro write to my desktop (on the mac)?

[Zvoni]

It shouldn't be write-restricted. But if it is: how can I solve that problem? Or let the macro write to my desktop (on the mac)?

I would at least try to export to Desktop first, and to Home-Directory second (even if the path is hardcoded)

Never worked with a Mac.

Can you try a
"Debug.Print Environ("USER")"
and look where it points to?
And then maybe construct an Export-Path from there

[MarkvS]

In case for an export to the desktop, I must be able to set the path in the following command:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="Member-ID-XXXX", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, From:=1, To:=1, OpenAfterPublish:=True

And that is just my problem: I can't set a path or Filename.

[Zvoni]

Hmmmmm.....
https://stackoverflow.com/questions/...-mac-excel-vba

Look at both answers.
The first Answer requires you to explicitely change the Directory to your Target-Folder
The second answer says, you can't change Target, und have to use a specific Folder.

Try it

[MarkvS]

Tried it. I keep getting the error pointing at the command: ActiveSheet.ExportAsFixedFormat .....
Now the macro doesn't even export the sheet to /Users/[current user]/Library/Group Containers/UBF8T346G9.Office