Update: Sample of the excel data I'm trying to pdf and the pdf file that is usually created below Excel - https://i.sstatic.net/PlsBQ.jpg PDF - https://i.sstatic.net/8DEHh.jpg
The problem seems to be that the process isn't detecting any content in the worksheet - any advice on how I can 'activate' the content?
-
I have a VBA macro that converts an excel report to PDF files - I want to replicate the code across several workbooks but when I do it works in some workbook and not others.
The code is below - I update the four Const values in each to the relevant cell / value for each workbook and I've checked that the same references are selected in each workbook.
When I run the code it loops through, changing the index cell and produces a PDF with the new data. In one of the workbooks though the PDFs are not being created, I have stepped through the code and it creates the postscript file but does not convert it to PDF.
Public Sub ExportGraphs()
Const indexCell = "B55"
Const totalCell = "D55"
Const authorityName = "B5"
Const fileName = "Civic cultural and community venues performance indicator standings report 2013-14 - "
Application.ScreenUpdating = False
Application.ShowWindowsInTaskbar = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim i As Integer
Dim awb As Workbook
Set awb = ThisWorkbook
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
For i = 1 To awb.Worksheets("PIN").Range(totalCell).Value
awb.Worksheets("PIN").Range(indexCell).Value = i
Dim strGraphLoc, strPIN As String
strPIN = awb.Sheets("PIN").Range(authorityName).Value & " " & awb.Worksheets("PIN").Range("B6").Value
strGraphLoc = awb.Path & "/"
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = strGraphLoc & fileName & strPIN & ".ps"
PDFFileName = strGraphLoc & fileName & strPIN & ".pdf"
'Print the Excel range to the postscript file
awb.Sheets("PIN").PrintOut copies:=1, from:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, prtofilename:=PSFileName
'Convert the postscript file to .pdf
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill PSFileName
Kill strGraphLoc & fileName & strPIN & ".log"
Next i
Set myPDF = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.ShowWindowsInTaskbar = True
Application.EnableEvents = True
End Sub
Any help would be greatly appreciated, I can't for the life of me work out why it works in some and not others.
In the workbook I am having trouble with, a 0kb postscript file is created and a text file which states:
%%[ Warning: Empty job. No PDF file produced. ] %%
After tearing my hair out and failing to find a solution for almost 6 months my colleague has just sussed it - the file name of the PDF we were trying to create was too long, we shortened and it works fine now!
I'm going for a lie-down