在VBA中正则表达式使用正则表达式 [英] Using regex with positive lookbehind in VBA

问题描述

这不是我完整编写的代码，有些是我从一个或两个站点拼凑而成的，有些是我设置的.我想做的是使用 regex.Pattern 中定义的正则表达式函数来查看邮件主题并提取一个值.这是我将在电子邮件主题中看到的:

This is not code I wrote completely, some I have pieced together from one or two sites and some is what I have set. What I'm trying to do is use a regex function defined in regex.Pattern to look at message subject and extract a value. This is what I'm going to see in the email subject:

新Linux服务器:prod-servername-a001

New Linux Server: prod-servername-a001

到目前为止，我可以将完整的邮件主题放入Excel文件中，但是当我尝试实现regex部分时，我得到了错误代码5017(根据我所能找到的表达式错误)，而regex不是在职的".我的期望是脚本将提取消息主题，使用正则表达式提取值并将其放在单元格中.我正在使用RegEx Builder(正则表达式测试程序)来测试该表达式，并且该表达式可以在此处运行，但不适用于此处.我是VB的新手，所以我不知道问题是否在于VB无法使用该表达式，或者脚本是否在其他地方失败，并且该错误是另一个问题造成的.还是有更好的方式编写此代码?

So far I can get the full message subject into the Excel file, but when I have tried to implement the regex portion, I get an error code 5017 (error in expression from what I can find) and the regex is not "working". My expectation is the script will pull the message subject, use the regex to extract the value and place it in the cell. I'm using RegEx Builder (regex testing program) to test the expression and it works there, but not here. I am very new to VB, so I don't know if the issue is that VB can't use this expression or if the script is failing somewhere else and the error is something residual from another problem. Or is there a better way to write this?

Sub ExportToExcel()
On Error GoTo ErrHandler

'Declarations
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim rng As Excel.Range
    Dim strSheet As String
    Dim filePath As String
    Dim strPath As String
    Dim intRowCounter As Integer
    Dim intColumnCounter As Integer
    Dim msg As Outlook.MailItem
    Dim nms As Outlook.NameSpace
    Dim fld As Outlook.MAPIFolder
    Dim itm As Object

'RegEx Declarations
    Dim result As String
    Dim allMatches As Object
    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")

    regex.Pattern = "(?<=Server: ).*"
    regex.Global = True
    regex.IgnoreCase = True


' Set the filename and path for output, requires creating the path to work
    strSheet = "outlook.xlsx"
    strPath = "D:\temp\"
    filePath = strPath & strSheet

'Debug
Debug.Print filePath

'Select export folder
    Set nms = Application.GetNamespace("MAPI")
    Set fld = nms.PickFolder

'Handle potential errors with Select Folder dialog box.
    If fld Is Nothing Then
        MsgBox "There are no mail messages to export", vbOKOnly, "Error"
        Exit Sub

    ElseIf fld.DefaultItemType <> olMailItem Then
        MsgBox "There are no mail messages to export", vbOKOnly, "Error"
        Exit Sub

    ElseIf fld.Items.Count = 0 Then
        MsgBox "There are no mail messages to export", vbOKOnly, "Error"
        Exit Sub
    End If

'Open and activate Excel workbook.
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open (filePath)
    Set wkb = appExcel.ActiveWorkbook
    Set wks = wkb.Sheets(1)
    wks.Activate
    appExcel.Application.Visible = True


'Copy field items in mail folder.
For Each itm In fld.Items
    intColumnCounter = 1
    Set msg = itm

    If itm.UnRead = True Then
        intRowCounter = intRowCounter + 1
        wks.Cells(1, 1).value = "Subject" 'Row 1 Column 1 (A)
        wks.Cells(1, 2).value = "Unread" 'Row 1 Column 2 (B)
        wks.Cells(1, 3).value = "Server" 'Row 1 Column 3 (C)

        Set rng = wks.Cells(intRowCounter + 1, intColumnCounter)

        If InStr(msg.Subject, "Server:") Then
        Set allMatches = regex.Execute(msg.Subject)
        rng.value = allMatches
        intColumnCounter = intColumnCounter + 1
        msg.UnRead = False                           

        Else
            rng.value = msg.Subject
            intColumnCounter = intColumnCounter + 1
            msg.UnRead = False
        End If

        Set rng = wks.Cells(intRowCounter + 1, intColumnCounter)
        rng.value = msg.UnRead
        intColumnCounter = intColumnCounter + 1
    End If

Next itm
Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing
Exit Sub


ErrHandler:

If Err.Number = 1004 Then
    MsgBox filePath & " doesn't exist", vbOKOnly, "Error"

    ElseIf Err.Number = 13 Then
        MsgBox Err.Number & ": Type Mismatch", vbOKOnly, "Error"
    ElseIf Err.Number = 438 Then
        MsgBox Err.Number & ": Object doesn't support this property or method", vbOKOnly, "Error"
    ElseIf Err.Number = 5017 Then
        MsgBox Err.Number & ": Error in expression", vbOKOnly, "Error"
    Else
        MsgBox Err.Number & ": Description: ", vbOKOnly, "Error"

End If


Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing

End Sub

推荐答案

VBA正则表达式不支持lookbehinds，但是在这种情况下，您不需要正向后视，您可以使用捕获组-"Server:(. *)`--然后访问第1组的值:

VBA regex does not support lookbehinds, but in this case, you do not need a positive lookbehind, you just can use a capturing group - "Server: (.*)"` - and then access Group 1 value:

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = "Server: (.*)"
regex.IgnoreCase = True
Set allMatches = regex.Execute("New Linux Server: prod-servername-a001")
If allMatches.Count <> 0 Then
    rng.Value = allMatches(0).Submatches(0)
End If

在这里

• Server: -匹配字符串Server: +空格
• (.*)-匹配并将其捕获到第1组中的零个或多个字符(除了换行符直到行尾).

• Server: - matches a string Server: + space
• (.*) - matches and captures into Group 1 zero or more characters other than a newline up to the end of line.

详细了解 捕获组 .

这篇关于在VBA中正则表达式使用正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！