Insert text strings into HTML source code using VBA

[batyushki]

I have a product database in Excel. I want to update HTML pages based on changes in the product database. I've built the tags for each product entry as strings in Excel, and now I need to insert them at specific points in the HTML source code. How can I open HTML as source code rather than as a page in Excel 2000? Every time I try to either paste the source code into Excel or open an HTML document, it automatically shows the displayed page rather than the source code behind it. I need to get at the source code.

[Scott Huish]

Not sure if this will work in Excel 2000 or not. It was tested with Excel 2002(XP).

This example reads an HTML file into a good sized TextBox that has the MultiLine property set to True and the ScrollBars property set to fmScrollBarsVertical.

Sub readhtml()
TextBox1.Text = ""
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("C:\test.html")
Set ts = f.OpenAsTextStream(1, 0)
s = ts.readall
TextBox1.Text = s
ts.Close
End Sub

[Jon Peltier]

That'll work in 2000 and 97, as long as the machine has windows script host enabled (some IT departments turn it off).

Here's an alternate technique that I swiped from somewhere before WSH. It loads the file into a column of cells in a new workbook.

Sub inputTXTfile()
' input text file line by line without interpreting html tabs
' open new sheet first and select home cell

Dim linein As String, fh As Integer
Dim FileName As String, lineNum As Double

FileName = Application.GetOpenFilename
If FileName = "False" Then Exit Sub
Application.ScreenUpdating = False
lineNum = 0
fh = FreeFile
Workbooks.Add

Open FileName For Input As fh
Do Until EOF(fh)
Line Input #fh, linein
ActiveCell.Offset(lineNum, 0) = linein
lineNum = lineNum + 1
Loop
Close #fh

Application.ScreenUpdating = True
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

[batyushki]

Jon, your open statement and loop worked great. I used Line Input # to read in the text, and Print # to put it back, which solved the formatting problems. Using other methods it was putting quotes around each line.

Thanks both of you for the help!

[razzandy]

Hi

I am trying to do the same as you and wondered if you could explain the TAGS in your Main Excel Database. Maybe you could send me an example. I have used Jon's code to open my HTML Page (Template) but now I want to be able to produce an SKU Database with the SKU number, Title, Description and Image URL's which I can then select an SKU/Row in this Database and hit a button to combine the Template and SKU information into a completed Web Page ready for publishing to the web.

Hope you can help

Cheers

Ryan

I have a product database in Excel. I want to update HTML pages based on changes in the product database. I've built the tags for each product entry as strings in Excel, and now I need to insert them at specific points in the HTML source code. How can I open HTML as source code rather than as a page in Excel 2000? Every time I try to either paste the source code into Excel or open an HTML document, it automatically shows the displayed page rather than the source code behind it. I need to get at the source code.