VBA possible string issue

[Access Beginner]

Hello,


I've have code that creates a PDF from my workbook and this saves the PDF to a folder based on a cell (Menu Sheet A5). This cell is change via a drop down list, so I can change the location of where the PDF is saved to.

This issue I have, is someone has change the structure of our folders and the path is now a lot longer.


Path where it does work: Y:\ABC\FCIAIF\INDIGREM\Pndigeniis\Lusiness Puaaort ACoordiABCion\6. ABCIONAL PNDIGENIIS STRATEGIES\0. PNDIGENIIS MELVICING SPRATEGY (QWE)\

Path where it doesn't work: Y:\ABC\FCIAIF\INDIGREM\Pndigeniis\Lusiness Puaaort ACoordiABCion\6. ABCIONAL PNDIGENIIS STRATEGIES\0. PNDIGENIIS MELVICING SPRATEGY (QWE)\5. (QWE) REPORTING\2018 QWE DASHBOARD\Zone QWE Dashboards\Central HJN Badney\



Not to sure why this doesn't work, I initially thought it may have to do with a character limit in VBA or excel, but the character length of the longest path above is 216.

Any ideas?

Sub PDFPrint()

Dim ReportPath As Variant
    Dim ReportName As Variant
    Dim ToPrint As Variant
  
   
    Set ReportPath = Sheets("Menu").Range("A5")
    Set ReportName = Sheets("Menu").Range("A3")
    Set ToPrint = Sheets("Menu").Range("A4")
  
    
   Worksheets("ISS_Charts").Activate
    On Error Resume Next
    With ActiveSheet
    .PageSetup.PrintArea = ToPrint
    .ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ReportPath & ReportName & ".PDF", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    On Error Resume Next
    
    End With
MsgBox "                                  PDF document:" & vbCrLf & "File Name:    " _
& ReportName & vbCrLf & vbCrLf & "                                  Has been created and has been saved to:" _
& vbCrLf & vbCrLf & "File Location:    " & ReportPath
End Sub

 

[Yongle]

I initially thought it may have to do with a character limit in VBA or excel, but the character length of the longest path above is 216

Excel cannot handle long filepaths - the maximum is 218 characters INCLUDING filename
see this link https://support.microsoft.com/en-gb...en-or-save-a-file-in-microsoft-excel-filename

Cause - in Microsoft's own words ...

This error message occurs when you save or open a file if the path to the file (including the file name) exceeds 218 characters. This limitation includes three characters representing the drive, the characters in folder names, the backslash character between folders, and the characters in the file name.


This behavior is based on a 256-character limitation in Excel for creating links to another file. This limit of 218 characters for the path name is based on the following:

• Up to 31 characters in a sheet name.
• Apostrophes and brackets used to denote the workbook name.
• An exclamation point.
• A cell reference.

For example, the path for a file might resemple the following:

'c:\excel\personal\...\[my workbook.xls]up_to_31_char_sheetname'!$A$1

Note This behavior will also occur if there is a square bracket in the path.