CODEWITHSUNDEEP
stringvba
vinx
vinx

Reputation: 89

Extract strings from a text file using VBA

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?

Upvotes: 1

Views: 926

Answers (1)

LocEngineer
LocEngineer

Reputation: 2917

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

Upvotes: 1

Related Questions