Replace string that has a variable number

[Clutch Cargo]

I am attempting to replace a string of text which includes a number with new text which also includes new numbers. Such as:

Old strings

"NUM COLUMNS=21659"
"NUM ROWS=16244"

and replace those with:

"NUM COLUMNS=10831"
"NUM ROWS=8122"

The issue is the numbers on the Old strings can vary. The numbers are part of the string and not considered integers. The new numbers are constant so that is not an issue. Here is what I have so far but not complete as it does not include the old numbers:

Option Explicit

Sub EditGCP()
	Dim objFSO			: Set objFSO 	 = CreateObject("Scripting.FileSystemObject")
	Dim sFolder			: sFolder 		 = "P:\Temp"
	Dim OldSizeColumns	: OldSizeColumns  = "NUM COLUMNS="
	Dim OldSizeRows		: OldSizeRows	 = "NUM ROWS="
	Dim OldSizeColumns	: NewSizeColumns = "NUM COLUMNS=10831"
	Dim OldSizeRows		: NewSizeRows	 = "NUM ROWS=8124"
	
	Dim inFile, tempFile, strText, strNewText, strNewText1
	Const ForReading = 1
	Const ForWriting = 2
	Const OverwriteExisting = TRUE

	For Each infile In objFSO.GetFolder(sFolder).Files
		If (objFSO.getExtensionName(inFile.path))="txt" Then
			
			Set tempFile = objFSO.OpenTextFile(infile, 1)
			strText = tempFile.ReadAll
			tempFile.Close
		
			strNewText = Replace(strText, OldSizeColumns, NewSizeColumns)
			strNewText1 = Replace(strNewText, OldSizeRows, NewSizeRows)
			
			Set tempFile = objFSO.OpenTextFile(Infile, 2)
			tempFile.Write strNewText1
			tempFile.Close	
		Else
			End If
	Next	
End Sub

I have been reading up on using a pattern to capture any numbers after "NUM COLUMNS=" and "NUM ROWS=" using something like:

Dim r
Set r = New RegExp
r.Pattern="NUM COLUMNS=\(\d+\)"

or

Dim r
Set r = New RegExp
r.Pattern="NUM COLUMNS=\[0-9]+"

and

strNewText = r.Replace(OldSizeColumns, NewSizeColumns)

However when I apply them it deletes all text in the file and just leaves "NUM COLUMNS="!

I thought maybe one could use a 'replace line' command of some type since "NUM COLUMNS=" is always on line 24 and "NUM ROWS=" is always on line 25 but could not find enough information yet to make an attempt.

Any help would be most appreciated.

[Victor Bravo VI]

Try this:

Option Explicit

Private Sub EditGCP()
    Const SHCONTF_NONFOLDERS = &H40&, sFolder = "P:\Temp"
    Dim oFolderItem, oFolderItems3, RE, sText

    Set RE = CreateObject("VBScript.RegExp")
    RE.Global = True
    RE.MultiLine = True

    Set oFolderItems3 = CreateObject("Shell.Application").NameSpace(sFolder).Items
    oFolderItems3.Filter SHCONTF_NONFOLDERS, "*.TXT"

    With CreateObject("Scripting.FileSystemObject")
        For Each oFolderItem In oFolderItems3
            RE.Pattern = "NUM COLUMNS=\d+"
            sText = RE.Replace(.OpenTextFile(oFolderItem.Path).ReadAll, "NUM COLUMNS=10831")

            RE.Pattern = "NUM ROWS=\d+"
           .CreateTextFile(oFolderItem.Path).Write RE.Replace(sText, "NUM ROWS=8124")
        Next
    End With
End Sub

---

References

• Introduction to Regular Expressions (Scripting)
• Regular Expression Syntax (Scripting)
• Regular Expression (RegExp) Object
• Shell Objects for Scripting and Microsoft Visual Basic
• FolderItems3.Filter method
• CreateTextFile Method
• OpenTextFile Method
• TextStream Object

[Clutch Cargo]

Victor Bravo,

Just had to reply that your code worked flawlessly the first time! In fact, I am a bit in awe looking over your code how sleek and concise it is. So I am studying it to understand better and improve my own coding practices. I must ask if you don't mind:

1. Can you explain the Constant 'Const SHCONTF_NONFOLDERS = &H40&' .... SHCONTE and &H40& as I have not seen those terms/commads?
2. I see 'r' or 'RE' when using Pattern. Does it stand for "repeating"?

Many thanks

[Clutch Cargo]

Sorry, a double-post.

[Victor Bravo VI]

1. Can you explain the Constant 'Const SHCONTF_NONFOLDERS = &H40&' .... SHCONTE and &H40& as I have not seen those terms/commads?

The SHCONTF_NONFOLDERS constant comes from the _SHCONTF Enumeration, which are the only allowed values for the first parameter of the FolderItems3.Filter method. I chose the Shell object because it is slightly more efficient than the FileSystemObject.

The &H40& is a number in hexadecimal form. The &H prefix indicates that the number is a hexadecimal while the & suffix tells VBScript to treat the number as a Long subtype (by default, numeric literals from -32,767 to 32,767 are treated as Integers).

EDIT

I just did some tests using the TypeName function and it appears that VBScript ignores the & suffix when it is appended to a hex number. VBScript raises an error, however, when it is affixed to a decimal number (e.g., 123&). So, just remove that & from the hex number; it is useless apparently.

2. I see 'r' or 'RE' when using Pattern. Does it stand for "repeating"?

Nope. RE actually stands for Regular Expression.

Sorry, a double-post.

This is a known issue (see this, this or this). Some of the proposed solutions are either ignore the wait 30 seconds warning or click Go Advanced instead of Post Quick Reply.