根据图纸名称仅将Excel VBA中的某些图纸动态保存为PDF [英] Dynamic saving to PDF only certain Sheets in Excel VBA based on sheetname

问题描述

我有一个包含5张纸的Excel工作簿(为简化起见，这里我将其简化):

I have an Excel workbook with 5 sheets(I'm simplifying for clarity here):

'DataMaster'

'CityData S'

'TownData S'

'TownlandData S'

'StreetData S'

有时还会有其他工作表/标签，但要保存的工作表都带有"S"后缀.这里的数据管理员"不需要保存.

Sometimes there are additional sheets/tabs but the sheets to be saved are all suffixed with the ' S' suffix. 'Datamaster' here does not need to be saved.

我希望使用

1)仅会查看名称中最右边2个字符为"S"的工作表.

1) Only will look at sheets with rightmost 2 characters in their names being ' S'.

2)不在乎这些表是3还是10.

2) Will not care if there 3 or 10 of these sheets.

3)将导出为PDF，其文件名是工作表名称中的左短语.

3) will export to PDF with filename being the leftphrase in the sheetname.

因此，出口将是:

CityData.PDF , 
TownData.PDF , 
TownlandData.PDF , 
StreetData.PDF

任何提示/帮助将不胜感激，我仅基本导出为PDF代码.

Any tips/help would be appreciated, I have the basic export to PDF code only.

推荐答案

您可以使用 For Each 循环遍历工作簿中的每个工作表.您可以使用 If 来测试 Right()两个字符是否匹配.

You can use a For Each loop to loop through every worksheet in your workbook. You can use an If to test if the Right() two characters match.

类似的东西:

Sub SaveToPDF
    Dim ws as worksheet

    'Loop through worksheets, each worksheet is assigned to variable "ws" declared above:
    For Each ws in ThisWorkbook.Worksheets
        If Right(ws.name, 2) = " S" Then
            'export as pdf
            ws.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="C:\" & Left(ws.name, len(ws.name)-2) & ".pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
        End If
    Next ws
End Sub

这篇关于根据图纸名称仅将Excel VBA中的某些图纸动态保存为PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！