我试图添加添加法语版本到我的代码。我有宏，从文本文件报告中读取并提取正确格式的日期。文本文件日期格式是2023年7月13日。我的宏工作正常，但有时日期出现在法语-一月-一月，F：V -二月，MAR -三月，AVR -四月，MAI-五月，JUN -六月，JLT -七月，AO} -八月，SEP -九月，OCT -十月，NOV -十一月，D：C -十二月。我试图找到最好的解决方案，将其添加到我的代码中，这样它就可以读取所有可能的日期，并给予我一个常规的日期格式作为输出。以下是我的代码：
Sub test()
Dim fn As String, mtch As Object, m As Object, s As Object, txt As String
Dim i As Long
fn = "C:\temp\test.txt"
txt =CreateObject("scripting.filesystemobject").OpenTextFile(fn).ReadAll
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "[^\n]+"
Set mtch = .Execute(txt)
i = 1
Dim b As Long
b = 1
For Each m In mtch
.Pattern = "[a-zA-Z0-9]{7}\s\s[^\s]+\s[a-zA-Z\s]*[0-9]{2}\/[0-9]{4}"
For Each s In .Execute(m.Value)
i = i + 1
Cells(i, 1) = s
b = b + 1
Range("B" & b).Value = Right(Cells(i, 1), 10)
Next
Next
End With
Dim var As String
Dim N As Long, p As Long, j As Long
N = Cells(Rows.Count, "B").End(xlUp).Row
For p = 2 To N
var = Range("B" & p).Value
Range("C" & p).Value = convert_date(var)
Range("D" & p).Value = Range("C" & p) + 179
Range("E" & p).Value = Range("C" & p) + 209
j = j + 1
Next p
End Sub
Function convert_date(date_as_string As String) As Date
Dim mthstring As String
mthstring = "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC"
convert_date = DateSerial( _
CInt(Right(date_as_string, 4)), _
CInt(((InStr(1, mthstring, Left(date_as_string, 3)) - 1) / 4) + 1), _
CInt(Replace(Mid(date_as_string, 4, 2), "/", "")))
End Function
Sub testConvertDate()
Dim var As String
Dim N As Long, i As Long, j As Long
N = Cells(Rows.Count, "B").End(xlUp).Row
Dim m As Integer
For i = 2 To N
'Range("B" & i).Value = Right("A" & i, 10)
var = Range("B" & i).Value
Range("C" & i).Value = convert_date(var)
Range("D" & i).Value = Range("C" & i) + 179
Range("E" & i).Value = Range("C" & i) + 209
j = j + 1
Next i
End Sub这是我的结果：
2条答案
按热度按时间sycxhyv71#
由于您的法语月份名称枚举包含3或4个字符的字符串，您需要以不同的方式处理字符串
Date。请尝试下一个经过调整的函数。不要错过复制仅返回数字的函数（onlyNo）：该函数的调用应与现有代码中的调用完全相同。
您可以使用下一个测试Sub简单地测试它。请逐个取消注解注解行并运行它：
请在测试后发送一些反馈。
dgenwo3n2#