VBA Code won't run correctly if sheet hidden

[tlc53]

Hi,

I have the following module VBA code which saves a report located on a different sheet (sheet80) to a PDF file.

I would like to have sheet80 hidden as it's all formulated and the report does not need editing. However, when I run the code on a different sheet, it only works correctly if sheet80 is not hidden, otherwise it returns "Could not create PDF file". Not sure why this would matter. Any ideas please?

Sub PDFActiveSheet()
'www.contextures.com
'for Excel 2010 and later
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
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = Sheet80


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & ""


strName = wsA.Range("A1").Value _
          & " - " & wsA.Range("A2").Value _
          & " " & Format(wsA.Range("A3"), "dd.mm.yy")


'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile


'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

 

[Trebor76]

Hi tlc53,

Have a look at the following where if the sheet is hidden I set the newly created blnWasSheetHidden Boolean (True / False) variable to True, unhide the sheet, create the PDF and then re-hide the sheet. If it was already visible nothing different happens.

Hope that helps,

Robert

Sub PDFActiveSheet()
 'www.contextures.com
 'for Excel 2010 and later
 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
 Dim myFile As Variant
 Dim blnWasSheetHidden As Boolean
 On Error GoTo errHandler

 Set wbA = ActiveWorkbook
 Set wsA = Sheet80

 'get active workbook folder, if saved
 strPath = wbA.Path
 If strPath = "" Then
 strPath = Application.DefaultFilePath
 End If
 strPath = strPath & ""

 strName = wsA.Range("A1").Value _
 & " - " & wsA.Range("A2").Value _
 & " " & Format(wsA.Range("A3"), "dd.mm.yy")

 'create default name for savng file
 strFile = strName & ".pdf"
 strPathFile = strPath & strFile

 'use can enter name and
 ' select folder for file
 myFile = Application.GetSaveAsFilename _
 (InitialFileName:=strPathFile, _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")

 'export to PDF if a folder was selected
 If myFile <> "False" Then
 If wsA.Visible = xlSheetHidden Then
    wsA.Visible = xlSheetVisible
    blnWasSheetHidden = True
End If
 wsA.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:=myFile, _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=False
 If blnWasSheetHidden = True Then
    wsA.Visible = xlSheetHidden
 End If
 'confirmation message with file info
 MsgBox "PDF file has been created: " _
 & vbCrLf _
 & myFile
 End If

exitHandler:
 Exit Sub
errHandler:
 MsgBox "Could not create PDF file"
 Resume exitHandler
 End Sub

 

[tlc53]

Hi Robert,
Thank you for that! It is now saving the file. However, the default file save name has now been amended. It should be a combination of A1, A2 and A3, but before the A1 it is now putting the folder name in. Any idea why that is happening?
Thank you!
Tracy-Lee

 

[Trebor76]

However, the default file save name has now been amended.

Did this ever work as expected as I didn't touch anything to do with the generation of file name

Looking at the code I think it's because this line...

InitialFileName:=strPathFile

...should be this:

InitialFileName:=strName & ".pdf"

I have made this change to the following which works as expected for me:

Option Explicit
Sub PDFActiveSheet()
 'www.contextures.com
 'for Excel 2010 and later
 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
 Dim myFile As Variant
 Dim blnWasSheetHidden As Boolean
 On Error GoTo errHandler

 Set wbA = ActiveWorkbook
 Set wsA = Sheet80

 'get active workbook folder, if saved
 strPath = wbA.Path
 If strPath = "" Then
 strPath = Application.DefaultFilePath
 End If
 strPath = strPath & ""

 strName = wsA.Range("A1").Value _
 & " - " & wsA.Range("A2").Value _
 & " " & Format(wsA.Range("A3"), "dd.mm.yy")

 'create default name for savng file
 strFile = strName & ".pdf"
 strPathFile = strPath & strFile

 'use can enter name and
 ' select folder for file
 myFile = Application.GetSaveAsFilename _
 (InitialFileName:=strName & ".pdf", _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")

 'export to PDF if a folder was selected
 If myFile <> "False" Then
 If wsA.Visible = xlSheetHidden Then
    wsA.Visible = xlSheetVisible
    blnWasSheetHidden = True
End If
 wsA.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:=myFile, _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=False
 If blnWasSheetHidden = True Then
    wsA.Visible = xlSheetHidden
 End If
 'confirmation message with file info
 MsgBox "PDF file has been created: " _
 & vbCrLf _
 & myFile
 End If

exitHandler:
 Exit Sub
errHandler:
 MsgBox "Could not create PDF file"
 Resume exitHandler
 End Sub

Regards,

Robert

 

[tlc53]

It didn't appear to be an issue before. Not sure why it only became apparent then.
I have added your new amended code and it now works perfectly. Thanks very much for helping me out!

Have a good day!

 

[Trebor76]

You're welcome. Thanks for the thanks and the like