1

We have .htm files we would like to edit with an Excel macro.

Here's what I have so far, which is an attempt to open the file, replace some placeholders inside the HTML body, and then save the file as an HTML file:

Sub SaveCustomerCountHTML()

Dim customerCountHTML As HTMLDocument
Dim customerCount As String
Dim customerPercentage As String
Dim strHTML As String


Set customerCountHTML = CreateObject("O:\00.Department Documents\06.Status Board Updates\Daily Sales\Templates\CUSTOMER COUNT.htm")

strHTML = customerCountHTML.HTMLbody
customerCount = "55"
customerPercentage = "2"

strHTML = Replace(customerCountHTML.HTMLbody, "%CUSTOMERCOUNT%", customerCount)
strHTML = Replace(customerCountHTML.HTMLbody, "%CUSTOMERPERCENTAGE%",     customerPercentage)

customerCountHTML.SaveAs Filename:= _
"O:\00.Department Documents\06.Status Board Updates\Daily     Sales\Templates\CUSTOMER COUNT2.htm", _
FileFormat:=xlHtml, _
CreateBackup:=False
End Sub

We definitely don't want to open the file in Internet Explorer - we just want to edit the placeholders in the body and re-save to another folder it without seeing what it's doing.

I receive an error message "ActiveX component can't create object" on the "Set customerCountHTML" line. We were also getting an "Object doesn't support this property or method" error for the HTMLBody lines, even though we included the Microsoft HTML Object Library in our references.

Again, the intention/attempt is to simply open the HTML file as an object, replace text in that file, and save the file. All of this without needing to see anything opening or closing. Most of the answers online include Internet Explorer or Outlook in some way, and we simply want to edit the file.

Community
  • 1
  • 1
  • 1
    The sample above is doing replacements on the entire `HTMLBody`. Why not just open it as a text file? – Comintern Sep 18 '16 at 18:19
  • I'll give that a shot. Thanks for the tip, I guess it doesn't really need to be opened as an HTML, just saved as one. –  Sep 18 '16 at 18:29
  • Take a look at this: http://stackoverflow.com/questions/18286598/read-local-html-file-into-string-with-vba – Miqi180 Sep 18 '16 at 18:54
  • Sorry, Miqi, but that post was extremely convoluted and the accepted answer only led to a dead end. –  Sep 24 '16 at 18:16
  • Thanks, Comintern, your comment and the post below led us eventually to a solution! –  Sep 24 '16 at 18:17

1 Answers1

2

As @Comintern suggests, HTML files are nothing more than text files. You can open them and work on them as text files, and you can save them as text files, but with an .htm/html extension.

However, working with text files doesn't give you any contextual information, so you can't easily distinguish a table tag, the use of table in css or javascript or attributes, or in actual content.

If you know the exact text you want to replace is unique and reliably found without any False Positives, then replacing the text is good enough. But if you need to be able to find and replace specific parts of the HTML that might be ambiguous, then it might be worth ensuring that the file is XHTML compliant, and opening the file with the MSXML type library. You can then work with the file as an XML Document including a node-tree and all of the MSXML features for finding nodes.

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • I will mark this as an answer because it led us in the correct direction, although as an answer it lacks specificity and introduces confusing elements concerning the tags which is irrelevant information for our purposes here. The real solution it led us to can be found in the accepted answer here: http://stackoverflow.com/questions/19371990/how-do-i-replace-a-string-in-a-line-of-a-text-file-using-filesystemobject-in-vba . If anyone needs to replace exact text (we used placeholders) in an htm/html file and resave it the accepted answer in that post worked with our htm/html files. –  Sep 24 '16 at 18:12
  • Consider **not** treating HTML as text files but as markup files that can be parsed using DOM properties. And if HTML is well-formed like XML, you can modify source files with XSLT. Please post a html sample for demonstration. – Parfait Oct 02 '16 at 20:56