I have a macro that refreshes some pivot charts in a page of data and then exports the data into a PDF format:
This works fine for colleagues in Europe and the US, but some users in China are getting PDFs where the data is scrambled, and the formatting and pivot charts are lost/not displaying properly (or at all). I assume this is due to language settings but I have no idea how to mitigate for this, any ideas? The PDF reader being used is Nitro Pro if that makes any difference. Thanks!
VBA Code:
Sub Export_PDFReport()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total").CurrentPage = "(All)"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total").PivotItems("0").Visible = True
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total").CurrentPage = "(All)"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total").PivotItems("0").Visible = False
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotFields("Countries").PivotItems("(blank)").Visible = False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Countries").PivotItems(Range("Pivot_Info").value).Visible = False
Dim ReportDate As String
ReportDate = Format(Date, "dd-mmmm-yyyy")
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Printed on :" & ReportDate
.CenterFooter = "Confidential"
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintArea = Range("ReportPrintArea").Address(External:=True)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
Application.PrintCommunication = True
ClientName = Range("ClientName").value
ClientNumber= Range("ClientNumber").value
SaveFolder = Application.ActiveWorkbook.Path
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
SaveFolder & "/Summary Report_" & ClientName & "_" & ClientNumber & "_" & ReportDate & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
ReportName = "Summary Report_" & ClientName & "_" & ClientNumber & "_" & ReportDate & ".pdf"
MsgBox "Report created and saved to " & SaveFolder & " as " & ReportName, vbOKOnly, "PDF created!"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
This works fine for colleagues in Europe and the US, but some users in China are getting PDFs where the data is scrambled, and the formatting and pivot charts are lost/not displaying properly (or at all). I assume this is due to language settings but I have no idea how to mitigate for this, any ideas? The PDF reader being used is Nitro Pro if that makes any difference. Thanks!