vba - Excel vba - 打开具有可变(日期)文件名的文件

Question

由于日期在其中，我有以下代码来打开具有可变文件名的文件。我个人每天都用日期戳保存每个文件，即今天早上我用昨天的日期 4.20.17 保存了一个文件。

此代码将在每个星期五早上运行，目标是加载最后 5 个工作日的文件(上周五、本周一、周二、周三、周四)从这些文件中获取一些信息(从每个文件中复制 2 个单元格)，将该信息粘贴到新工作表中，最后关闭每个文件。

目前，代码设置为在文件不存在时告诉我(例如，上周五是耶稣受难日，所以周一早上，我没有为上周五创建任何文件)，然后忽略并移过那一天。

我目前遇到的问题(除了代码很长并且可能被连接)是上周四存在一个文件，但我的代码告诉我没有。我被告知这是因为代码实际上是在今天(星期四)查看，而不是一周前的星期四，实际上有一个文件。

任何帮助表示赞赏。我删除了几天以使下面的代码不那么难看，示例文件名为“Agent Group Daily Summary 4.19.17”

Const strFilePath As String = "D:\Users\stefan.bagnato\Desktop\Daily Performance Summary\Agent Group Daily Summary "
Dim LastFridayDate, MondayDate, TuesdayDate, WednesdayDate, ThursdayDate As String
Dim fullFileNameLastFriday, fullFileNameMonday, fullFileNameTuesday, fullFileNameWednesday, fullFileNameThursday As String
Dim wbkLastFriday, wbkMonday, wbkTuesday, wbkWednesday, wbkThursdayOpen As Workbook

LastFridayDate = Format(Date - (Weekday(Date, vbFriday) - 1), "m.d.yy")
fullFileNameLastFriday = strFilePath & LastFridayDate & ".xls"
If Dir(fullFileNameLastFriday) = "" Then
    MsgBox "File for last Friday doesn't exist!"
    GoTo ExitLastFriday
End If
Set wbkLastFriday = Workbooks.Open(fullFileNameLastFriday, False, True)
Call BasicDailySummary
wbkLastFriday.Activate
Range("T2:T8").Copy
fp.Activate
Range("B3:B9").PasteSpecial xlPasteValues
wbkLastFriday.Activate
Range("F2:F8").Copy
fp.Activate
Range("G3:G9").PasteSpecial xlPasteValues
wbkLastFriday.Close SaveChanges:=False
ExitLastFriday:

MondayDate = Format(Date - (Weekday(Date, vbMonday) - 1), "m.d.yy")
fullFileNameMonday = strFilePath & MondayDate & ".xls"
If Dir(fullFileNameMonday) = "" Then
    MsgBox "File for Monday doesn't exist!"
    GoTo ExitMonday
End If
Set wbkMonday = Workbooks.Open(fullFileNameMonday, False, True)
Call BasicDailySummary
wbkMonday.Activate
Range("T2:T8").Copy
fp.Activate
Range("C3:C9").PasteSpecial xlPasteValues
wbkMonday.Activate
Range("F2:F8").Copy
fp.Activate
Range("H3:H9").PasteSpecial xlPasteValues
wbkMonday.Close SaveChanges:=False
ExitMonday:

....................................

ThursdayDate = Format(Date - (Weekday(Date, vbThursday) - 1), "m.d.yy")
fullFileNameThursday = strFilePath & ThursdayDate & ".xls"
If Dir(fullFileNameThursday) = "" Then
    MsgBox "File for Thursday doesn't exist!"
    GoTo ExitThursday
End If
Set wbkThursday = Workbooks.Open(fullFileNameThursday, False, True)
Call BasicDailySummary
wbkThursday.Activate
Range("T2:T8").Copy
fp.Activate
Range("F3:F9").PasteSpecial xlPasteValues
wbkThursday.Activate
Range("F2:F8").Copy
fp.Activate
Range("K3:K9").PasteSpecial xlPasteValues
wbkThursday.Close SaveChanges:=False
ExitThursday:

Answer 1

That a file exists for last Thursday, yet my code tells me there is none

如 I explained in the other question you asked yesterday , 把 vbMonday或 vbThursday等在 Format函数不会神奇地告诉 VBA 当天返回:

Hint: The vbFriday part of the Weekday function is not magically telling it to get friday's date. It's actually telling it that, for the sake of this function call, consider Friday to be the first day of the week. The Weekday function then returns an integer (the ordinal day of the week) which it subtracts from the Date.

所以，你需要回过头来了解这些函数是如何工作的，你不能随便把常量转储到那里，而不努力去理解它们在做什么，或者为什么。请注意，您绝对需要 read this and learn how to begin debugging and troubleshooting first .这描述了如何在运行时单步执行代码并检查变量的值/等的基础知识。这些技术是您使用 VBA 所需的基础。

Here是 VBA 中可用的语句列表。这是解释诸如“如何使用 For/Next 等创建循环结构”之类的文档。

你应该回顾一下你在这里问过的十几个问题，并为那些已经解决了你问题的答案标记为已接受的答案。这只是礼仪的一个基本点:你在这里问了 11 个问题，只接受了 1 个答案。

另请注意，这种声明不会像您认为的那样做:

Dim LastFridayDate, MondayDate, TuesdayDate, WednesdayDate, ThursdayDate As String
Dim fullFileNameLastFriday, fullFileNameMonday, fullFileNameTuesday, fullFileNameWednesday, fullFileNameThursday As String
Dim wbkLastFriday, wbkMonday, wbkTuesday, wbkWednesday, wbkThursdayOpen As Workbook

只有每个语句中的最后一项是强类型的，其余的都是隐式变体。您应该尽可能地强输入所有变量，例如:

Dim wbkLastFriday As Workbook, wbkMonday As Workbook, wbkTuesday As Workbook, wbkWednesday As Workbook, wbkThursdayOpen As Workbook

而不是使用五个不同的工作簿对象(除非您真的需要一次打开 5 个工作簿，只需使用一个工作簿对象并在循环中操作，在每次迭代时打开连续的文件。

Dim wb as Workbook
Dim i as Long
For i = 1 to 5
    Set wb = Workbooks.Open(...)
    'Do something
    wb.Close()
Next

解决您的实际问题:

像下面这样的函数将返回一个日期组件数组。这将返回从 FirstDay 开始的前 7 天(默认为上周五)。您可以使用 Dir像以前一样简单地测试文件名是否有效/存在(例如，周日文件不存在等)，如果它无效则跳过它。

Function GetFileNames(Optional FirstDay = vbFriday)
Dim filenames(1 To 7) As String
Dim i As Long
For i = 1 To 7
filenames(i) = Format(Date - (Weekday(Date, FirstDay) + i), "m.d.yy")
Next
GetFileNames = filenames
End Function