Selecting all sheets using personal.xlsb

[barbs706]

Hi all.

I am trying to get a script together that will select all sheets (not hidden ones) on opening of a workbook.

We have a vast amount of workbooks that date back to 2006 so I was hoping I could use the PERSONAL.XLSB instead of manually adding code to every workbook.

A lot of the workbooks have differently named sheets so the code needs to be universal so that it works regardless of sheet name/quantity.

I have managed to get the PERSONAL.XLSB to open automatically with one of our workbooks, by recording a dummy macro (personal.xlsb was missing) but now I am at a loss with what to do next!

Can anyone suggest the code? Thank you!

 

[Fishboy]

Hi all.

I am trying to get a script together that will select all sheets (not hidden ones) on opening of a workbook.

We have a vast amount of workbooks that date back to 2006 so I was hoping I could use the PERSONAL.XLSB instead of manually adding code to every workbook.

A lot of the workbooks have differently named sheets so the code needs to be universal so that it works regardless of sheet name/quantity.

I have managed to get the PERSONAL.XLSB to open automatically with one of our workbooks, by recording a dummy macro (personal.xlsb was missing) but now I am at a loss with what to do next!

Can anyone suggest the code? Thank you!

Hi barbs706, welcome to the boards.

Can you elaborate a little as to what you want to do?

Let's say you open WorkbookA, when it opens you want to select all visible sheets, but then do what?

Also, if you have a lot of workbooks that all need you to do, well, whatever it is you are trying to do, are they all in the same folder? Are they all the same file type (like .xlsx, or xls or whatever)?

 

[barbs706]

Thanks for the reply!

Yes sorry I should have explained a bit more.

Basically, I want to PDF the workbooks. I have some software that uses 'Watch folders' to monitor a directory. Once an excel file is put into the watch folder, the software automatically opens the workbook and prints to PDF.

The problem I am having is that there is no way to set Excel’s default to 'Print Entire Workbook'. So although the software prints to pdf successfully, it only prints the active sheet.

So what I was hoping is that once the pdf software opens the workbook, code instantly runs selecting all the sheets.

But to answer your questions:

No, they are all in different folders (split up into customer names)
No, the file types are either: XLSX or XLSM

Maybe there is a better alternative to using this PDF software?

 

[barbs706]

Forgot to add, this will be running on a server.

So once I create a workbook, I can save it into the correct directory on my workstation and the server automatically prints the workbook to PDF.

I would like to keep it as streamlined and without user intervention as possible. It will be used in an office environment and we have some staff members that really do not like change!

Thanks again.

 

[Fishboy]

Hi barbs,

Test out the following code in you personal.xlsb workbook. I am yet to work out a good way of automating the whole thing so currently you will need to manually run this to test it but I wanted to check that this works for you before spending any more time on it.

In theory this code should work its way through any open workbooks which are NOT the personal.xlsb one. For each of those workbooks it will "select" all visible worksheets and export them as a single PDF file to the specified folder (update the bold red part to suit your requirements) naming the PDF file whatever the name of the workbook was.

If you are happy that this is working as described we can try and find a way of removing the need for a user to run it themselves.

Sub Export_Visible_Sheets_To_PDF()
' Defines variables
Dim wbk As Workbook
Dim ws As Worksheet
Dim SaveName As String, ArraySheets() As String
Dim x As Variant


' Disable screen updating to reduce flicker
Application.ScreenUpdating = False
     
    With Worksheets(1)
        ' For each open workbook
        For Each wbk In Workbooks
            ' If the workbook name is not the same as the .xlsb name then...
            If wbk.Name <> ThisWorkbook.Name Then
                ' Activate the workbook
                wbk.Activate
                ' Clear the variable ArraySheets
                ReDim ArraySheets(x)
                ' Update variable SaveName as the name of the workbook
                SaveName = Left(wbk.Name, (InStrRev(wbk.Name, ".", -1, vbTextCompare) - 1))
                ' For each sheet in the workbook
                For Each ws In wbk.Worksheets
                    ' If the worksheet is visible then...
                    If ws.Visible = xlSheetVisible Then
                        ' Re-dims the variable ArraySheets preserving any existing sheet names
                        ReDim Preserve ArraySheets(x)
                        ' Add the sheet name to variable ArraySheets
                        ArraySheets(x) = ws.Name
                        ' Increase variable x by 1 for each sheet added
                        x = x + 1
                    End If
                ' Check next worksheet
                Next ws
                ' Select all sheets in ArraySheets
                Sheets(ArraySheets).Select
                ' Export the selected sheets as a single PDF to the specified folder as "SaveName".pdf
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\TestFolder" & "\" & SaveName & ".pdf"
            End If
        ' Check next workbook
        Next wbk
    End With
    
' Re-enable screen updating
Application.ScreenUpdating = True


End Sub

 

[barbs706]

That works brilliantly! No error messages at all.

If we can now find a way of running that without user intervention, that would be superb.

Thanks for your help so far 'Fishboy'

 

[Fishboy]

That works brilliantly! No error messages at all.

If we can now find a way of running that without user intervention, that would be superb.

Thanks for your help so far 'Fishboy'

Glad to hear that much is working as intended

With regards to automating it, I am interested to know what method you have used to open up the personal.xlsb workbook when you open the other documents. Depending on how that works I am hoping to incorporate my code in as part of that so it should (in theory at least) all happen automatically.

 

[barbs706]

I followed this to create the personal.xlsb:

https://support.office.com/en-us/ar...Workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566

I have tried to run the code you provided at startup using Sub Autpen() in the personal.xlbs file, but I get the following error:

Run-time error '1004':
Method 'Worksheets' of object '_Global' failed.

When I click debug, it highlights With Worksheets(1) in yellow.

I suppose it's trying to run the code before the workbook as opened?

 

[Fishboy]

OK, so adding the following 2 bits of code to the ThisWorkbook module of your Personal.xlsb...

Private WithEvents AppEvents As Application
 
Private Sub Workbook_Open()
 
    Set AppEvents = Application
 
End Sub

Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)
 
    If Not Wb Is Me Then
        Call Export_Visible_Sheets_To_PDF(Wb)
    End If
 
End Sub

...will call the macro whenever you open another workbook. You also need to move my original macro into ThisWorkbook module as well and put Private at the start of the sub name and Wb As Workbook in the brackets at the end of the sub name so it looks like this:

Private Sub Export_Visible_Sheets_To_PDF(Wb As Workbook)

This much I have working, but as always there is an issue

While Export_Visible_Sheets_To_PDF works as a standalone macro without any errors for some reason when using it via the open event in the personal workbook it breaks on the selecting sheets part...

                ' Select all sheets in ArraySheets
                Sheets(ArraySheets).Select

...with an error "Run-time error '1004': Select methods of Sheets class failed"

I find this strange that it has no issue selecting those sheets as the standalone macro but doesn't want to play ball when the open event in the personal workbook is triggering it.

I will continue to dig, and may even start my own thread to see if any of the gurus can step in to explain why that might be happening.

 

[barbs706]

I have just setup 'ThisWorkbook' as you described, but I am getting the following error:

Run-time Error '9'
Subscript Out Of Range

Debug:
' Select all sheets in ArraySheets
Sheets(ArraySheets).Select

It is defiantly opening up the workbook now through, as the filename now appears in the top window frame.

Strange how we have two different error codes, but still failing at the same place?

That would be great if you could start your own thread. Let me know if you find any more info or would like me to try some things. I will be monitoring this thread like a hawk!