You are viewing this page out of context. To see it in the context it is intended please click here.

About This Page

Reply posted by Campbell Moody on Thu 17 Jul 2003 in response to Keeping the boss happy

Controlling Excel from LotusScript

I do all this stuff quite differently from what has been mentioned so far.


I start a few variables that have the same name as their VBA equivalents as
follows:


xlCenter=-4108
xlLeft = -4131
xlRight = -4152
xlBottom = -4107
xlTop = -4160
xlEdgeBottom = 9
xlEdgeLeft = 7
xlEdgeRight = 10
xlEdgeTop = 8

Launch Excel from script with:
Set XA = CreateObject("Excel.Application")

Add a New Workbook, and get a handle to the active Worksheet:
Call XA.Workbooks.Add
Set XW = XA.ActiveSheet
XA.ActiveWindow.Zoom = 75

Add some page setup stuff:
XW.PageSetup.PrintTitleRows = "$1:$3"
XW.PageSetup.PrintTitleColumns = ""
XW.PageSetup.LeftHeader = ""
XW.PageSetup.CenterHeader = ""
XW.PageSetup.RightHeader = ""
XW.PageSetup.LeftFooter = "Some Text here"
XW.PageSetup.CenterFooter = "Page &P"
XW.PageSetup.RightFooter = "&D"
XA.Visible = True


Put something in a cell:
XW.Range("A3").value = "No"


Put something in a computed cell:
rownumber=4
XW.Range("B" & rownumber).value = doc.form(0)


Format a range of cells:
XW.Range("A4:J"& rownumber).Select
XA.Selection.HorizontalAlignment = xlLeft
XA.Selection.VerticalAlignment = xlTop
XA.Selection.WrapText = True
XA.Selection.Orientation = 0
XA.Selection.ShrinkToFit = False
XA.Selection.MergeCells = False
XA.Selection.Borders.LineStyle = 1
XA.Selection.Borders.Weight = 2


.... And so on.


Basically, using the above code (and i'm only using the xlTop and xlLeft values
in the examples, you'll need to research the other ones in the VBA help in
Excel), to make reports, all you do is start the macro recorder in Excel, do
what it is that you want to achieve, then cut and paste the code straight into
your click event or whatever.

To make it work, you edit the VBA code so that where it says 'Application.' you
change that to XA., and everything that says 'Worksheet.' you change to XW.


People love it, because what happens is that Excel starts up, and magically
starts filling in with the data, then formats it (depending on the code you
write of course). You have control over literally everything, and get a real
insight into how Excel works internally as well.


It doesn't translate to the Web too well, but in an environment where you have
notes clients, and know that Excel is installed, it works beautifully.


You even get to trap that the initial CreateObject call failed, so you know
that Excel isn't listening.