Excel VBA宏-从where子句中提取值 [英] excel vba macro - extract value from where clause

问题描述

在excel工作表输入"的columnA中，我有以下内容(每行在工作表的新行中):

In columnA of excel sheet 'Input' I have the following (with each line being on a new row in the sheet):

    update my_table
    set time = sysdate,
    randfield1 = 'FAKE',
    randfield5 = 'ME',
    the_field8 = 'test'
    where my_key = '84'
    ;
    update my_table
    set time4 = sysdate,
    randfield7 = 'FAeKE',
    randfield3 = 'MyE',
    the_field9 = 'test'
    where my_key = '37';

我正在尝试创建一个新的工作表输出"，该输出仅在columnA中包含以下值，但是我不知道如何在-> where my_key之后的引号之间提取位:

I'm trying to create a new sheet 'output' that only contains the following values in columnA but I don't know how to extract the bit in between the quotes after --> where my_key:

84
37

一些注意事项:能够在工作表输入"的单元格B1中指定字段名称非常好，在此示例中为my_key.

Some notes: it would be great to be able to specify the fieldname in cell B1 of sheet 'input', in this example it would be my_key.

以前，我一直使用过滤器列手动执行此操作，其中文本包含"where"，然后在等号后去除所有内容，然后对单引号和; s进行查找/替换.有没有人能够通过一个按钮单击宏来实现这一目标?

Previously, I've been doing this manually using filter column where text contains 'where' then stripping out everything after the equals then doing a find/replace on single quotes and ;s. Has anyone been able to achieve this with a single button click macro?

推荐答案

虽然使用筛选"或查找"非常有效，但我认为使用变体数组保存输入表的所有值不会有太大区别，将使用InputB1中的字段名称针对正则表达式进行测试，匹配的任何数字部分都将转储到A列输出中.

While using Filtering or Find is very efficient I don't think you will see much difference in using a variant array to hold the all values for your Input Sheet, to be tested against a regex using a fieldname in InputB1, with any numeric portions of the match being dumped to Column A Output.

Sub VarExample()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim X
Dim Y
Dim lngRow As Long
Dim objRegex
Dim objRegexMC
Set ws1 = ActiveWorkbook.Sheets("Input")
Set ws2 = ActiveWorkbook.Sheets("Output")
Set objRegex = CreateObject("vbscript.regexp")
objRegex.Pattern = ".+where.+" & ws1.[b1] & ".+\'(\d+)\'.*"
 X = ws1.Range(ws1.[a1], ws1.Cells(Rows.Count, "A").End(xlUp)).Value2
ReDim Y(1 To UBound(X, 1), 1 To UBound(X, 2))
For lngRow = 1 To UBound(X, 1)
    If objRegex.test(X(lngRow, 1)) Then
    Set objRegexMC = objRegex.Execute(X(lngRow, 1))
        lngCnt = lngCnt + 1
       Y(lngCnt, 1) = objRegexMC(0).submatches(0)
    End If
Next
ws2.Columns("A").ClearContents
ws2.[a1].Resize(UBound(Y, 1), 1).Value2 = Y
End Sub

这篇关于Excel VBA宏-从where子句中提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！