I copied the following code directly from the manual. Please note that I am running on MVS using FOCUS 7.6.4 and want to generate multiple worksheets. Also notice that I'm using HOLD instead of PCHOLD and transferring the file later. If I run without BYTOC I get a single worksheet (as expected); when I add BYTOC and download the file, Excel says "Unable to read file." I'm using Excel 2002. Any suggestions?
SET COMPOUND=BYTOC TABLE FILE CAR PRINT SALES BY COUNTRY NOPRINT BY CAR ON TABLE HOLD AS EXCEL FORMAT EXL2K ENDThis message has been edited. Last edited by: Alan,
WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
January 26, 2010, 02:37 PM
njsden
As far as I know, that functionality is only available when using Excel 2003.
I'll try to look for the exact piece of documentation and will post it later.
Note: This feature can be used only with Excel 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases.
WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
January 26, 2010, 02:42 PM
njsden
Sorry! My memory is not serving me well . Excel 2002 should be supported!
quote:
Note: This feature can be used only with EXCEL 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases.
I don't have access to Excel 2002 so I am not able to replicate you case
Neftali, This is such a simple technique for generating multiple worksheets, and there's not a lot of documentation to read. Everything says it should just work.
I also read thru all the FocalPoint stuff but most of that deals with using PCHOLD -- not quite the same thing.
Thanks for the feedback!
WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
January 26, 2010, 02:57 PM
njsden
Weird!
I'm seeing something similar in my environment.
This is the code I'm running against by WF 5.3.3 Server (on Unix):
SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT SALES BY COUNTRY NOPRINT BY CAR
ON TABLE HOLD AS EXCEL FORMAT EXL2K
END
-RUN
-UNIX excel.xht /tmp/.
I then transferred "excel.xht" to my local Windows XP desktop. When attempting to open it in Excel 2003 I get:
quote:
XML Parsing Error: syntax error Location: file:///H:/excel.xht Line Number 1, Column 1:MIME-Version: 1.0
I tried copying the file in both Text and Binary formats from my FTP client but found no difference whatsoever.
Hmmm, there seems to be much more to this. After the HOLD file is created, there are a set of *.xht files in the temporary location created for my session.
Those are:
19216 Jan 26 15:08 excel.xht
3503 Jan 26 15:08 ibit0001.xht
3310 Jan 26 15:08 ibit0002.xht
3497 Jan 26 15:08 ibit0003.xht
3386 Jan 26 15:08 ibit0004.xht
3653 Jan 26 15:08 ibit0005.xht
If I peek into "excel.xht" I can see the following at the very beginning of the file:
Well, this will sound kind of silly but hey, we all have to learn somehow.
Those "ibi*.xht" files are not really required. Whenever I attempted to open "excel.xht" I was getting the weird XML Parser error; but then I did things in a different order ... I started Excel first and from within I asked it to open "excel.xht" and guess what? I does work!
I think what I'll need to do is to just create an association between .XHT and Excel in my Windows desktop so I can open it directly.
Neftali, You must be running WebFOCUS instead of FOCUS. I'm on MVS running FOCUS and pre-allocating the HOLD file. I don't think MVS FOCUS creates more than one xls file (if it does, I'm not aware of it).
Of course, that just may be the problem...
Alan
WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
January 26, 2010, 03:27 PM
Alan
However, I'm looking at the FOCUS for Mainframe manual...
WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
January 26, 2010, 03:40 PM
njsden
I have never worked with FOCUS on MVS so I don't know which "particularities" should be considered.
However, based on your comment:
quote:
I don't think MVS FOCUS creates more than one xls file ..
I think I may know what the issue might be. When you create a regular HOLD file in EXL2K the engine creates a file with ".xls" extension. However, when using BYTOC, [Web]FOCUS no longer produces an Excel file but an XML file instead and it now uses the ".xht" extension.
If you are pre-allocating your HOLD file and are explicitly using .xls that could be the reason why Excel cannot understand the file. It sees ".xls" and therefore expects a file in native Excel format, not in XML.
To make matters simple and since I pressume you already have a local copy of the file in your PC, can you try renaming the file replacing its extension by ".xht" and later attempting to open it from with Excel and see what happens?
Neftali, You are absolutely correct! MVS FOCUS generates an xml files instead of the Web Archive format described in the manual (or is that what Web Archive means?). I had pre-allocated the file with a name of .xls and transferred it to my PC. I changed the name to .xht and ran again, then transferred to my PC and got the same results.
Turns out that by initiating FTP from the mainframe some hidden characters are embedded into the .xht file that cause a problem. When I use FTP client from my PC the problem goes away. Now all I have to do is capture the FTP commands to find out what option it's using.
Thanks for your help with this!
Alan
WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL