从正则表达式中获取SubMatches,然后填充Excel单元格。

huangapple go评论70阅读模式
英文:

Getting SubMatches from RegEx return from Email to populate cells in Excel

问题

以下是您提供的内容的中文翻译:

我需要从Outlook中的选定电子邮件下载数据(货币汇率),然后将其加载到Excel中的特定单元格中。 Marko是从Excel中运行的。我使用函数 regexp 来检索数据。
我已经定义了两种模式,用于搜索数据,宏找到了它们,但没有将它们分配给单元格。我尝试分配找到的数据的方式是: matches.Item(0).SubMatches

Sub data_from_email()

Dim olItem As Outlook.MailItem
Dim olApp As Outlook.Application
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim KSell, KBuy, KAvr As String '货币汇率的值)
Dim sText As String
Dim Reg1, Reg2 As Object
Dim M1 As Object
Dim m As Object

Set wb = ThisWorkbook
Set xlSheet = wb.Sheets("name")
Set olApp = New Outlook.Application

sText = olItem.Body '电子邮件内容的赋值

'搜索值的模式变体1(以点为分隔符的货币汇率)
Set Reg1 = CreateObject("vbscript.regexp")
With Reg1
    .Pattern = "[0-9]\.[0-9][0-9][0-9][0-9]"
    .Global = True
    .IgnoreCase = True
End With
If Reg1.Test(sText) Then
        Set m1 = Reg1.Execute(sText)
        For Each m In m1
        KSell = m.SubMatches(2) 
        KBuy = m.SubMatches(1) 
        KAvr = m.SubMatches(0)
        Next
    End If

'搜索值的模式变体2(以逗号为分隔符的货币汇率)
Set Reg2 = New RegExp
With Reg2
    .Pattern = "[0-9]\,[0-9][0-9][0-9][0-9]"
    .Global = True
    .IgnoreCase = True
End With
If Reg1.Test(sText) Then
        Set m1 = Reg1.Execute(sText)
        For Each m In m1
        KSell = m.SubMatches(2) 
        KBuy = m.SubMatches(1)
        KAvr = m.SubMatches(0) 
        Next
    End If

End Sub

请注意,您在代码中使用的变量和注释已经翻译为中文。如果您需要进一步的帮助,请随时提问。

英文:

I need to download data (currency rates) from a selected email in outlook and load them into specific cells in excel. Marko is run from excel. I'm using a function regexp to retrieve the data.
I have defined 2 types of patterns with which I search for data, the macro finds them but does not assign them to cells. The way I tried to assign the found data is: matches.Item(0).SubMatches.

Sub data_from_email()

Dim olItem As Outlook.MailItem
Dim olApp As Outlook.Application
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim KSell, KBuy, KAvr As String 'value of currency rates)
Dim sText As String
Dim Reg1, Reg2 As Object
Dim M1 As Object
Dim m As Object

Set wb = ThisWorkbook
Set xlSheet = wb.Sheets("name")
Set olApp = New Outlook.Application


sText = olItem.Body 'assignment of email content

'pattern of value searched variant 1 (currency rates with a dot as a separator)
Set Reg1 = CreateObject("vbscript.regexp")
With Reg1
    .Pattern = "[0-9]\.[0-9][0-9][0-9][0-9]"
    .Global = True
    .IgnoreCase = True
End With
If Reg1.Test(sText) Then
        Set m1 = Reg1.Execute(sText)
        For Each m In m1
        KSell = m.SubMatches(2) 
        KBuy = m.SubMatches(1) 
        KAvr = m.SubMatches(0)
        Next
    End If

'pattern of value searched variant 2 (currency rates with a comma as a separator)
Set Reg2 = New RegExp
With Reg2
    .Pattern = "[0-9]\,[0-9][0-9][0-9][0-9]"
    .Global = True
    .IgnoreCase = True
End With
If Reg1.Test(sText) Then
        Set m1 = Reg1.Execute(sText)
        For Each m In m1
        KSell = m.SubMatches(2) 
        KBuy = m.SubMatches(1)
        KAvr = m.SubMatches(0) 
        Next
    End If


    End Sub

答案1

得分: 1

我通过反复尝试找到了解决方案。结果发现移除了 Submatches 部分就可以正常运行。以下是修正后的代码:

Sub data_from_email()

    Dim olItem As Outlook.MailItem
    Dim olApp As Outlook.Application
    Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim KSell, KBuy, KAvr As String '货币汇率的值
    Dim sText As String
    Dim Reg1, Reg2 As Object
    Dim M1 As Object

    Set wb = ThisWorkbook
    Set xlSheet = wb.Sheets("name")
    Set olApp = New Outlook.Application

    sText = olItem.Body '分配电子邮件内容

    '搜索货币汇率的模式变体1(带点作为分隔符的货币汇率)
    Set Reg1 = CreateObject("vbscript.regexp")
    With Reg1
        .Pattern = "[0-9]\.[0-9][0-9][0-9][0-9]"
        .Global = True
        .IgnoreCase = True
    End With
    If Reg1.Test(sText) Then
        Set M1 = Reg1.Execute(sText)
    End If

    '搜索货币汇率的模式变体2(带逗号作为分隔符的货币汇率)
    Set Reg2 = New RegExp
    With Reg2
        .Pattern = "[0-9]\,[0-9][0-9][0-9][0-9]"
        .Global = True
        .IgnoreCase = True
    End With
    If Reg2.Test(sText) Then
        Set M1 = Reg2.Execute(sText)
    End If

    '将数据分配给单元格
    KSell = M1(2).Value
    KBuy = M1(1).Value '找到的第二个与模式匹配的文本
    KAvr = M1(0).Value '找到的第一个与模式匹配的文本

    With xlSheet
        .Range("E3") = Replace(KSell, ".", ",") '将货币汇率值分配给单元格并将点替换为逗号
        .Range("D3") = Replace(KBuy, ".", ",")
        .Range("C3") = Replace(KAvr, ".", ",")
    End With

End Sub

这是您提供的代码的修正版,已翻译好。

英文:

I found the solution by trial and error. Turns out that removing Submatches worked. This code worked:

Sub data_from_email()

    Dim olItem As Outlook.MailItem
    Dim olApp As Outlook.Application
    Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim KSell, KBuy, KAvr As String 'value of currency rates)
    Dim sText As String
    Dim Reg1, Reg2 As Object
    Dim M1 As Object

    Set wb = ThisWorkbook
    Set xlSheet = wb.Sheets("name")
    Set olApp = New Outlook.Application


    sText = olItem.Body 'assignment of email content
    
    'pattern of value searched variant 1 (currency rates with a dot as a separator)
    Set Reg1 = CreateObject("vbscript.regexp")
    With Reg1
        .Pattern = "[0-9]\.[0-9][0-9][0-9][0-9]"
        .Global = True
        .IgnoreCase = True
    End With
    If Reg1.Test(sText) Then
        Set M1 = Reg1.Execute(sText)

    End If
    
    'pattern of value searched variant 2 (currency rates with a comma as a separator)
    Set Reg2 = New RegExp
    With Reg2
        .Pattern = "[0-9]\,[0-9][0-9][0-9][0-9]"
        .Global = True
        .IgnoreCase = True
    End With
    If Reg2.Test(sText) Then
        Set M1 = Reg2.Execute(sText)

    End If
 
    'assigning data to a cell
           
        KSell = M1(2).Value
        KBuy = M1(1).Value 'second found text matching the pattern
        KAvr = M1(0).Value 'first found text matching the pattern

        
    With xlSheet
        .Range("E3") = Replace(KSell, ".", ",") 'assigment value of currency rates to cells and change a dot to a comma)
        .Range("D3") = Replace(KBuy, ".", ",")
        .Range("C3") = Replace(KAvr, ".", ",")
    End With
 
        End Sub

huangapple
  • 本文由 发表于 2023年5月24日 23:06:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76324975.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定