Using Macros to create HTML

**DanishFk** · 11-06-2009, 08:12 PM

I have a couple spreadsheets which update using a RTD link. I then use Macros to sort it and publish a html file every 30 seconds.

I found the html macro thanks to : http://www.meadinkent.co.uk/xlhtmltable.htm

The problem is that whenever I run two sheets together, the macro that creates the html file at times gets the data off the wrong worksheet. Sometimes I only see one sheet's data being used, sometimes the other.

Both html files when published have some feature from one macro and some from the other macro. Like macro one sheet says use 1528 rows and the title for page is Relative Strength Over 750K. Other macro says use 979 rows and title for page is Relative Strength Under 750K. I would have both the sheets as like 1528 rows for with title Relative Strength Under 750K.

If I run one sheet at a time, everything works fine.

Here are the Macro codes for Sheet #1:

ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Macro1", , False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:30"), "Macro1"
End Sub

Module 1

Public dTime As Date

Sub Macro1()
dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "Macro1"
Columns("P:AH").Select
Selection.Sort Key1:=Range("AG1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Run "MakeHTM_Over"
End Sub

Module 2

Option Base 1 ' sets first array element to 1, not 0
Sub MakeHTM_Over()
' Defining a list of variables used in this program
Dim PageName As String, FirstRow As Integer, LastRow As Integer
Dim FirstCol As Integer, LastCol As Integer, MyBold As Byte
Dim TempStr As String, MyRow As Integer, MyCol As Integer
Dim MyFormats As Variant, Vtype As Integer, MyPageTitle As String

' MyFormats is an array which can contain formats for numbers
' and dates. Add one element for each table column.
MyFormats = Array("", "#,", "#,##0.00;(#,##0.00)", "0.00", "0.00", "0.00", "#,", "0.00", "0.000", "0.00", "0.00", "0.00", "#,##0.00;(#,##0.00)", "0.00", "", "", "", "", "", "", "", "0.00", "", "")

PageName = "C:\WebPages\rs_over_750k.html" 'location and name of saved file
MyPageTitle = Range("A2").Value

FirstRow = 1 ' the range of the worksheet to be
LastRow = 1528 ' converted into an HTML table
FirstCol = 16
LastCol = 39

If UBound(MyFormats) < (LastCol - FirstCol + 1) Then
MsgBox "The 'MyFormats' array has insufficient elements", vbOKOnly + vbCritical, "MakeHTM macro"
Exit Sub
End If

Open PageName For Output As #1
Print #1, "<html>"
Print #1, "<head>"
Print #1, "<title>Relative Strength Over 750K</title>"
Print #1, "<style type='text/css'>"
Print #1, "body {font-family: Verdana, sans-serif; font-size: 10pt; margin-left: 10; margin-right: 10; color: #FFFFFF; background-color: #383838; text-align: center;}"
Print #1, "td {padding: 1pt 3pt 2pt 3pt; border-style: solid; border-width: 1.5; border-color: #383838; font-size: 10pt; text-align: left;}"
Print #1, "table {border-collapse: collapse; border-width: 1.5 ; border-style: solid; border-color: #383838;}"
Print #1, "</style>"
' The next line refers to a cascading style sheet as an alternative to the <style> instructions
' Print #1, "<link rel='stylesheet' type='text/css' href='mikbasic.css'>"

Print #1, "</head>"
Print #1, "<body>"
Print #1, "<h1>" & MyPageTitle & "</h1>"
Print #1, "<table>"
For MyRow = FirstRow To LastRow
Print #1, "<tr>"

For MyCol = FirstCol To LastCol
If Cells(MyRow, MyCol).Font.Bold = True Then
MyBold = 1
Else
MyBold = 0
End If

Vtype = 0 ' check whether the cell is numeric
If IsNumeric(Cells(MyRow, MyCol).Value) Then Vtype = 1
If IsDate(Cells(MyRow, MyCol).Value) Then Vtype = 2

' if numeric and a format code has been created, apply it
If Vtype > 0 And MyFormats(MyCol - FirstCol + 1) <> "" Then
TempStr = Format(Cells(MyRow, MyCol).Value, MyFormats(MyCol - FirstCol + 1))
Else
TempStr = Cells(MyRow, MyCol).Value
End If

If MyBold = 1 Then
TempStr = "<b>" & TempStr & "</b>"
End If

If Vtype = 1 Then ' align numbers (not dates) to the right
TempStr = "<td align='right'>" & TempStr & "</td>"
Else
TempStr = "<td>" & TempStr & "</td>"
End If

' if a table cell is blank, add a space
If TempStr = " <td></td>" Or TempStr = "<td align='right'></td>" Then
TempStr = " <td> </td>"
End If

Print #1, TempStr
Next MyCol
Print #1, "</tr>"
Next MyRow

Print #1, "</table>"
Print #1, "<p><small>Last Updated: " & Format(Date, "dd mmm") & " | " & Format(Time, "ttttt") & "</small></p>"
Print #1, "</body>"
Print #1, "</html>"
Close #1
End Sub

I have attached the codes for sheet #2 as a txt file.

Would appreciate any help what so ever. Thanks!

**Andy Pope** · 11-07-2009, 07:38 AM

Looks like the code will process what ever sheet is active.
You need to make the correct sheet active when the code runs.

**DanishFk** · 11-07-2009, 10:17 AM

Originally Posted by Andy Pope

Looks like the code will process what ever sheet is active.
You need to make the correct sheet active when the code runs.

How can I do that? Can I code that into the macro only to use the Active sheet ?

**DanishFk** · 11-08-2009, 12:01 AM

Anyone knows a solution ?

**Andy Pope** · 11-08-2009, 07:47 AM

Because you are not qualifying the range references in the macro it is already using the current worksheet. I assume the problem is that sometimes the activesheet is not the one you want to convert to html.

you need to make the correct sheet active. So something like this.

Please Login or Register  to view this content.

**DanishFk** · 11-09-2009, 12:24 PM

Originally Posted by Andy Pope

Because you are not qualifying the range references in the macro it is already using the current worksheet. I assume the problem is that sometimes the activesheet is not the one you want to convert to html.

you need to make the correct sheet active. So something like this.

Please Login or Register  to view this content.

I get the following error:

Run-time error "9":

subscript out of range

I even tried pasting it in Module #2

**Andy Pope** · 11-09-2009, 12:25 PM

Obvious question but I have to ask. Does your workbook have a tab named Sheet1 ?

**DanishFk** · 11-09-2009, 12:31 PM

Originally Posted by Andy Pope

Obvious question but I have to ask. Does your workbook have a tab named Sheet1 ?

Sheet is named RSOVER750K

Module 1 looks like:

Sub Macro1()
Worksheets("RSOVER750K").Activate
dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "Macro1"
Columns("P:AH").Select
Selection.Sort Key1:=Range("AG1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
Run "MakeHTM_Over"
End Sub

**Andy Pope** · 11-09-2009, 12:33 PM

so even with the correct sheetname the code errors on that line?

Please Login or Register  to view this content.

**DanishFk** · 11-09-2009, 12:38 PM

Originally Posted by Andy Pope

so even with the correct sheetname the code errors on that line?

Please Login or Register  to view this content.

Yes, the error appears on that line.

One sheet has RSUNDER750K other sheet has RSOVER750K.

Maybe the activate code is running on a excel file which doesn't have that sheet in the first place. Same issue as with other macro where it's mixing up files.

RSUNDER750K and RSOVER750K are sheets in different Excel files.

**Andy Pope** · 11-09-2009, 12:41 PM

So you need to apply some logic in order to make sure the correct workbook is active as well as the correct sheet.

**DanishFk** · 11-09-2009, 01:01 PM

Originally Posted by Andy Pope

So you need to apply some logic in order to make sure the correct workbook is active as well as the correct sheet.

Used:

Application.Workbooks("RSOVER750K").Activate
Application.Sheets("RSOVER750K").Activate

Seems to work now, thanks

**StephenR** · 11-09-2009, 01:05 PM

This is a cross-post: http://www.ozgrid.com/forum/showthread.php?t=141354

Fortunately, no-one at Ozgrid appears to have spent any time on this.

Using Macros to create HTML

LinkBack

Thread Tools

Rate This Thread

Display

Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Re: Using Macros to create HTML

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions