英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论