Re: [U2] Building an Excel File

Israel, John R. Mon, 06 Feb 2012 14:02:41 -0800

I will delve into this code tomorrow.

Thanks.


John

-----Original Message-----
From: u2-users-boun...@listserver.u2ug.org 
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Richard A. Wilson
Sent: Monday, February 06, 2012 4:50 PM
To: U2 Users List
Subject: Re: [U2] Building an Excel File

try this subroutine. Note it is written for an old Revelation G2b application 
so change as needed

Rich

      SUBROUTINE
EXCEL_SCRIPTS_SUBR(RESULT,PROCESS,FROM.FILE,TO.FILE,EXTRA.INFORMATION)
*------------------------------------------------------------------------
*
* Purpose: Create Excel Vbs Scripts
*
* Date:    01-24-12
*
* Author:  Richard A. Wilson
*          Lakeside Systems
*          4 Myers Street
*          Smithfield, RI  02917-1821
*          Email: rwil...@lakeside-systems.com
*
*------------------------------------------------------------------------
*
* M o d i f i c a t i o n s
*
* Date     By  Description
* 02-06-12 RAW Saving as XLS doesnt require file format
*              Infact it will cause save as vbs error
*------------------------------------------------------------------------
*
* Note from and to files require complete path L:\RES\etc
*
      RESULT = ''
      BEGIN CASE
         CASE PROCESS EQ 'XLSTOTAB'
            FILE.FORMAT = -4158
            EXTENSION = 'TXT'
         CASE PROCESS EQ 'XLSTOCSV'
            FILE.FORMAT = 6
            EXTENSION = 'CSV'
         CASE PROCESS EQ 'CSVTOXLS'
            FILE.FORMAT = 56
            EXTENSION = 'XLS'
      END CASE
*
      COMMA.CNT = COUNT(FROM.FILE,'.')
      PART1 = FIELD(FROM.FILE,'.',1,COMMA.CNT)
*
      IF TO.FILE EQ "" THEN
         TO.FILE = PART1:'.':EXTENSION
      END
*
* Quote the file names just incase spaces might exist and cause issues
*
      QUOTED.FROM.FILE = '"':FROM.FILE:'"'
      QUOTED.TO.FILE = '"':TO.FILE:'"'
*
      SCRIPT = ''
      SCRIPT<-1> = "'"
      SCRIPT<-1> = "' 01-23-12 Found this script at technet.microsoft.com"
      SCRIPT<-1> = "'"
      SCRIPT<-1> = "if WScript.Arguments.Count < 2 Then"
      SCRIPT<-1> = '    WScript.Echo "Error! Please specify the source 
path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"'
      SCRIPT<-1> = "    Wscript.Quit"
      SCRIPT<-1> = "End If"
      SCRIPT<-1> = "Dim oExcel"
      SCRIPT<-1> = 'Set oExcel = CreateObject("Excel.Application")'
      SCRIPT<-1> = "Dim oBook"
      SCRIPT<-1> = "Set oBook =
oExcel.Workbooks.Open(Wscript.Arguments.Item(0))"
      SCRIPT<-1> = "'"
      SCRIPT<-1> = "' following disables overwrite popup"
      SCRIPT<-1> = "'"
      SCRIPT<-1> = "oExcel.DisplayAlerts = False"
*
* 02-06-12 following IF logic
*
      IF EXTENSION EQ 'XLS' THEN
         SCRIPT<-1> = "oBook.SaveAs WScript.Arguments.Item(1)"
      END ELSE
         SCRIPT<-1> = "oBook.SaveAs WScript.Arguments.Item(1), ":FILE.FORMAT
      END
*
      SCRIPT<-1> = "oBook.Close False"
      SCRIPT<-1> = "oExcel.Quit"
      SCRIPT<-1> = "'"
      SCRIPT<-1> = "'":' 01-23-12 comment out "Done"'
      SCRIPT<-1> = "'"
      SCRIPT<-1> = "'":' WScript.Echo "Done"'
*
      CONVERT @FM TO CHAR(10) IN SCRIPT
      OSWRITE SCRIPT ON 'C:\BATFILES.WRK\':PROCESS:'.VBS'
*
* Execute script
*
      CMD = 'C:\BATFILES.WRK\':PROCESS:'.VBS '
      CMD := QUOTED.FROM.FILE:' '
      CMD := QUOTED.TO.FILE
      BAT.NAME = 'REN':TIME()
      CONVERT '.' TO '' IN BAT.NAME
      BAT.NAME = '\BATFILES.WRK\':BAT.NAME[1,8]:'.BAT'
      OSWRITE CMD ON BAT.NAME
      OSCLOSE BAT.NAME
      PCPERFORM 'CMD.EXE /C ':BAT.NAME
      PCPERFORM 'ERASE ':BAT.NAME
      IF EXTRA.INFORMATION EQ 'DELETE_FROM.FILE' THEN
         PCPERFORM 'CMD.EXE /C ERASE ':FROM.FILE
      END
*
      RETURN
   END




Israel, John R. wrote:
> We are running UniData on a UNIX platform.  We create tab-delimited txt files 
> (and tab delimited txt files with xls extensions) all the time that users 
> open with Excel.  Many of these jobs are run in the middle of the night or in 
> a trigger program.  In all cases, there is no direct user interaction 
> building these files.
>
> We also have some XML files that are coded so that Excel opens them with 
> formatting, colors, filters, etc.
>
> Unfortunately, our BB users and iPhone users can not open these files up in a 
> meaningful way.
>
> Is there a way to build a TRUE Excel file without any user intervention from 
> UniData or UNIX?
>
> John
>
>
>
>
>
> John Israel
> Senior ERP Developer
>
> Dayton Superior Corporation
> 1125 Byers Rd  Miamisburg, OH 45342
> Office: 937-866-0711 x44380
> Fax: 937-865-9182
>
> johnisr...@daytonsuperior.com
>
> This message w/attachments (message) is intended solely for the use of the 
> intended recipient(s) and may contain information that is privileged, 
> confidential or proprietary. If you are not an intended recipient, please 
> notify the sender, and then please delete and destroy all copies and 
> attachments, and be advised that any review or dissemination of, or the 
> taking of any action in reliance on, the information contained in or attached 
> to this message is prohibited.
>
>
>
>
> _______________________________________________
> U2-Users mailing list
> U2-Users@listserver.u2ug.org
> http://listserver.u2ug.org/mailman/listinfo/u2-users
>
>   

--
Richard A Wilson
Lakeside Systems
Smithfield, RI, USA
Voice 401-231-3959
Fax   206-202-2064

_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users

Re: [U2] Building an Excel File

Reply via email to