Generate HTML output using excel variables using excel vba and Notepad or Shell

Question

I'm building an excel based application that produces TV wallboard visuals. The business would like to be able to preview new vizzies prior to them going live. The system works via mutliple html templates with associated javascript reading & writing to a sql table. What I would like to do is pick up the new inputs entered into formatted excel sheets & using these inputs generate the html output & write it to notepad so business can test and see preview with full layout and styling intact.

I've attempted the open notepad function and print output and file output using shell but the result has not allowed image file path along with variable image name. If I remove the image entirely not all styling functions either.

Sub htmlstr()

Dim strHTML As String

Dim Heading As String
Dim SubHeading As String
Dim ImageFile As String
Dim TextBox1 As String
Dim TextBox2 As String

Heading = Range("c_Head").Value
SubHeading = Range("c_SubHead").Value
ImageFile = Range("c_img").Value
TextBox1 = Range("c_txtbx1").Value
TextBox2 = Range("c_txtbx2").Value

Dim sFile As String

sFile = ActiveWorkbook.Path & "\WallboardPreview.html"
Close

Open sFile For Output As #1
Print #1, "<!DOCTYPE html>"
Print #1, "<head>"
Print #1, "<meta charset=""UTF-8"">"
Print #1, "<title>Preview</title>"
Print #1, "</head>"
Print #1, "<html lang=""en"">"
Print #1, "<body>"
Print #1, "<h1 class=""h1"">" & Heading & "</h1>"
Print #1, "<h2 class=""h2"">" & SubHeading & "</h2>"
Print #1, "<img src=""ImageFile"" class=""img"" align=""right"" alt=""There should be an image here but it seems to have disappeared. Gremlins in the machine, but I'm sure it'll be back soon"">"
Print #1, "<p class=""container1"">" & TextBox1 & "</p>"
Print #1, "<p class=""container2"">" & TextBox2 & "</p>"
Print #1, "<p class=""footer"">Scrolling Message</p>"
Print #1, "</body>"
Print #1, "</html>"
Print #1, "<style>"
Print #1, "body{background: rgb(255,255,255);padding: 0; margin: 0;}"
Print #1, ".h1{font-family: calibri, arial; font-size: 67px; color: rgb(27,29,81); margin-top: 0; margin-bottom: 0v; margin-left: 10px;}"
Print #1, ".h2{font-family: calibri, arial; font-size: 40px; color: rgb(27,29,81); margin-top: 0; margin-left: 10px;}"
Print #1, ".img{background: rgb(255,255,255); margin-top: 10px; margin-right: 10px; width: 535px; height: 705px; border: 10px solid;}"
Print #1, ".container1{margin-top: 0; margin-bottom: 0; margin-left: 0; margin-right: 1px; padding: 10px; font-family: calibri, arial; font-size: 50px; color: rgb(0,0,0);}"
Print #1, ".container2{margin-top: 10px; margin-bottom: 10px; margin-left: 0; margin-right: 1px; padding: 10px; font-family: calibri, arial; font-size: 50px; color: rgb(0,0,0);}"
Print #1, ".footer{position: fixed; left:0; bottom: 0; width: 1920px; font-size: 60px; background-color: gray; color: white; text-align: center; font-family: arial;}"
Print #1, "</style>"
Close
Shell "hh " & vbLf & sFile, vbMaximizedFocus

End Sub

The expected output should be as below:

<!DOCTYPE html>
<head>
<meta charset="UTF-8">
<meta http-equiv="refresh" content="20">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Preview</title>
</head>
<html lang="en">
<body>
<h1 class="h1">CPC & DCA</h1>
<h2 class="h2">Focus on the following information re inbound calls</h2>
<img src="\\i00\Test\AMUWB\CommonImageFiles\test_1.png" class="img" align="right" alt="There should be an image here but it seems to have disappeared. Gremlins in the machine, but I'm sure it'll be back soon">
<p class="container1">Some text here...</p>
<p class="container2">Some text here...</p>
</body>
</html>
<style>
body{background: rgb(255,255,255);padding: 0;margin: 0;}
.h1{font-family: calibri, arial;font-size: 7vh;color: rgb(27,29,81);margin-top: 0vh;margin-bottom: 0vh;margin-left: 10px;}
.h2{font-family: calibri, arial;font-size: 4vh;color: rgb(27,29,81);margin-top: 0vh;margin-left: 10px;}
.img{background: rgb(255,255,255);margin-top: -30px;padding: 1vh;width: 28vw;height: 80vh;}
.container1{margin-top: 0vh;margin-bottom: 0vh;padding: 1vh;font-family: calibri, arial;font-size: 7vh;color: rgb(0,0,0);}
.container2{margin-top: 1vh;margin-bottom: 1vh;padding: 1vh;font-family: calibri, arial;font-size: 7vh;color: rgb(0,0,0);}
</style>

See https://stackoverflow.com/questions/10098802/vba-variable-in-a-range-inside-a-formula - double quotes `"` must be escaped (doubled `""`) inside string literals. — Mathieu Guindon, Sep 27 '19 at 15:23
Thanks Mathieu, I just noticed that & implemented. Unfortunately I dont think its possible for the file image to be retrievable, nor are viewport width and height includeding the meta tag but thats a deal breaker as I essentially just want to convey a general preview of the content. Unless yourself or anybody else has more insight. — GpioNelson, Sep 27 '19 at 15:32
To be honest I have a very hard time understanding what your question or the problem actually is. The missing quote escapes should mean your code doesn't even get to compile, and yet you make no mention whatsoever of any compiler error. — Mathieu Guindon, Sep 27 '19 at 15:34
Apologies, I've edited my code now with the functioning syntax. It now compiles & produces a HTML mock-up giving the end user a preview of a vizzie before they choose to make it live. Ideally I would have liked to show the image they have chosen but I can't see a way to get the full file & the variable image title to work. — GpioNelson, Sep 27 '19 at 15:57
`Print #1, ""` is working in my test. I think use of **Chr(34)** instead of "" is the trick. — Ahmed AU, Oct 01 '19 at 03:16

Generate HTML output using excel variables using excel vba and Notepad or Shell

0 Answers0