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>,

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.

[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.

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

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

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

http://www.informationbuilders.../new/pdf/WFN1207.pdf (page 6)

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

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