I have code in a workbook to export a hidden sheet to PDF by activating a macro. Users have reported the macro fails. I've pinpointed that the macro fails unless the user has previously manually "saved as" a sheet to PDF, which seems to wake up the macro.
My code is
Sub PRINTPDF()
Sheets("Hardcopy").Visible = True
Sheets("Hardcopy").Activate
'Code from Contextures.com
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
On Error GoTo 0
Dim mainfilename As String
mainfilename = Range("FILENAME")
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")
'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"
'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")
'create default name for savng file
strFile = mainfilename & "_" & strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=strFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
Sheets("Hardcopy").Visible = False
Sheets("DataEntry").Activate
Range("B8").Select
End Sub
Is this an error in my code, or an Excel bug? Appreciate I have no error handling (any guidance on this would also be appreciated). Excel version is latest and greatest Office 365, 32 bit.
Bookmarks