VBA 参考工作表与图表表 [英] VBA Refer to worksheet vs chart sheet

问题描述

我正在尝试编写一个小函数，它接受文件路径(工作簿保存的位置)、目标路径(pdf 将保存到的位置)和一串选项卡名称(管道 (|) 分隔)在 Excel 中.

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.

该函数的用户不必输入选项卡名称字符串(可选)，如果不输入，我想选择所有可见选项卡并打印它们.如果用户在单独的工作表中有 50 个图表并且不想编写像Chart1|Chart2|...."这样的字符串，就会出现这种情况.

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|...."

代码:

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

For Each 循环有两个问题:它不抓取诸如Chart1"之类的任何工作表，它只抓取诸如Sheet1"之类的工作表.此外，它会抓取隐藏的工作表，因此当我尝试全选它们时，会出现越界错误.

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.

推荐答案

在循环中使用 WBO.Sheets 而不是 WBO.Worksheets.

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

验证 WSO.Visible = xlSheetVisible 以过滤掉隐藏的工作表.

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

这篇关于VBA 参考工作表与图表表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！