I'm modifying some old focexecs that call Python to open the .XLS files created in WebFOCUS and save them as Excel files. This is done because they found that in doing so it reduces the file size because whether they use FILEDEF or ON TABLE SAVE FORMAT EXCL2K, the file created was not a true Excel file, but rather Excel HTML, which creates much larger files (with hundreds of these created daily, they need to minimize the file sizes).

Evidently this was the case many releases ago. Is this still the case in WebFOCUS 7.6.8? I'd like to get rid of the Python routines as part of streamlining and cleaning up the code.


Thank you,

John

This message has been edited. Last edited by: JohnB,

According to Tech Support, this is still the case, and also is the case when creating .PDF files -- they are actually PDF-HTML. There is a new feature request to create real Excel and real PDF files.

The PDF files are not HTML, but a non compressed PDF document. There is a new feature in 7.6.4, I think, with the setting SET FILECOMPRESS=ON, to compress the contents.

Yep,

 SET FILECOMPRESS=ON 

Works fine for FORMAT PDF, does not work for FORMAT EXL2K.
Thx for the reminder Waz

From 7.7 a new Excel format which is supposed to be Excel 2007 binary format is planned.

It will only be usable if your clients are Excel 2007.

If file size is an issue at the moment then would zipping the files be an option?

For windows, I create a VB Script file on the fly in EDATEMP and then call that. Works just fine and average file size reduction is in the order of 66%.

FILEDEF VBScript DISK ProcExcel.vbs
-RUN
-WRITE VBScript   On Error Resume Next
-WRITE VBScript   Dim ojbExcel
-WRITE VBScript   Dim objBook
-WRITE VBScript   Dim objFSO
-WRITE VBScript   set objFSO = Wscript.CreateObject("Scripting.FileSystemObject")
-WRITE VBScript   const xlNormal = 1
-WRITE VBScript   Set objExcel = CreateObject("Excel.Application")
-WRITE VBScript   if Err.Number <> 0 then
-WRITE VBScript     Wscript.Echo "Excel application not installed."
-WRITE VBScript     Wscript.Quit
-WRITE VBScript   end if
-WRITE VBScript   Set objBook = objExcel.Workbooks.Open("\\server\fullpath to file\&FILENAME.EVAL.xls")
-WRITE VBScript   If objFSO.FileExists(objBook) Then
-WRITE VBScript     objExcel.DisplayAlerts = False
-WRITE VBScript     objExcel.ActiveWorkbook.SaveAs objBook.FullName, xlNormal
-WRITE VBScript     objExcel.DisplayAlerts = True
-WRITE VBScript     objBook.Close
-WRITE VBScript   End If
-WRITE VBScript   objExcel.Quit
-WRITE VBScript   Set objBook  = Nothing
-WRITE VBScript   Set objExcel = Nothing
-WRITE VBScript   Set objFSO   = Nothing
-RUN
-* Open file in MS Eggshell on the server to regain white space !!
CMD START ProcExcel.vbs
-RUN

Of course, you have to have Excel on the reporting server.

T

This message has been edited. Last edited by: Tony A,

Hi Tony,

Would you please show how to use your VB code to open a .XHT file and save it as a.XLS file in the same location? The way it is, it appears to open a .XLS file, yes?

I've tried your example, but nothing happened. I've tried playing with the code a little as well, but didn't get the .XLS file as a result.

Thank you,

John

Filedef your output file something like FILEDEF XLSOUT DISK [fullpath]\filename.xls before using ON TABLE HOLD AS XLSOUT. Then use the VB Script code to open that file.

The VB Script code opens an xls file and then uses save as vbNormal to save it back to the same file name.

T

The FILEDEF was the missing ingredient! I was trying to get the code to open the .XHT and do a Save As into a .XLS. With the FILEDEF no changes were needed to your code.

To sum this up, your routine serves 2 purposes:
1. It compresses the Excel file
2. It converts the .XHT into .XLS

Thank you,

John

After the focexec runs the .vbs routine, later on it issues a DOS COPY command to copy the .XLS file to a different server. The .XLS file size after the .vbs routine finished was 884kb. After the copy it was 3,166kb.

I opened each version and did a SAVE AS to see what file type Excel thought each was. The original, 884 kb version was a true Excel file, but the copied 3,166kb version was the Excel HTML web archive version.

Why it revert back to the EXCEL HTML version? Any ideas on how to prevent this? I suppose I could see if the .vbs routine would work on the file after it's been copied to the other server.

Thank you,

John

What I think it's doing is not waiting for the .vbs routine to finish and is copying the original Excel HTML version. Is there a way to make the focexce wait until the .vbs routine is finished before it continues and executes the copy command?

Thank you,

John

The SYSTEM function is the solution.

The SYSTEM function forces WebFOCUS to pause processing until the DOS command finishes executing.

John, wish there were more members like you.

Found your own answer and solution before you got a reply.

Much more important though, you posted the solution.

quote:

Originally posted by JohnB:
The SYSTEM function is the solution.

The SYSTEM function forces WebFOCUS to pause processing until the DOS command finishes executing.

Hi John,

What you mean by the SYSTEM function?

Shirley

There is a UWS called SYSTEM, have a look at the functions manual.

Hi Waz,

Please excuse my lack of knowledge on SYSTEM function. I looked at the help on SYSTEM function, but I'm still not sure how that applies to the VBScript coding above. I'm not familiar with VBScript.

Thanks for the response.

I have been bugging IBI to create true Excel files for a long time now and found out in the Expert Room at Summit that they are finally there. We use OpenOffice and WebFOCUS output looks ugly and/or crashes OpenOffice when opening the "HTML Excel" currently produced by most versions of WebFOCUS.

IBI has been working on getting output to display properly on mobile devices and found out that it was necessary to get Excel to display properly in OpenOffice first.

I've had a preview of this, and it isn't perfect, but a big improvement. The folks I spoke to said it would be part of a maintenance release for 7.7.04 and perhaps 7.7.03.

I wanted to add my two cents even though this is almost a year old.

I had to use the VBScript that Tony showed to be able to allow users getting the report by email on the iPads, iPhones and Macs. The mht Excel file sent by WebFocus will not open on their devices.

I had difficulty setting it up as the ReportCaster service couldn't call the function as the Windows LocalSystem account. Here are the steps I went through to get it to work.

1. Setup ReportCaster schedule to run from an Active Directory account whose password does not change.
2. Run DCOMCNFG -32 (Since the excel version we were using was 32 bit. You can run DCOMCNFG to get to the 64 bit settings)
3. Go to Component Services\Computers\My Computer\DCOM Config\Microsoft Excel Application and right click to go to the Properties.
4. Go to the Identity tab, and change to "This user" and put in the credentials for the new Active Directory user you are using in the ReportCaster schedule
5. Go to the Security tab and for each of the settings, make sure this same user has the capability to access and launch Excel.

Once I did this, the ReportCaster schedule could run the report and the VBScript to open Excel and re-save it as a "real" Excel file. I could then send this new file to our users and they could open it on any OS or device that could open normal Excel files.

Thought this might help.