VBA Find string and add hyperlink

[derbva]

Hey, first of all, I hope this falls under Excel category.

Version: Running on the Microsoft 365 MSO.

What I want to do: I have several local HTML text files in a single directory. I wish to find all certain strings within those files and add hyperlinks to each of those strings. Hyperlinks will lead to other files.

This macro will be written in Excel and in there I have combined 2 columns for what I think are necessary. Column A for Hyperlinks.Add Anchors. These would be the strings I want to find. Data in column A would be strictly numbers and letters e.g. "ABC123ZYX". Column B for filepaths as the address for hyperlinks.

I have very little to none experience with VBA, so I can not provide useful code. However I can provide the code I have so far and how I think it should be done.

Any and all help is deeply appreciated.


Sub AddHyperlinks()Dim objFSO As Object
Dim objFil As Object
Dim objFil2 As Object
Dim StrFileName As String
Dim StrFolder As String
Dim strAll As String




Set objFSO = CreateObject("scripting.filesystemobject") 'enable microsoft scripting runtime
StrFolder = "C:\...\" 'choose folder to go through
StrFileName = Dir(StrFolder & "*.html") 'choose extension to edit HTML/DOCM


Do While StrFileName <> vbNullString 'looping through the files
Set objFil = objFSO.opentextfile(StrFolder & StrFileName) 'Set objFil as a TextStream object that can be used to access the file
strAll = objFil.readall 'returns one long string containing everything from the looped file
objFil.Close 'closing the TextStream document
Set objFil2 = objFSO.createtextfile(StrFolder & StrFileName) 'Creates a specified file name and returns a TextStream object
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row 'Loop through the rows

'Loop through all the words and find that in the opened document
'Add hyperlink to that word with Column B addresses

Next i
objFil2.Close
StrFileName = Dir
Loop


End Sub

EDIT: I should add that regardless of files being HTML format, they can be opened with Word and they behave similar to Word docs. It does not open as source code, and hyperlinks should be added with Word methods, instead of HTML hyperlinking ( <a href="url">link text</a>)

[arnelgp]

here is a small demo.

[derbva]

Hi,

many thanks! This seems to work wonders so far with Word docs. Will have to demo further when I have the time. Tried it with HTML files and couldn't get it to work. But that'll be easily fixed by converting to Docs before running a script and then converting back. Will also probably try to modify it so I can simply run the script and it'll go through each file on it's own.

Will update once I've tinkered around more

[derbva]

Hey,

I've gotten back to the issue. I can run the macro once, after which it will keep the Word Process open in Task Manager. When trying to run for second time it will give the Error 4248: "This command is not available because no document is open." If I were to manually shut down the word process, I would get the Error 462: "The remote server machine does not exist or is unavailable". This problem goes away every time I shut down the Excel and re-open it to run macro again.

This also seems to be the reason why I previously said I couldn't get it to work with HTML files. It works fine for the first iteration.

Any ideas regarding these errors?

[arnelgp]

on ProcessFile sub, there is a variable app (word.application).
at the end of the sub it is setting it to Nothing.
close it before setting to Nothing:

app.Quit
Set app = Nothing

[derbva]

This will get rid of the Error 4248, but Error 462 still remains when running several times.

[arnelgp]

i added code to ThisWorkbook object.
also modify some codes.

[derbva]

Thank you! You have done a marvelous job with this. With some small personal tweaks, this now does exactly what I want.