OpenAfterPublish and OpenAfterExport Not Working In Excel VBA

[Hunger]

Hi, new poster here. Extremely inexperienced with VBA, no prior coding proficiency but I find myself needing to create a few bespoke tools for my work so I am learning.

I am using PK's Export all Excel worksheets in separate PDF files macro, which can be found here - Export all Excel Worksheets in separate PDF files - PK: An Excel Expert.

I have a workbook which is composed of about 20 worksheets; each worksheet is set up as a invoice for an individual client. There is also a single worksheet named CONTROL which lists all all clients and their details, and has fields for me to input their individual fees for this month, plus the date and some other info that is universal to all the invoices for this month. The individual invoice worksheets draw info from the CONTROL sheet via VLOOKUPs.

When the Export macro is run it exports all the worksheets into a single folder as individual PDFs, so I can input the totals for each client each month on the CONTROL worksheet and press one button to create the whole batch of PDFs.

I have added a line to hide the CONTROL worksheet using Worksheets("CONTROL").Visible = True before the macro exports the files, and then a corresponding second line after the important bit happens to convert the value back to False. On its own this creates a runtime error (since it can't find the hidden CONTROL worksheet to export), but this is solved with the On Error Resume Next line.

The macro works perfectly at that. Code is as follows:


Sub Multiple_PDF_Print()

Dim Folder_Path As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select The Folder Path"

If .Show = -1 Then Folder_Path = .SelectedItems(1)

End With

Worksheets("CONTROL").Visible = False

Dim sh As Worksheet

On Error Resume Next

If sh.Visible = True Then

For Each sh In ActiveWorkbook.Worksheets

sh.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & ".pdf"

Next

Worksheets("CONTROL").Visible = True

MsgBox "PDFs have been exported to the selected folder."

End If

End Sub


I was hoping to put the cherry on top and have it open all the PDFs so I can check them manually before sending them out, and a quick Google search leads me to assume this should be a simple matter of using either the line OpenAfterExport:=True or OpenAfterPublish:=True, which I assume should go toward the end of the code. However, I cannot for the life of me get this to work.

Firstly, when I input the line with this exact formatting it turns red and tells me there is a compile error. It doesn't like the combination of the colon and = sign positioned adjacent to each other. If I write it as OpenAfterExport = True then the word True turns blue, like the other working functions elsewhere in the code, which appears to indicate that this is the correct formatting.

Secondly I tried both variants (Export and Publish) because I came across another forum-posted solution somewhere that worked for somebody when he used the other command, but this doesn't work for me.

Thirdly, I have tried sticking both variants absolutely all over the place in the code, but it does not matter where I put them they do not work. At this point I believe I have tried every combination of formats, commands and locations but the PDFs never open after the macro runs. Adobe Reader Touch is the default app I have to open the PDFs, I have reinstalled it.

Apologies for my inexperience, I recognise that I'm pretty out of my depth when it comes to engaging with coding matters, however I am learning. So far I have managed to put together a few working macros by copying code from different places, cobbling together elements to make what I need and fiddling around until it works, but this is the first time I have come across something I can't get working at all - and it seems so straightforward because its just one simple line.

I might be missing something fundamental in my understanding here, but could somebody please explain what I am doing wrong or why the PDFs do not open? I have seen a similar post on another forum from a MAC user, but it apparently works fine on his Windows machine.

I am using Office 365 on Windows 10. Local copy of Excel.

Thank you for your help.

 

[John_w]

Welcome to MrExcel forums.

You've missed something simple and will probably kick yourself...

OpenAfterPublish is parameter of the ExportAsFixedFormat method, not a separate keyword or statement.

On its own this creates a runtime error (since it can't find the hidden CONTROL worksheet to export), but this is solved with the On Error Resume Next line.

The error occurs because you don't assign the 'sh' worksheet object variable to anything, therefore it's Nothing and If sh.Visible = True Then fails.

You can do it without the On Error Resume Next, or hiding the CONTROL sheet, like this:

    Dim sh As Worksheet    
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "CONTROL" Then
            sh.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & ".pdf", OpenAfterPublish:=True
        End If
    Next

Or if CONTROL is the first sheet:

    Dim i As Long
    For i = 2 To ActiveWorkbook.Worksheets.Count
        ActiveWorkbook.Worksheets(i).ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & ".pdf", OpenAfterPublish:=True
    Next

 

[Hunger]

Hi John_w, thank you so much for your response to my question. Ah, I thought I'd tried placing the command everywhere, but I didn't think to try it there! Your help is greatly appreciated.

I have added the OpenAfterPublish bit in the right place, and it does now open Adobe when the macro runs, however the exported invoices do not stay open for more than a second. Adobe opens each invoice one at a time in rapid succession as the macro does its work, but as soon as the the next one is exported (which happens almost instantaneously) it closes the current one and opens the next.

I think this might be because I am using Adobe Touch Reader though... I can only normally open one PDF at a time, so I assume the macro works but the reader shuts them down. I will upgrade to Acrobat and that should hopefully enable me to do what I need to do.

Thanks once again for your help, I now have what I need to get the process working correctly! Very grateful to you

 

[Hunger]

Just to follow this up, I downloaded Adobe Acrobat DC and everything now works perfectly, just as intended.

Thanks once again for your help, this will massively reduce my workload.