New Response

« Return to the main article

You are replying to:

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

Your Comments