Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] XLSX Output Using Template - Getting Excel 'Activate method...' Error.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] XLSX Output Using Template - Getting Excel 'Activate method...' Error.
 Login/Join
 
Master
posted
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,




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
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".


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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.




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Master
posted Hide Post
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. :-)
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
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)?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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.
 
Posts: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] XLSX Output Using Template - Getting Excel 'Activate method...' Error.

Copyright © 1996-2020 Information Builders