获取Excel工作表名称并用作宏中的变量 [英] Get Excel sheet name and use as variable in macro

问题描述

我正在尝试找到一种方法来将Excel工作表名称用作我编写的宏中的变量.每个月我都会处理一份工作簿，该工作簿会寄给我2张纸.宏的一部分使用打开文件"界面导航到该文件夹​​并打开文件.

I'm trying to find a way to use an Excel sheetname as a variable in a macro that I've written. Every month I deal with a workbook that is sent to me with 2 sheets. Part of the macro uses the 'Open File' interface to navigate to the folder and open the file.

文件中的第一张表称为报告"，它是静态的.我要做的就是删除它(因为我不需要它).

The first sheet in the file is called 'Report', which is static. All I do with that is delete it (because I don't need it).

第二张表可以被称为任何东西，这就是我需要在其上运行宏的表.有没有办法说:

The second sheet could be called anything and that's the sheet upon which I need to run the macro. Is there a way to say:

shtName = %whatever_the_sheetname_is_called%

，然后在整个宏中使用'shtName'变量?我想将这个新文件名作为变量也将有所帮助.

and then use the 'shtName' variable throughout the macro? I suppose getting this new filename as a variable would help as well.

推荐答案

在您将要使用的Visual Basic宏中

in a Visual Basic Macro you would use

pName = ActiveWorkbook.Path      ' the path of the currently active file
wbName = ActiveWorkbook.Name     ' the file name of the currently active file
shtName = ActiveSheet.Name       ' the name of the currently selected worksheet

ActiveWorkbook.Worksheets(1)

因此，删除[Report]标签后，您将使用

so after deleting the [Report] tab you would use

ActiveWorkbook.Worksheets("Report").Delete
shtName = ActiveWorkbook.Worksheets(1).Name

要以后再处理该工作表"，您可以创建一个范围对象，如

to "work on that sheet later on" you can create a range object like

Dim MySheet as Range
MySheet = ActiveWorkbook.Worksheets(shtName).[A1]

并继续研究MySheet(rowNum, colNum)等.

在不定义shtName的情况下创建范围对象的快捷方式:

shortcut creation of a range object without defining shtName:

Dim MySheet as Range
MySheet = ActiveWorkbook.Worksheets(1).[A1]

这篇关于获取Excel工作表名称并用作宏中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！