derbva
03-10-2022, 01:07 AM
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>)
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>)