Re: Creating an Excel input file from an SQR?

[Charlie Dornbos <charlie.dornbos@GM.COM>]

Here is what I have done for a few years now. I create a .csv file on the users
hard drive, then I open an Excel file in which I have written some autostart
VBA code to pull in the file and put the title into headers and bold, center and
 change fonts. I do other things with it too, make charts and graphs, but
basically it creates the headers you are talking about.

SQR Code:
encode '<044>' into $sep
     String $Name1  &WOOR &WOCR &WOCAR BY $SEP INTO $RDATA
     String $Name2  &WOOM &WOCM &WOCAM BY $SEP INTO $MDATA
     String $Name3 &WOOW &WOCW &WOCAW BY $SEP INTO $WDATA
OPEN 'C:\SPL\PMMAND.CSV ' AS 1 FOR-WRITING RECORD=100:VARY STATUS=#STAT
     !if #stat is anyting but 0, the file didn't open
IF #STAT !=0
     PRINT 'ERROR, Report not Written'   (+1,10) bold
end-if
MOVE ', WorkOrder''s Active, WorkOrder''s Closed, WorkOrder''s Canceled' TO
$TITLE
WRITE 1 FROM $TITLE
WRITE 1 FROM $RDATA
WRITE 1 FROM $MDATA
WRITE 1 FROM $WDATA
     !Run Excel and open comma delimited file writen earlier
 LET $RUN_STRING ='C:\PROGRA~1\MICROS~1\OFFICE\EXCEL.EXE
C:\Max302\SQR3\PMMAND.XLS'
 CALL SYSTEM USING $RUN_STRING #RETURN_STATUS
     !if anything but 0 somethings wrong
 IF #RETURN_STATUS != 0

 PRINT 'Error !!!!!! Excel not Loaded.'  (+2,10)Bold
 End-if

VBA Code:
Sub Auto_Open()
    ChDrive "C"
    ChDir "C:\spl"
    Workbooks.Open Filename:="C:\spl\Pmmand.csv"
    Range("A1:D14").Select

    With Selection.Font
        .Name = "Arial"
        .Size = 10
    End With
    Rows("1:1").Select
    Selection.Font.Bold = True
    Selection.Columns.AutoFit
    With Selection.Borders(xlLeft)
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
etc.....

Charlie



Michael Lee <homestoremike@yahoo.com> on 03/01/2002 01:45:12 PM

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Charlie Dornbos/US/GM/GMC)
Subject:  Re: Creating an Excel input file from an SQR?





Also, most users like to have a header at the top to
describe the columns.  You can write the header at the
beginning of your problem, outside your select loop.
However, if you want it bolded or underlined, you
probably have to open the file and bold/underline it
yourself... then save in .XLS format.  I haven't been
able to find a way to send a file from SQR to EXCEL
and include tags to bold or underline.

Michael K. Lee
Senior HRIS Systems Analyst
Homestore