Reading Inputs from external file

[imatrox05]

Hi,

I am currently building a VBA app to help me convert a bunch of HTML.

i need to do a lot of "Find & Replace" but i do not want to hardcore the search and replace strings into the VBA coz the requirement changes from time to time.

I want to have a external file that contains the search keyword folowed by some seperator like "||" and then the replace keyword

eg:

<h1> || <header1>
<h2> || <header2> and so on...

When ever i run the tool i specify the input file and then the conversion should start.

How do i go about achieving that.

Please Help.

Thanks in advance

[DKenny]

I am currently building a VBA app

What are you building it in? Access, Excel, Word...

BTW, you decision to go with a file based approach is the correct one. But I need to know you app of choice before giving any more advice.

[imatrox05]

Word

[DKenny]

OK
The best way to do this is to use a TextStream from a FileSystemObject. Here's some code that will achieve this.

You will need to change the value of sPath to the fully qualified path for your reference file.
Also, make sure you have a reference to the MS Scripting Runtime library in your project, or else you will not be able to access the FileSystemObject.

Sub imatrox05_replace()
'Note:  You will need a reference to the MS Scripting
'       Runtime library to use the FileSystemObject
Dim fsReplaceData As Scripting.FileSystemObject
Dim sText As TextStream
Dim sPath As String
Dim sFullLine As String
Dim aLineArray() As String
    
    'The fully qualified path to your lookup file
    sPath = "C:\data\testreplace.txt"
    
    'Create a new FileSystemObject
    Set fsReplaceData = New Scripting.FileSystemObject
    
    'Create a text stream from your FileSystemObject
    Set sText = fsReplaceData.OpenTextFile(sPath, ForReading, False, TristateUseDefault)
    
    'Loop through each line in the text stream
    Do While Not sText.AtEndOfStream
        
        'Get the complete line of text
        sFullLine = sText.ReadLine
        
        'split the line of text into a 1 dimensional array
        'using || as the delimeter and
        'only taking the first 2 values from the line
        aLineArray = Split(sFullLine, "||", 2)
        
        'Within the Active document find all instances of
        'the first value in the Array and replace with
        'the second value in the Array
        With ActiveDocument.Range.Find
            .Text = aLineArray(LBound(aLineArray))
            .Replacement.Text = aLineArray(UBound(aLineArray))
            .Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue, MatchWholeWord:=True
        End With
    Loop
    
    Set sText = Nothing
    Set fsReplaceData = Nothing
End Sub

[imatrox05]

Thanks DKenny