Export to PDF macro fails on Excel Office 365, not on Pro Plus 2019

[ddewilt]

Hi,

I have a button inside Excel workbook which exports certain pages to PDF with one click on a button.
The Excel version I am using is Professional Pro Plus 2019 and my customers version is the same build, but an Office 365 version.

If I hit the button in my version it works, but if my customer does on his computer it gives an Error 52: "Bad file name or number".

More information:
- The file was in a folder in My Documents when tested
- Macro's are enabled at Trust Center
- My customer and I are both working on a Windows PC.
- The code below is in a module
- What the code does is, depending your Choice (ja/nee) on a worksheet, it wil export 1 of 2 sheets to 1 PDF.

Below is the code of the macro I am using:

Sub Maak_offerte()

Dim path_ As String
    path_ = ThisWorkbook.Path & "\" & "Offertes"
With CreateObject("Scripting.FileSystemObject")
    If Not .FolderExists(path_) Then .CreateFolder (path_)
End With



With Worksheets("OFFERTE").PageSetup
    .LeftMargin = Application.CentimetersToPoints(0)
    .RightMargin = Application.CentimetersToPoints(0)
    .TopMargin = Application.CentimetersToPoints(0)
    .BottomMargin = Application.CentimetersToPoints(0)
    .Orientation = xlPortrait
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False

End With


With Worksheets("OHSERVICE").PageSetup
    .LeftMargin = Application.CentimetersToPoints(0)
    .RightMargin = Application.CentimetersToPoints(0)
    .TopMargin = Application.CentimetersToPoints(0)
    .BottomMargin = Application.CentimetersToPoints(0)
    .Orientation = xlPortrait
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False

End With
Application.AlertBeforeOverwriting = False
FolderName = "Offertes"
FileName1 = Worksheets("1. STARTGEGEVENS").Range("F16")
FileName2 = Worksheets("2. KLANTGEGEVENS").Range("F16")
FileName3 = Worksheets("2. KLANTGEGEVENS").Range("F14")
Worksheets("OFFERTE").Visible = True
Worksheets("OHSERVICE").Visible = True
If Range("ohservice").Value = "Ja" Then
ThisWorkbook.Sheets(Array("OFFERTE", "OHSERVICE")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & FolderName & "\" & FileName1 & " - " & FileName2 & " " & FileName3 & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Application.AlertBeforeOverwriting = True
Worksheets("OFFERTE").Visible = xlSheetHidden
Worksheets("OHSERVICE").Visible = xlSheetHidden
End If
If Range("ohservice").Value = "Nee" Then
Worksheets("OFFERTE").Visible = True
ThisWorkbook.Sheets(Array("OFFERTE")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & FolderName & "\" & FileName1 & " - " & FileName2 & " " & FileName3 & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Application.AlertBeforeOverwriting = True
Worksheets("OFFERTE").Visible = xlSheetHidden
End If
Worksheets("1. STARTGEGEVENS").Activate
End Sub

Does anybody have any idea what the problem might be and what the/a solution is?

Thanks in advance!

Greetings Daan

 

[RoryA]

Can you find out what the actual values of Filename1, Filename2 and Filename3 are when the code errors?

Also, you could just use path_ in the export lines instead of ThisWorkbook.Path & "\" & FolderName

 

[ddewilt]

Can you find out what the actual values of Filename1, Filename2 and Filename3 are when the code errors?

Also, you could just use path_ in the export lines instead of ThisWorkbook.Path & "\" & FolderName

The output of:

FileName1 = MU2021-0643 (number auto increases)
FileName2 = Text
FileName3 = Text

Do I get it right what you're saying about using path_ the code would be:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path_ & "\" & FolderName & "\" & FileName1 & " - " & FileName2 & " " & FileName3 & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True ?

 

[RoryA]

No, path already includes FolderName, so it would be Filename:=path_ & "\" & FileName1 & " - " & FileName2 & " " & FileName3 & ".pdf".

Re the error, I'd need to know the actual values of the variables. Perhaps the total file path and name exceeds 255 characters, which can be a problem sometimes.

 

[ddewilt]

Thanks for the reply! I will check it out with mt customer! I'll get back to it, thaanks!!

 

[ddewilt]

I found the problem!!
This piece of code below was the pain in the ***... I don't know why, but it works like a charm right now!

Thanks for the effords! I really appreciate it!

Greetings!

 

[smozgur]

This piece of code below was the pain in the ***... I don't know why, but it works like a charm right now!

Glad to hear you got the solution.

Do you mind posting the problem piece of code? Then it is perfectly fine to mark your post as the solution to help future readers.

 

[ddewilt]

Oh yeah, offcourse! I saw I somehow forgot to paste it in my previous post haha..

I removed this piece of code on the top of the snippet:

Dim path_ As String
    path_ = ThisWorkbook.Path & "\" & "Offertes"
With CreateObject("Scripting.FileSystemObject")
    If Not .FolderExists(path_) Then .CreateFolder (path_)
End With

Greeetss!

 

[smozgur]

Right, if you have the Offertes folder in the directory where the workbook resides, then you won't have any problem removing that code. So, you should have the users create that folder manually prior to using the macro.

The actual problem could be that your customer is storing their files on the cloud, I mean it is likely the OneDrive folder, so that's why Scripting.FileSystemObject throws that error as it cannot create the remote folder.
Sample: I have my workbook saved in the OneDrive folder, and the following returns the remote location instead of my computer's local path:
Debug.Print ThisWorkbook.Path => https://d.docs.live.net/.../Documents/

Because of the File collaboration setting in OneDrive, VBA retrieves the remote folder path:

If you uncheck this check box in the OneDrive settings, then it will only help when you open the file in the folder directly. However, I don't think this is what you want, because it will make OneDrive basically useless for Office files.

So, either the workbook file should be saved somewhere other than the OneDrive folders to get the local path in VBA, or that folder should be created manually in the OneDrive folder to not create it in the code - as you did.

 

[ddewilt]

Right, if you have the Offertes folder in the directory where the workbook resides, then you won't have any problem removing that code. So, you should have the users create that folder manually prior to using the macro.

The actual problem could be that your customer is storing their files on the cloud, I mean it is likely the OneDrive folder, so that's why Scripting.FileSystemObject throws that error as it cannot create the remote folder.
Sample: I have my workbook saved in the OneDrive folder, and the following returns the remote location instead of my computer's local path:
Debug.Print ThisWorkbook.Path => https://d.docs.live.net/.../Documents/

Because of the File collaboration setting in OneDrive, VBA retrieves the remote folder path:
View attachment 33932

If you uncheck this check box in the OneDrive settings, then it will only help when you open the file in the folder directly. However, I don't think this is what you want, because it will make OneDrive basically useless for Office files.

So, either the workbook file should be saved somewhere other than the OneDrive folders to get the local path in VBA, or that folder should be created manually in the OneDrive folder to not create it in the code - as you did.

Thanks for the explanation you gave here! I'll check it later, problem is solved for now. We're back to a version earlier to get a working file for now.