用通配符打开工作簿？
用通配符打开工作簿？
提问于 2017-04-06 13:58:46
我在以下目录中有一个.xlsx文件：
G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\8. 2017\2017 Planner.xlsx我可以把它指向这个目录，但是目录会根据年份而改变。
所以这个目录可以变成：
G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\9. 2018\2018 Planner.xlsx为了处理这个问题，我尝试将通配符添加到我的路径中，如下所示：
G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\" & "*." & " " & Year(Date) & "\"我的工作簿不会打开。有人能告诉我我哪里出了问题吗？
分：
'Find Planner
If Len(FindDepotMemo) Then
Set wb2 = Workbooks.Open(FindDepotMemo, ReadOnly:=True, UpdateLinks:=False)
End If功能：
Function FindDepotMemo() As String
Dim Path As String
Dim FindFirstFile As String
Path = "G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\" & "*." & " " & Year(Date) & "\"
FindFirstFile = Dir$(Path & "*.xlsx")
While (FindFirstFile <> "")
If InStr(FindFirstFile, "Planner") > 0 Then
FindDepotMemo = Path & FindFirstFile
Exit Function
End If
FindFirstFile = Dir
Wend
End Function回答 2
Stack Overflow用户
回答已采纳
发布于 2017-04-06 16:30:20
有很多种方法，但是，我会使用这种方法：
Function FindDepotMemo() As String
Dim oldPath, tempPath, newPath As String
Dim FindFirstFile As String
Dim addInt as Integer
' ASSUMING YOU ALREADY HAVE THIS PATH
' WE WILL USE THIS AS BASE PATH
' AND WE WILL INCREMENT THE NUMBER AND YEAR IN IT
oldPath = "G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\8. 2017"
' EXTRACT 8. 2017 FROM oldPath
tempPath = Mid(oldPath, InStrRev(oldPath, "\") + 1)
' GET THE YEAR DIFFERENCE
addInt = Year(Date) - CInt(Split(tempPath, ".")(1))
' ADD THIS DIFFERENCE TO NUMBER AND YEAR
' AND NOW YOU HAVE CURRENT YEAR FOLDER
newTemp = Split(tempPath, ".")(0) + addInt & ". " & Split(tempPath, ".")(1) + addInt
FindFirstFile = Dir$(Path & "\" & "*.xlsx")
While (FindFirstFile <> "")
If InStr(FindFirstFile, "Test") > 0 Then
FindDepotMemo = Path & FindFirstFile
Exit Function
End If
FindFirstFile = Dir
Wend
End Function因为我增加了一些评论来帮助你理解我在做什么。
Stack Overflow用户
发布于 2017-04-06 15:45:04
试试这个方法。代码循环遍历文件夹名1.2020、1.2019、1.2018和当前年份，找到最低的文件夹名，比如1.2018。在那一年，它寻找最高月份的数字，放弃前一个月的下一个更高。如果存在4.2018，那就是它使用原始代码查找文件名的路径。
Function FindDepotMemo() As String
Dim Pn As String ' Path name
Dim Fn As String ' Folder name
Dim Sp() As String ' Split string
Dim Arr() As String, Tmp As String
Dim FindFirstFile As String
Dim i As Integer
Dim n As Integer
For i = 2020 To Year(Date) Step -1
Pn = "G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\1. " & CStr(i) & "\"
If Len(Dir(Pn, vbDirectory)) Then Exit For
Next i
If i >= Year(Date) Then
Sp = Split(Pn, "\")
Arr = Sp
n = UBound(Sp) - 1
Fn = Sp(n)
For i = 2 To 12
Arr(n) = CStr(i) & Mid(Fn, 2)
Pn = Join(Arr, "\")
If Len(Dir(Pn, vbDirectory)) = 0 Then Exit For
Sp = Arr
Next i
FindFirstFile = Join(Sp, "\") & "*.xlsx"
While (FindFirstFile <> "")
If InStr(FindFirstFile, "Planner") > 0 Then
FindDepotMemo = Pn & FindFirstFile
Exit Do
End If
FindFirstFile = Dir
Wend
End If
End Function如果未找到1.2017或更高版本，则该函数将返回一个空字符串。由于缺乏数据，我无法测试代码。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43257415
复制相关文章
相似问题
腾讯云开发者
