vba遍历字符串以查找日期 [英] vba loop through string to find date

问题描述

我正在尝试遍历文件名以查找日期.我没有要查找的特定日期，只是尝试在文件名中存在某个日期的情况下拉一个日期.问题是用户每次都不会使用相同的格式，因此我需要考虑从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遍历字符串以查找日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！