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.
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.