I EXecute the following code:

SET EXCELSERVURL = ' '
-*
DEFINE FILE GGSALES
 MYTEXT1/A48= 'A very very [b] bold [/b] job!';
 MYTEXT2/A48= 'A very very [i] italics [/i] job!';
 MYTEXT3/A48= 'A very very <u> underlined </u> job!';
END
-*
TABLE FILE GGSALES
HEADING
"Sales for Poughkeepsie, NY"
-*
SUM DOLLARS
    MYTEXT1
    MYTEXT2
    MYTEXT3
BY  PCD
ON TABLE PCHOLD AS MYFILENAME FORMAT XLSX
END  

I then create the following macro in the workbook that was created and downloaded by WebFOCUS:

Sub converthtmltags()

MsgBox "Ready to convert HTML tags."

' Select the cells of the sheet with data.
Worksheets("Sheet1").Activate 
ActiveSheet.UsedRange.Select

' Format cells with text containing HTML tags.
With CreateObject("InternetExplorer.Application")
    .Visible = False
    .Navigate "about:blank"
    For Each cell In Selection
        If InStr(cell.Value, "<") > 0 Then
            .document.body.InnerHTML = cell.Value
            .ExecWB 17, 0
            .ExecWB 12, 2
            ActiveSheet.Paste Destination:=cell
        End If
    Next cell
    .Quit
End With

MsgBox "HTML tags converted."

End Sub  

And magically I see my 'Ready' message, then HTML tags converted to Excel formatted text, and then the 'Completion' message.

My requirement is to have the macro run automatically upon the focexec execution, XLSX creation, download, and open in Excel.

So I..:
* Created an Excel file called HTMLCONVERTER.xltm.
* Added my 'html conversion' code to a module called 'Workbook_Open()' in the ThisWorkbook object of the .xltm.
* Stored the .xltm in an application folder called TESTCODE.
* Amended my focexec to call the .xltm.

Here is my focexec with the amendment:

APP PREPENDPATH TESTCODE
-RUN
-*
SET EXCELSERVURL = ' '
-*
DEFINE FILE GGSALES
 MYTEXT1/A48= 'A very very [b] bold [/b] job!';
 MYTEXT2/A48= 'A very very [i] italics [/i] job!';
 MYTEXT3/A48= 'A very very <u> underlined </u> job!';
END
-*
TABLE FILE GGSALES
HEADING
"Sales for Poughkeepsie, NY"
-*
SUM DOLLARS
    MYTEXT1
    MYTEXT2
    MYTEXT3
BY  PCD
ON TABLE PCHOLD AS MYFILENAME FORMAT XLSX TEMPLATE HTMLCONVERTER.xltm SHEETNUMBER 1
END   

When I EXecute this I see my 'Ready' message, and then I see an 'Activate method of worksheet class failed." error, on the 'Worksheets("Sheet1").Activate line.

The .xltm itself is fine, as I don't see the error message, if I open HTMLCONVERTER.xltm locally.

Also, after stopping the 'debugging', if I then run the macro manually it works correctly.

So, the error only occurs when the macro runs automatically at startup.

Perhaps there is some code, at the top of the VBA, that would allow the code to activate, select, and convert?

I can't find any code samples in FocalPoint, nor the manuals, in how to run a macro automatically upon opening a downloaded Excel file, when the macro makes updates to the worksheet.

Although it sounds like many folks (way smarter than me!) have got things like this to work. :-)

This message has been edited. Last edited by: David Briars,

I think you would have had problems with your original XLSX output if this were the case, but don't you need an ON TABLE SET HTMLENCODE ON here?

XLSX uses XML internally, and the < and > in the HTML conflict with the same symbols in the internal XML, resulting in a corrupt worksheet. A corrupt worksheet would explain the error you're seeing, but in that case I would also have expected a message along the lines of "Excel modified your worksheet to fix problems".

Thanks for your review Wep.

The data in my real scenario is coming from an external system.

And so, the text fields are coming in with the 'html tags'.

With ON TABLE PCHOLD FORMAT HTML the data looks perfect! I see bolding, underlining, and italics perfectly.

IF I wanted to show the tags and not the formatting in HTML format I would use the SET HTMLENCODE command.

My business case is to show the 'tags formatting' in both HTML and XLSX formats.

HTML is done, XLSX is tantalizingly close.

I see the data in the worksheet, AOK. It is just that the conversion subroutine cacks upon the worksheet opening, and me clicking the 'Enable Editing' button.

When I try running the macro, after turning off the debugger, I see it running perfectly, and the text 'tags' convert to formatting.

Interesting.

If I upon the download I click 'Save' and then 'Open' in my downloads folder, the macro works perfectly (no messy error message).

My users will want to, of course, click 'Open' upon the download and not save the file first, so hopefully we can together find a fix. :-)

quote:

Originally posted by David Briars:
With ON TABLE PCHOLD FORMAT HTML the data looks perfect! I see bolding, underlining, and italics perfectly.

IF I wanted to show the tags and not the formatting in HTML format I would use the SET HTMLENCODE command.

My suggestion is to only add HTMLENCODE for XLSX outputs.

That doesn't appear to be your issue though, since it works with 'Save' and not with 'Open'.

Does the XLSX output have the correct extension and/or MIME-type HTTP-header for .XLSM output (as opposed to, for example, .XLSX output)?

quote:

...Added my 'html conversion' code to a module called 'Workbook_Open()' in the ThisWorkbook object of the .xltm...

I moved my 'html conversion' code from the 'Workbook_Open()' event handler to 'Workbook_Activate()'.

And once I did that there was much rejoicing.

From my testing the 'activate' event occurs after the 'open' event.

For what my VBA code was doing and/or what happens upon the download and open in Excel (...PCHOLD FORMAT XLSX...) the 'activate' event handler is what the doctor ordered.