问题描述
我正在尝试遍历文件名以查找日期.我没有要查找的特定日期,只是尝试在文件名中存在某个日期的情况下拉一个日期.问题是用户每次都不会使用相同的格式,因此我需要考虑从1-1-14到2014年1月1日的所有内容.我为此编写了一个函数,但是当文件名中的日期为2014年6月23日时,我得到的返回值为6/23/201.示例文件名为"F2 A-Shift 06-23-2014日常维护Report.xls"和"F1C-Shift 6-25-14日常维护Report.xls".对于可行解决方案的任何帮助,将不胜感激.
I am trying to loop through filenames to find a date. I do not have a specific date I'm looking for, just trying to pull a date if one exists in the filename . Problem is that the users don't use the same format everytime so I have everything from 1-1-14 to 01-01-2014 to consider. I wrote a function for this but when the date in the file name is 06-23-2014 I get a return of 6/23/201. Example file names are "F2 A-Shift 06-23-2014 Daily Sustaining Report.xls" and "F1C-Shift 6-25-14 Daily Sustaining Report.xls". Any help on a viable solution would be greatly appreciated.
Function GetDate(strName As String) As Date
Dim intLen As Integer, i As Integer
intLen = Len(strName)
If intLen <= 10 Then Exit Function
For i = 1 To intLen - 10
If IsDate(Mid(strName, i, 10)) = True Then
GetDate = (Mid(strName, i, 10))
Exit Function
End If
Next i
GetDate = "1/1/2001"
End Function
推荐答案
您的第一个问题是,您假定日期始终为10个字符,而第二个则是在检查有效日期,并在获得有效日期后立即进行检查你已经存在你的循环了.
Your first problem is you are assuming that a date is always 10 characters and 2nd is you are checking for a valid date and as soon as you get a valid date you are existing your loop.
您使用的代码永远不会将6-1-14识别为有效日期,因为即使在尾随空格的情况下,当您查看10个字符的块时,它也永远不会是有效日期.
The code you are using will never recognize 6-1-14 as a valid date because even with a trailing and leading space, it will never be a valid date when you are looking at blocks of 10 characters.
第二个问题的问题在于 If IsDate(Mid(strName,i,10))= True Then
The issue with your 2nd problem lies with If IsDate(Mid(strName, i, 10)) = True Then
Excel在很多方面做得很好,其中之一就是猜测您要尝试做什么.您假设日期(例如"06-23-201")上的前导空格将不被视为有效日期,但是您不正确. IsDate
函数将其视为有效日期,因此您的循环在到达"4"之前就已退出.这就是为什么您只得到 6/23/201
的原因.
There are a number of things Excel does too well and one of which is guess what you are trying to do. You are assuming that a leading space on a date such as " 06-23-201" would not be considered a valid date, but you are incorrect. The IsDate
function sees this as a valid date so your loop exits before you even get to the "4". This is why you are only getting 6/23/201
.
因此,要解决这两个问题,您需要修改逻辑.您不必使用一次检查10个字符的事实,而应使用这样的事实,即您的日期似乎总是有前导或尾随空格.
So to solve both of your problems, you need to modify your logic. Instead of focusing on checking 10 characters at a time, you should use the fact that your dates will always seem to have a leading or trailing space.
Function GetDate(strName As String) As Date
Dim FileNameParts as Variant
Dim part as Variant
FileNameParts = Split(strName," ")
For Each part in FileNameParts
If IsDate(part ) = True Then
GetDate = part
Exit Function
End If
Next
GetDate = "1/1/2001"
End Function
这篇关于vba遍历字符串以查找日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!