VBA 参考工作表与图表表

发布于 2024-11-25 14:12:55 字数 826 浏览 0 评论 0原文

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

该函数的用户不必输入一串选项卡名称（它是可选的），如果不需要，我想选择所有可见的选项卡并打印它们。如果用户在单独的工作表中有 50 个图表并且不想编写类似“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”之类的工作表。另外，它会抓取隐藏的工作表，因此当我尝试选择所有工作表时，我会收到越界错误。

我不知道图表工作表的引用是否与常规工作表不同，也不知道为什么还选择隐藏工作表。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

云柯 2024-12-02 14:12:55

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

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

回复收藏 0 原文

歌入人心 2024-12-02 14:12:55

<块引用>
For Each 循环有两个问题：它不抓取任何工作表，如“Chart1”，它只抓取“Sheet1”等工作表

。图表和工作表是两个不同的集合。
试试这个：

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

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

回复收藏 0 原文

~没有更多了~