Can word wildcards or regular expressions be used inside InStr function in Excel VBA?

Question

I have this code here that sets a range to whole document contents and within that range it searches for the text = "cenę brutto w kwocie " - which I expect to be there. The code's purpose is to find and extract the apartment price from the word document and to write it into the excel cell.

My question pertains the 11th line of code endPos = InStr(startPos, rng, ",00zł") : How to change ",00zł" into "[0-9]{3},[0-9]{2}zł"?

I know these wildcards can be used with range.find method, but can these wildcards be passed into the InStr function?? These strings "cenę brutto w kwocie " and ",00zł" are very close to each other in very long word document. They're used to narrow the rng range from initially whole document into very small range where the apartment price can be found and retrieved. I need to change it because sometimes the apartment prices have currency decimals in them.

 'Assigning object variables and values
    Set wordApp = GetObject(, "Word.Application")      
    Set excelApp = GetObject(, "Excel.Application")    
    Set wordDoc = wordApp.ActiveDocument
    Set mySheet = Application.ActiveWorkbook.ActiveSheet
    Set rng = wordApp.ActiveDocument.Content
    Set scndRng = ActiveSheet.Range("A10:J40").Find("cena", , xlValues)
    textToFind1 = "KRS 0000609737, REGON 364061169, NIP 951-24-09-783,"  
    textToFind2 = "- ad."  
    textToFind3 = "Tożsamość stawających"
    textToFind4 = "PESEL"
    TextToFind5 = "cenę brutto w kwocie łącznej"
    textToFind6 = "cenę brutto w kwocie "       

          Set rng = wordApp.ActiveDocument.Content  'this command without "set" will destroy the document's formating;
               With rng.Find
                .Text = textToFind6      '="cenę brutto w kwocie "
                .MatchWildcards = False
                .MatchCase = False
                .Forward = True
                .Execute
                  If .Found = True Then
                     Set rng = wordApp.ActiveDocument.Content
                     startPos = InStr(1, rng, textToFind6)      'we're looking 4 "cenę brutto w kwocie"
                     endPos = InStr(startPos, rng, ",00zł")     'here we get 47380, we're looking 4 ",00zł"
                     startPos = startPos + Len(textToFind6)     ' + 21 characters     'now start position is reassigned at 47331.
                     Debug.Print Replace(Mid(rng, startPos, endPos - startPos), ".", "")
                     price = Replace(Mid(rng, startPos, endPos - startPos), ".", "")
                     price = Trim(price)
                     Debug.Print price
                  End If
                End With
            Debug.Print scndRng.Address
            scndRng.Offset(0, 1) = price

Previously all the apartment prices where rounded into whole currency, therefore finding ",00zł" and passing it's long position into endPos variable. How can I fit "[0-9]{3},[0-9]{2}zł" into InStr or any other function that will pass the long value into the endPos variable.

Check this: [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). You can use Regular Expressions but not in `InStr`. Anyway RegEx would completely replace the `InStr`. — Pᴇʜ, May 15 '20 at 10:04

score 1 · Accepted Answer · 2020-05-15T17:04:59.773

No, you can't use regular expressions in InStr, and in this case the "wildcard-matching" VBA operator "Like" won't help either.

You could consider using a number of separate Word Finds, e.g. something like this. This one assumes that if you have

text_to_find_1

no amount

text_to_find_1

amount_x

text_to_find_1

amount_y

then you want to find an amount (or "no amount") for each text_to_find_1. If you don't need that you can just use two ranges and a simpler loop.

I have to use chrw() here for the accented characters.

Sub getAmountsBetween2EqualTexts()
Dim rng1 As Word.Range
Dim rng2 As Word.Range
Dim rng3 As Word.Range
Dim findtext1 As String
Dim findtext2 As String
Dim wordApp As Word.Application
Set wordApp = Word.Application
findtext1 = "cen" & ChrW(281) & " brutto w kwocie "
findtext2 = "[0-9]{3},[0-9]{2}z" & ChrW(322)

Set rng1 = wordApp.ActiveDocument.Content
Set rng2 = wordApp.ActiveDocument.Content
Set rng3 = wordApp.ActiveDocument.Content

' Set up the middle and end finds

With rng2.Find
  .ClearFormatting
  .Text = findtext2
  .MatchWildcards = True
  .Forward = True
  .Wrap = wdFindStop
End With

With rng3.Find
  .ClearFormatting
  .Text = findtext1
  .MatchWildcards = False
  .Forward = True
  .Wrap = wdFindStop
End With

With rng1.Find
  .ClearFormatting
  .Text = findtext1
  .MatchWildcards = False
  .Forward = True
  .Wrap = wdFindStop
  While .Execute
    With rng3
      .Start = rng1.End
      .End = wordApp.ActiveDocument.Content.End
      With rng2
        If rng3.Find.Execute Then
          .Start = rng1.End
          .End = rng3.Start
        Else
          .Start = rng1.End
          .End = wordApp.ActiveDocument.Content.End
        End If
        If .Find.Execute Then
          ' process these values as needed
          Debug.Print .Text
        Else
          Debug.Print "Amount not found"
        End If
      End With
    End With
   Wend
End With

Set rng3 = Nothing
Set rng2 = Nothing
Set rng1 = Nothing
End Sub

Can word wildcards or regular expressions be used inside InStr function in Excel VBA?

1 Answers1