Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA Refer to worksheet vs chart sheet

I'm trying to write a small function that takes in a filepath (where the workbook was saved at), targetpath (where the pdf will be saved to), and a string of tab names (pipe (|) delimited) in excel.

The user of the function doesn't have to input a string of tab names (it's optional) and if they don't, I want to select all of the visible tabs and print them. This would be in the case if the user has 50 charts in separate worksheets and don't want to write a string like "Chart1|Chart2|...."

Code:

For Each WSO.Name In WBO.Worksheets 
    strSheets = strSheets & WSO.Name & "|" 
Next WSO

strSheets = Left(strSheets, Len(strSheets) - 1) 
arraySheets() = Split(strSheets, "|")

WBO.Sheets(arraySheets()).Select     
WBO.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _ 
    strFilePath, Quality:=xlQualityStandard, _ 
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ 
    True

There's two problems with the For Each loop: it doesn't grab any sheets such as "Chart1", it only grabs sheets such as "Sheet1". Also, it will grab hidden sheets so that when I try to select them all I get an out of bounds error.

I didn't know if a Chart sheet is referred to differently then a regular sheet or why hidden sheets are also chosen.

like image 822
Jesse Smothermon Avatar asked Jul 23 '11 00:07

Jesse Smothermon


People also ask

What's the difference between sheets and worksheets in VBA?

In Excel VBA: The 'Worksheets' collection would refer to the collection of all the worksheet objects in a workbook. In the above example, the Worksheets collection would consist of three worksheets. The 'Sheets' collection would refer to all the worksheets as well as chart sheets in the workbook.

What does worksheet mean in VBA?

In VBA, each workbook has a collection of worksheets. There is an entry in this collection for each worksheet in the workbook. This collection is simply called Worksheets and is used in a very similar way to the Workbooks collection. To get access to a worksheet all you have to do is supply the name.


2 Answers

Use WBO.Sheets instead of WBO.Worksheets in the loop.

Verify that WSO.Visible = xlSheetVisible to filter out hidden sheets.

like image 106
GSerg Avatar answered Oct 13 '22 12:10

GSerg


There's two problems with the For Each loop: it doesn't grab any sheets such as "Chart1", it only grabs sheets such as "Sheet1"

Charts and Worksheets are two different collections.
Try this:

Sub Demo()
Dim oWs As Worksheet
Dim oCs As Chart

For Each oWs In ActiveWorkbook.Worksheets
    Debug.Print oWs.Name
Next

For Each oCs In ActiveWorkbook.Charts
    Debug.Print oCs.Name
Next
End Sub
like image 24
Steve Rindsberg Avatar answered Oct 13 '22 13:10

Steve Rindsberg