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] Writing from Excel to Webfocus

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Writing from Excel to Webfocus
 Login/Join
 
<janine>
posted
Hi

I'm quite nervous about posting a question, as I know some people respond quite harshly! But I'm quite desperate for help - I have raised a case, but have been told that it is an issue on the macro side.

I'm no expert and that's why I'm asking for help ... please be kind :-)

I saw this article http://www.informationbuilders.../8-5/07_delgado.html which shows how to display data on an excel spreadsheet, and then when a button is clicked, to write user changes back to webfocus.

I have used the code almost exactly (I called my .mht EXCEL_MHT.mht instead of EXCEL_TEMPLATE; and in the macro I put the name of our server). I created a EXCEL_MHT.fex and EXCEL_MHT.mht which are both stored in the apps\bi folder. This folder is mapped in the application path.

When I run the .fex, the data is correctly displayed in the spreadsheet in a WFServlet. But when I click on the button to update the CAR table in webfocus, then I get a message saying that EXCEL_MHT.mht cannot be found.

So if I understand correctly: The .fex can find the .mht and uses it to display the data in the WFServlet. However, when clicking the button on the WFServlet, it cannot find the .mht. Could somebody please help me in getting the data to be written back to the table? We don't have MAINTAIN.

The strange part: Yesterday on my work PC, if I had the .mht open and then ran the process, it worked, and I managed to successfuly update the CAR table. However, today I am working remotely on the laptop and it won't work even if I have the .mht open!

The .fex is as follows:


-GOTO &GOTO_LABEL

-REPORT

TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
BODYTYPE
RETAIL_COST
DEALER_COST
COMPUTE MARGIN/D5.2 = (RETAIL_COST - DEALER_COST) / DEALER_COST;

ON TABLE PCHOLD FORMAT EXL2K FORMULA TEMPLATE 'EXCEL_MHT' SHEETNUMBER 2

END

-EXIT

-INSERT

-IF &COUNTRY.EXISTS NE 1 THEN GOTO NODATA;

MODIFY FILE CAR
FREEFORM COUNTRY CAR MODEL BODYTYPE DEALER_COST RETAIL_COST
MATCH COUNTRY CAR MODEL BODYTYPE
ON NOMATCH REJECT
ON MATCH UPDATE DEALER_COST RETAIL_COST
DATA

-IF &COUNTRY0.EXISTS EQ 1 THEN GOTO MANY;

-JUST_ONE

COUNTRY=&COUNTRY ,CAR=&CAR , MODEL=&MODEL , BODYTYPE=&BODYTYPE, DEALER_COST=&DEALER_COST
,RETAIL_COST=&RETAIL_COST ,$

-GOTO LOOP

-MANY

-SET &I = 0;
-REPEAT LOOP &COUNTRY0 TIMES
-SET &I = &I + 1;
COUNTRY=&COUNTRY.&I ,CAR=&CAR.&I , MODEL=&MODEL.&I , BODYTYPE=&BODYTYPE.&I, DEALER_COST=&DEALER_COST.&I
,RETAIL_COST=&RETAIL_COST.&I ,$
-LOOP

END

-NODATA
-EXIT



Then the excel macro is as follows:


Sub CommandButton1_Click()

Call xmlQuery

End Sub


Sub xmlQuery()

Const Server_URL = "http://2003bit/ibi_apps/WFServlet"
Const IBIAPP_app = "EXCEL_MHT"
Const IBIF_ex = "EXCEL_MHT"

Dim xmlhttp As xmlhttp
Set xmlhttp = New xmlhttp

Dim strRequest As String

strRequest = getRequest(IBIAPP_app, IBIF_ex)

Call xmlhttp.Open("POST", Server_URL, False)
Call xmlhttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded")
Call xmlhttp.send(strRequest)


Call MsgBox(xmlhttp.responseText)


Set xmlhttp = Nothing

End Sub

Function getRequest(IBIAPP_app As String, IBIF_ex As String) As String

Dim strRequest As String
strRequest = getChangedData()

MsgBox strRequest
If strRequest = "" Then Exit Function

getRequest = "RANDOM=" & Int(Timer()) & _
"&IBIF_wfdescribe=OFF" & _
"&GOTO_LABEL=INSERT" & _
"&IBIAPP_app=" & IBIAPP_app & _
"&IBIF_ex=" & IBIF_ex & _
"&" & strRequest

End Function

Function getChangedData() As String

Dim UserWS As Worksheet
Dim DownLoadWS As Worksheet
Dim strRequest As String
Dim i As Long
Dim j As Long

Set UserWS = Worksheets("User's Worksheet")
Set DownLoadWS = Worksheets("DownLoaded Data")

'determine last row on download sheet
i = 2
Do Until Sheets("DownLoaded Data").Cells(i, 1) = ""
i = i + 1
Loop
i = i - 1

strRequest = ""

For j = 2 To i

strRequest = IIf(strRequest = "", strRequest, strRequest & "&")

'COUNTRY
strRequest = strRequest & Sheets("DownLoaded Data").Cells(1, 1).Value & "=" & _
escape(Sheets("User's Worksheet").Cells(j, 1).Value) & "&"
'CAR
strRequest = strRequest & Sheets("DownLoaded Data").Cells(1, 2).Value & "=" & _
escape(Sheets("User's Worksheet").Cells(j, 2).Value) & "&"
'MODEL
strRequest = strRequest & Sheets("DownLoaded Data").Cells(1, 3).Value & "=" & _
escape(Sheets("User's Worksheet").Cells(j, 3).Value) & "&"
'BODYTYPE
strRequest = strRequest & Sheets("DownLoaded Data").Cells(1, 4).Value & "=" & _
escape(Sheets("User's Worksheet").Cells(j, 4).Value) & "&"
'RETAIL_COST
strRequest = strRequest & Sheets("DownLoaded Data").Cells(1, 5).Value & "=" & _
escape(Sheets("User's Worksheet").Cells(j, 5).Value) & "&"
'DEALER_COST
strRequest = strRequest & Sheets("DownLoaded Data").Cells(1, 6).Value & "=" & _
escape(Sheets("User's Worksheet").Cells(j, 6).Value) & "&"

Next j

getChangedData = strRequest

End Function



Public Function escape(stringIn As String) As String
'URL Encode the string

Dim temp As String
temp = Replace(stringIn, "%", "%25")
temp = Replace(temp, "+", "%2B")
temp = Replace(temp, " ", "%20")
temp = Replace(temp, ";", "%3B")
temp = Replace(temp, "/", "%2F")
temp = Replace(temp, "?", "%3F")
temp = Replace(temp, ":", "%3A")
temp = Replace(temp, "@", "%40")
temp = Replace(temp, "=", "%3D")
temp = Replace(temp, "&", "%26")
temp = Replace(temp, "<", "%3C")
temp = Replace(temp, ">", "%3E")
temp = Replace(temp, Chr(34), "%22")
temp = Replace(temp, "#", "%23")
temp = Replace(temp, "{", "%7B")
temp = Replace(temp, "}", "%7D")
temp = Replace(temp, "|", "%7C")
temp = Replace(temp, "\", "%5C")
temp = Replace(temp, "^", "%5E")
temp = Replace(temp, "~", "%7E")
temp = Replace(temp, "[", "%5B")
temp = Replace(temp, "]", "%5D")
temp = Replace(temp, Chr(96), "%60")
temp = Replace(temp, Chr(10), "%0A")
temp = Replace(temp, Chr(13), "%0D")
escape = temp
End Function


Thanks for looking.

Regards,
Janine

This message has been edited. Last edited by: <janine>,
 
Report This Post
<JG>
posted
Janine, there are quite a lot of differences between your code and the example written by Mario Delgado
in the Winter 2007 WebFOCUS news letter which I assume is what you have based your code on.

Excelling With WebFOCUS: Updating Server Data from a WebFOCUS Excel Template

I would start by going back to Mario's original code and get that to work.
 
Report This Post
Silver Member
posted Hide Post
[quote=janine]The strange part: Yesterday on my work PC, if I had the .mht open and then ran the process, it worked, and I managed to successfuly update the CAR table. However, today I am working remotely on the laptop and it won't work even if I have the .mht open!
[/quote]
Call xmlhttp.Open("POST", Server_URL, False)
Call xmlhttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded")
Call xmlhttp.send(strRequest)
I think you were not able to make it work remotely because the xmlhttp object is not allowed to make cross domain calls. When you were working remotely and if the excel spreadsheet was on the remote machine you more than likely were trying to do something the xmlhttp object is not allowed to do for security reasons. Now if you had the spread sheet on a local machine (inside the network) and you 'remoted' to that machine and ran it from that machine it should work fine (like it already has). Just a guess but I would sure take a look at that.


7.6.6 Mainframe
7.6.4 Web Focus
Windows

 
Posts: 45 | Location: Gaffney SC | Registered: March 30, 2007Report This Post
<janine>
posted
Hi

Thanks for the tips JG and Lucas. I am back at work today and will follow your advice. Will keep you posted.

Thanks,
Janine
 
Report This Post
<janine>
posted
Hi

The code as such was correct ... my error (yes very embarrassing) was that I had used a Button from the forms toolbar instead of using the Command Button from the VB toolbar.

The forms Button kept defaulting the on_action to include a prefix of "EXCEL_MHT.mht" before the sub, which is why the code was expecting the .mht to be open.

Now that I am using a Command Button, the program works.

Thanks for your time.

Regards,
Janine
 
Report This Post
Platinum Member
posted Hide Post
Hi,
can anybody gice me the newsletter from Mr. Delgado the link does not work anymore!!
"Excelling With WebFOCUS: Updating Server Data from a WebFOCUS Excel Template "


Regards

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
Virtuoso
posted Hide Post
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
Hi, sorry for refreshing this post!!
I get the same error message as janine "EXCEL_MHT.mht cannot be found" and i used a button from vba toolbar. I use excel 2007 for building the mht. Could this be the reason?? i followed strictly Mr. Delgado,s example!!

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
Platinum Member
posted Hide Post
Hi,

You need to use excel 2003 to update/create mht files as they are not a fully supported feature in Office 2007

Here is the statement from microsoft

Single File Web Page (*.mht; *.mhtml) Excel 2007 no longer stores Excel-specific feature information in this file format. You can open a file in this format, preserving any Excel-specific features that were created in an earlier version of Excel. However, you can save such a file (or any other workbook) in this file format for publishing purposes only.

Crystal


Webfocus 8.0.7 on Windows
 
Posts: 176 | Location: Ohio | Registered: October 26, 2006Report 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] Writing from Excel to Webfocus

Copyright © 1996-2020 Information Builders