如何激活文件名中包含日期并每周更改的工作簿?

Question

我需要你的帮助。 我有一个主宏，它使用单元格引用(文件的位置和文件名(打开前一天的文件。打开的文件用于将数据从其工作表(4 个工作表(复制到主宏。我录制了宏，日期是硬编码的。当我在下周运行宏时，它会给我一个错误，因为以前的文件名是硬编码的。我尝试使用活动工作簿，但它不起作用，因为我在复制粘贴时在主文件和上一个文件之间返回并防御。

下面的示例查询

Sub OpenPrev()
'
' OpenPrev Macro
'
'
ChDir "\hfx1nas02hfx1_data02_sh$Share2HFX MDBGMarlonMarkitEDMEDM_WORK"
Workbooks.Open Range("M1").Value & ".xlsx"
Sheets("BBG COMDTY - Cur Data").Select
Range("A3:AF10000").Select
Selection.Copy
Windows("EDM Summary Macro 1.1.xlsm").Activate
Sheets("BBG COMDTY - Prev Data").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("EDM_Matcher_Inbox_20191202.xlsx").Activate
Sheets("BBG PK BBGID - Cur Data").Select
Range("A3:AJ10000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("EDM Summary Macro 1.1.xlsm").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("BBG PK BBGID - Prev Data").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Activate
Windows("EDM_Matcher_Inbox_20191202.xlsx").Activate
Sheets("BBG BO - Cur Data").Select
Range("A3:AH10000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("EDM Summary Macro 1.1.xlsm").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=2
Sheets("BBG BO - Prev Data").Select

Answer 1

Workbooks.Open Range("M1").Value & ".xlsx"

Workbooks.Open是一个函数- 它返回一个值- 在本例中，如果函数成功返回而不引发任何错误，则返回对打开的Workbook对象的引用。

您正在丢弃该对象引用。用它！

Dim book As Workbook
Set book = Workbooks.Open(ActiveSheet.Range("M1").Value & ".xlsx")

此说明假定打开工作簿使其成为ActiveWorkbook：

Sheets("BBG COMDTY - Cur Data").Select

不要摆脱副作用和假设。

Dim currentCOMDTYSheet As Worksheet
Set currentCOMDTYSheet = book.Worksheets("BBG COMDTY - Cur Data")

您无需SelectRange即可Copy它。当你想在Selection工作时，你只需要Select一个Range，而你几乎从不想这样做。

Range("A3:AF10000").Select
Selection.Copy

相反，只需处理您得到的Worksheet对象：

currentCOMDTYSheet.Range("A3:AF10000").Copy

但是，让我们稍等一下，看看它首先被复制到哪里：

Windows("EDM Summary Macro 1.1.xlsm").Activate
Sheets("BBG COMDTY - Prev Data").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

(旁注：通常应避免行连续_，但是当您使用它们时，请避免在中间拆分命名参数 -SkipBlanks:=False应该在一行上(

我假设"EDM 摘要宏 1.1.xlsm"工作簿是托管您的 VBA 项目的工作簿。如果是这种情况，则无需从Windows集合中取消引用其Window，也无需从某些Workbooks集合中取消引用它(这是激活该工作簿比获取其Window更直接的方法(。承载 VBA 项目的工作簿始终在作用域中，并且始终称为ThisWorkbook(除非您重命名了它，但不要这样做(。

由于编译时存在的所有工作表ThisWorkbook都有一个代码名称，因此您根本不需要取消引用工作表 - 它就在那里，随时可用。也许它被称为Sheet3或Sheet21：在 VBE 的项目资源管理器中找到"BBG COMDTY - Prev Data"表，然后将其(Name)属性设置为有效的 VBA 标识符，例如PrevCOMDTYDataSheet.现在我们可以直接粘贴到那里：

currentCMDTYSheet.Range("A3:AF10000").Copy PrevCOMDTYDataSheet.Range("A3")

但这会带来格式、边框、验证和公式，我们只需要这些值。在这种情况下，我们甚至不需要让剪贴板参与其中：

PrevCOMDTYDataSheet.Range("A3:AF10000").Value = currentDataSheet.Range("A3:AF10000").Value

完成了！

类似这里：

Windows("EDM_Matcher_Inbox_20191202.xlsx").Activate
Sheets("BBG PK BBGID - Cur Data").Select
Range("A3:AJ10000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("EDM Summary Macro 1.1.xlsm").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("BBG PK BBGID - Prev Data").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

您已经有了book参考，因此无需再次激活窗口; BBGID数据手册需要一个对象参考：

Dim currentBBGIDSheet As Worksheet
currentBBGIDSheet = book.Worksheets("BBG PK BBGID - Cur Data")

然后类似于上面：

PrevBBGIDSheet.Range("A3:AJ10000").Value = currentBBGIDSheet.Range("A3:AJ10000").Value

与最后一个块相同：

ActiveWorkbook.Activate
Windows("EDM_Matcher_Inbox_20191202.xlsx").Activate
Sheets("BBG BO - Cur Data").Select
Range("A3:AH10000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("EDM Summary Macro 1.1.xlsm").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=2
Sheets("BBG BO - Prev Data").Select

Dim currentBBGBOSheet As Worksheet
Set currentBBGBOSheet = book.Worksheets("BBG BO - Cur Data")
PrevBBGBOSheet.Range("A3:AH10000").Value = currentBBGBOSheet.Range("A3:AH10000").Value

所以，回顾一下：第一步是用ThisWorkbook命名您的工作表。您需要PrevCOMDTYSheet、PrevBBGIDSheet和PrevBBGBOSheet在 VBA 项目中命名的工作表，然后这可以是一次性清理：

Public Sub OpenPrev()
Dim book As Workbook
Set book = Workbooks.Open(ActiveSheet.Range("M1").Value & ".xlsx")
Dim currentCOMDTYSheet As Worksheet
Set currentCOMDTYSheet = book.Worksheets("BBG COMDTY - Cur Data")
PrevCOMDTYSheet.Range("A3:AF10000").Value = currentCOMDTYSheet.Range("A3:AF10000").Value
Dim currentBBGIDSheet As Worksheet
currentBBGIDSheet = book.Worksheets("BBG PK BBGID - Cur Data")
PrevBBGIDSheet.Range("A3:AJ10000").Value = currentBBGIDSheet.Range("A3:AJ10000").Value
Dim currentBBGBOSheet As Worksheet
Set currentBBGBOSheet = book.Worksheets("BBG BO - Cur Data")
PrevBBGBOSheet.Range("A3:AH10000").Value = currentBBGBOSheet.Range("A3:AH10000").Value
End Sub

第二次传递，我会删除硬编码的 10000 行号，并实际计算出需要复制的实际大小Range。