Using MS-Excel with OLE- Rev 11-30-2002
Using MS-Excel with OLE- Rev 12-08-2002
Below is some information I have gathered from many Fivewin
users (Thanks to all of you).
I hope this information will be as helpful to others as it
was for me. Good Luck and Enjoy.
Setting Up:
1. First you need to have Fivewin 2.0 or higher
2. Next you need to download the Texcelscript V1.1 from
Patrick Mast’s website:
(http://www.fivewin.info/ulfiles/TExcelS1.1.zip)
3. Link in the OLE2.LIB file.
Programming:
Local oExcel, oSheet, oWin
oExcel := TOleAuto():New( "Excel.Application"
) //Start Excel OLE
oExcel:Workbooks:Add() //Open
Excel with empty book
oExcel:WorkBooks:Open(cMyFile) //open
existing file
oExcel:Get( "ActiveWorkbook" ):SaveAs(cMyFile) //save
file as
nSheets := oExcel:Sheets:Count() //count
number of sheets in workbook
oExcel:Worksheets( n ):Delete() //delete
a sheet from workbook
oSheet := oExcel:Get("ActiveSheet") //Get
active sheet
oWin := oExcel:Get( "ActiveWindow" ) //Get
active window
oSheet:Set("NAME", cText ) //name
your sheet method 1
oSheet:Name := cText //name
your sheet method 2
oSheet:Cells( nRow, nCol ):Value := myValue //put
data in a cell
oTemp:=oSheet:Cells(nRow, nCol ):Value //read
data from a cell
oSheet:Cells( nRow, nCol ):Select() //Select
a cell
oSheet:Cells:Font:Size := 10 //set
font size for all cells in active sheet
oSheet:Cells:Font:Name := "Arial" //set
font for all cells in active sheet
oSheet:Cells( 1, 1 ):Font:Size := 12 //set
font size for a cell
oSheet:Cells( 1, 1 ):Font:Bold := .t. //set
bold on for a cell
oSheet:Cells( 1, 1 ):Font:Bold := .f. //set
bold off for a cell
oSheet:Cells( 1, 1 ):Font:Name := "Arial" //set
font for a cell in active sheet
oSheet:Paste() //paste
from clipboard into selected cell
oSheet:Columns( nCol ):AutoFit() //will
automatically adjust the width of column nCol
nRows := oSheet:UsedRange:Rows:Count() //returns
the number of used rows
nCols := oSheet:UsedRange:Columns:Count() //returns
the number of used columns
oWin:Set( "FreezePanes", .t. ) //Freeze
cells from Selected Cell onward
oExcel:Visible := .F. //display
or hide MS-Excel
oExcel:Set( "DisplayAlerts", .F.) //display
or hide MS-Excel messages
oExcel:set( "ActivePrinter", cPrinter) ) //Select
printer (if you don’t use this, windows default
printer is
used)
Hint: load
MS-Excel and run the Macro function, print
to a specific
printer then edit the Macro to see what
excel wants
to call the printer.
oSheet:= oExcel:Get("ActiveWorkbook"):printout() //Print
entire workbook
oSheet:PrintOut() //Print
active sheet
oExcel:Sheets(cSheet):Select() //Select
sheet
oExcel:Quit() //Close
MS Excel application
oSheet:End() //End
oSheet
oExcel:End() //End
oExcel
oSheet := oExcel:Sheets(“oSheet1”) //move
sheet position. This example will move
oExcel:Sheets( "oSheet2” ):Move( oSheet )
//sheet2 infront of sheet1
Useful Tools:
Search your hard drive for VBAXL*.CHM – This is the MS-Excel
help file. It will give you an idea of what commands are available and a rough
idea on how to use them.
To see how a command is used in MS Excel:
1. Load
MS-Excel application
2. Start
the “Record Macro” tool
3. Manually
do what you want to do in MS Excel.
4. Stop
the Macro and Edit it.
5. You
will see the commands the way MS Excel uses them.
To make debugging easier I leave Excel Visible (oExcel:Visible
:= .T.) and rem out oExcel:Quit() until I have it running the way I want.
(NOTE: if you have errors, check your task manager to make sure you clear out
any running instances of EXCEL.EXE)
I only started working with MS Excel in October 2002 so I’m
sure I’ll be adding to this list.
If you think I can help …. E-mail me or leave a message on
the news:local.fivewin.English form and I’ll see what I can do.
Jeff Barnes
barnesj@sympatico.ca
81% (31 votes registred)