Excel vba - 打开具有变量（日期）文件名的文件 [英] Excel vba - Open files with variable (dates) filenames

问题描述

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

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

目前，代码设置为告诉我什么时候不存在一个文件（例如上个星期五是耶稣受难节，所以星期一上午，我没有创建任何文件上周五），然后忽略并移动超过那一天。

我现在的问题（除了代码很长，可能会连接）是上个星期四存在一个文件，但我的代码告诉我没有。我被告知，这是因为代码实际上是在今天（星期四），而不是一个星期前的星期四，其中有一个文件。

任何帮助是赞赏。我删除了几天，使下面的代码少了一个熊看，而一个示例文件名是代理组每日摘要4.19.17

  Const strFilePath As String =D：\Users\stefan.bagnato\Desktop\Daily Performance Summary\Agent Group Daily Summary
 Dim LastFridayDate，MondayDate，TuesdayDate，WednesdayDate，星期四作为字符串
 Dim fullFileNameLastFriday，fullFileNameMonday，fullFileNameTuesday，fullFileNameWednesday，fullFileNameTursday，StringFB，工作日（日期，vbFriday） -  1），mdyy）
 fullFileNameLastFriday = strFilePath& LastFridayDate& .xls
如果Dir（fullFileNameLastFriday）=然后
 MsgBox上周五的文件不存在！ 
 GoTo ExitLastFriday 
 End If 
设置wbkLastFriday = Workbooks.Open（fullFileNameLastFriday，False，True）
调用BasicDailySummary 
 wbkLastFriday.Activate 
范围（T2 ：T8）复制
 fp.Activate 
范围（B3：B9）PasteSpecial xlPasteValues 
 wbkLastFriday.Activate 
范围（F2：F8）。 b $ b fp.Activate 
 Range（G3：G9）。PasteSpecial xlPasteValues 
 wbkLastFriday.Close SaveChanges：= False 
 ExitLastFriday：
 
 MondayDate = Format日期 - （工作日（日，vbMonday） -  1），mdyy）
 fullFileNameMonday = strFilePath&星期一.xls
如果Dir（fullFileNameMonday）=然后
 MsgBox星期一的文件不存在！ 
 GoTo ExitMonday 
 End If 
设置wbkMonday = Workbooks.Open（fullFileNameMonday，False，True）
调用BasicDailySummary 
 wbkMonday.Activate 
范围（T2 ：T8）复制
 fp.Activate 
范围（C3：C9）PasteSpecial xlPasteValues 
 wbkMonday.Activate 
范围（F2：F8）复制
 fp.Activate 
范围（H3：H9）。PasteSpecial xlPasteValues 
 wbkMonday.Close SaveChanges：= False 
 ExitMonday：
 
 .... ................................ 
 
星期四日期=格式（日期 - （工作日（日期，vbThursday） -  1），mdyy）
 fullFileNameThursday = strFilePath&周日日.xls
如果Dir（fullFileNameThursday）=然后
 MsgBox星期四的文件不存在！ 
 GoTo ExitThursday 
 End If 
设置wbkThursday = Workbooks.Open（fullFileNameThursday，False，True）
调用BasicDailySummary 
 wbkThursday.Activate 
范围（T2 ：T8）复制
 fp.Activate 
范围（F3：F9）。PasteSpecial xlPasteValues 
 wbkThursday.Activate 
范围（F2：F8）。 b $ b fp.Activate 
 Range（K3：K9）。PasteSpecial xlPasteValues 
 wbkThursday.Close SaveChanges：= False 
 ExitThursday：
  / pre> 
 
解决方案
似乎您希望您的搜索从昨天开始，而不是今天。如果是，您可以尝试更改
 
 
  ThursdayDate =格式（日期 - （工作日（日期，vbThursday） -  1 ），mdyy） 
 
 
 into 
  ThursdayDate =格式（日期 - （工作日（日期 -  1，vbThursday）），mdyy）
  
，并将其概括为其他星期。实际上现在的情况是，当它运行时，比方说，在这个星期四，它会查找最后一个的文件星期四... 
 
I have the below code to open up files with variable file names, due to dates being in them.  I personally save each file daily with the date stamp, ie this morning I saved a file with yesterday's date, 4.20.17.

This code will be run every Friday morning, and the goal is to load the last 5 work days' files (last Friday, this Monday, Tues, Wed, Thurs) grab some info out of those files (copy 2 cells from each), paste that info in a new sheet, and finally close each file.

Currently, the code is set to tell me when a file does not exist (for instance, last Friday was Good Friday, so Monday morning, I did not create any file for last Friday), and then ignore and move past that day.

The issue I currently have (besides the code being long and can probably be concatenated) is that a file exists for last Thursday, yet my code tells me there is none.  I have been advised that this is because the code is actually looking at today (Thursday) and not a week ago Thursday, where there actually is a file.

Any assistance is appreciated.  I removed a few days to make the below code less of a bear to look at, and a sample filename is "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:

解决方案
It seems that you want your search to start from yesterday instead of today. If so, you can try changing

  
ThursdayDate = Format(Date - (Weekday(Date, vbThursday) - 1), "m.d.yy")
into
ThursdayDate = Format(Date - (Weekday(Date - 1, vbThursday)), "m.d.yy")
and generalize it to other week days. In fact what it does now is that when it runs, say, on this Thursday, it looks up for the file of last Thursday...

                        
这篇关于Excel vba  - 打开具有变量（日期）文件名的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！