Extract strings from a text file using VBA

Question

I have a text file which contains a list of variable each one with the correspondent value. It has the following structure

% Author: Name Surname

% Date:  /../..

% Other things ...

%----------------------------

% Output:

% Var1 = [1 0.5 5][8 5 0.9]

% Var2 = The signal is one from 0 sec to 0.5 sec; then its value increase ...
the description could span more than one row...

% Var3 = [...][...]

% Var4 = The signal is ...

% ...

% ------------------------------------------

Until now I've extracted the entire file into a strings from which I've extracted in turn the Author and other general infos.

What I have to do at this point is Extract the part of the file after %Output: by copying each variable name and value into a word table using VBA.

The table has two columns (var_name, var_value) and should has as many rows as the variables extracted are.

Once I will have each var and its value into strings, the VBA code to write these strings into the table it's straitforward.

Due to the particular structure of the document's part containing the outputs, I don't know how to extract var and its value.

I think I could extract the variable name first and then use these as bounds for the next search, I mean, suppose I had got var2 an var3 somehow, I could write some code to get everything beween them and assign it to a string:

StringValueVar2 = The signal is one from 0 sec to 0.5 sec; then its value increase ...

the description could span more than one row...

%

then clean this string the Remove function from % and spaces.

So, how can I proceed?

LocEngineer · Accepted Answer · 2015-10-26 09:43:51Z

1

Looks like a job for Regex. I've made a liitle example in Word VBA that will read your file and spit out each variable name and its value; no further cleaning of "%" or "=" needed. References to Microsoft VBScript Regular Expression 5.5 and Microsoft Scripting Runtime

Beware: I have assumed that your entire variables in your file look like this:

%[space]Variable[space]=[space]Value

Will this do the trick?

Sub ParseMyFile()
Dim rex As RegExp
Dim txt As String, fil As String, fso As FileSystemObject
Dim mcol As MatchCollection, m As Match

fil = "[pathToYourTxtFile]"
Set fso = New FileSystemObject

Set rex = New RegExp
rex.Pattern = "(\%\s)([\w\d]+?)(\s\=\s)([\w\W\s\S\d\D\r\n]+?)(?=\%|\Z)"
rex.Global = True
rex.Multiline = False
txt = fso.OpenTextFile(fil).ReadAll

Set mc = rex.Execute(txt)
For Each m In mc
    Debug.Print "Variable: " & m.SubMatches(1)
    Debug.Print "Value: " & m.SubMatches(3)
Next m

End Sub

edited Oct 26, 2015 at 9:43

answered Oct 23, 2015 at 9:42

LocEngineer

2,9271 gold badge18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vinx Over a year ago

I've tried the code and it works but finds just the first occurence of Var, Value. Maybe I did not understand how to use it. Does the For Each statement iterate through the entire text and print each occurrence of Var, Value, doesn't it?

LocEngineer Over a year ago

Yes, exactly. mcholds all matches, it is a MatchCollection. Try the edited code, see if that changes anything. Weird.

Collectives™ on Stack Overflow

Extract strings from a text file using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related