Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Formatted output to Excel

    I have a nice report done up for a client. Fairly complex, involving a crosstab with dynamic column headers as well as Report and Page headers.

    Now the client want the report as an Excel export rather than an Access Report.

    I realize they are going to have to sacrifice some formatting to do this, but I want to retain as much as possible. Any advice on best methods to output this data to Excel and retain the report headers and dynamic column headers would be greatly appreciated.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Posts
    25

    Output

    try the OutputTo macro action, you can select Report as ObjectType and Microsoft Excel as the Output Format

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Blindman

    Short answer is recordset - instantiate an Excel app & workbook and use the CopyFromRecordset method of the Range obect of the worksheet object (e.g. exSheet.Range("A2").CopyFromRecordset madoDataRst) - accepts DAO or ADO. You can then do all the fancy formatting you like.

    I have a generic class that formats all my exports based on parameters contained in tables. I picks up the relevent parameters for the report and then iterates through the data (columns and rows) formatting as required. Some of the more "exotic" exports have their own formatting routines that code execution peels off to.

    I think the level of formatting depends on how much time you have on your hands (or how important the formatting is once the clients have to dip into their pockets). However, I usually find coding the iterations the time consuming bit - recording your manual formatting in a macro and adjusting the code isn't in itself too difficult.
    Most of my code is more for generic application than creating anything pretty - if you write something specifically for one report you might be able to produce something quite decent.

    Report header you can write in yourself easily. Don't know how your page headers are going to work something flat like Excel. I have one report that creates one Work sheet per "grouping level" - including group specific information wouldnt be too hard I wouldn't have thought if that is the sort of affect you are thinking of.

    Code, of course, available on request.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks pootle, this is the direction I have been working on.

    Unfortunately, while I have loads of VB programming experience in Access, the Excel object structure is new to me, so I'm kind of finding my way in the dark. I hope you can give me some assistance with proper methods and syntax next week.

    Thanks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    poss alternate route is XML which opens "native" in Excel and handles all the formatting that i've thrown at it so far.

    actually coding up the XML file is a pain, but you can get around most of that by formatting an Excel as you want your final report to look and SaveAs-ing from Excel to XML.

    open the XML with notepad: rudimentary HTML knowledge is enough to steal and clone the formatting tags you need.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    cool thing to do

    idle thought:

    send your clients a bog-standard A-dump to Excel.
    tell them to format it how they want it to look and send it back to you.
    you SaveAs XLM, and pirate & clone

    ...woo: user-defined report formats implemented by the coder in moments.


    next challenge: automate it at the user level (patent pending izyrider-MegaCorp Inc)

    customer triggers your code to generate raw A-dump to XLS
    customer formats XLS & saves as XLS
    customer starts your magic code that:
    saves XLS as XML
    parses XML data vs tags
    writes code to generate XML

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - well I have zero experience of Izys suggestion - certainly looks very interesting indeed though - worth investigation.

    In case you go with old fashioned automation: thinking as I type....
    I presume you are fine with references and early\ late binding etc.

    Gotchas:
    Excel collections are 1 based not 0.
    Make sure you explicitly instantiate your objects (i.e. workbook and worksheet). If you use something like (WRONG!):
    MyApplication.Workbooks(1).DoThis
    MyApplication.Workbooks(1).DoThat
    then an implicit workbook object is created but, crucially, not released from memory when the procedure finishes. This results in errors every other time you run the code.

    Some of the methods and properties are not associated with the objets that you (or at least I) would expect them to be. Take advantage of the Excel macro recorder if you are struggling to find them.

    Main Excel objects:
    Application object.
    Use New keyword or CreateObject\ GetObject to instantiate.

    Workbook Object.
    Use the
    Set MyWorkBook = MyApplication.Workbooks.Open(WorkbookPath)
    method to open an existing workbook.
    or Use
    Set MyWorkBook = MyApplication.Workbooks.Add
    method to create a new one.

    Worksheet object - set using the Worksheets collection of the workbook object
    Set MyWorksheet = MyWorkBook.Worksheets(1)

    Remember to set your application to visible.

    Take advantage of the Appplication Interactive property to prevent users manipulating the workbook (and therefore causing the code to error)- remember to set to True in an error trap otherwise users will need to End using Task Manager in the event of an error.

    Refer to cells by ordinal postion:
    MyWorksheet.Cells(1, 1).DoSomething

    Refer to ranges by Alpha code:
    MyWorksheet.Range("A2").CopyFromRecordset myRecordset

    Function to get Alpha Column from the ordinal position (I think I rewrote this more succinctly but don't have access to the revised code right now - looks
    way over complicted to me):
    Code:
    Function ExcelCodes(ByVal intColNo As Integer) As String
     
        Dim strCol As String
     
        Do While intColNo > -1
            If intColNo > 26 Then
                strCol = Chr(64 + ((intColNo - 1) \ 26))
                intColNo = intColNo - (26 * ((intColNo - 1) \ 26))
            Else
                strCol = strCol & Chr(64 + intColNo)
                Exit Do
            End If
        Loop
     
        ExcelCodes = strCol
     
    End Function
    Freeze panes and DisplayGridlines are properties of the active window (for some reason). You also need to active the relevent cells\ sheets for some methods\ properties:
    MyWorksheet.Activate
    MyWorksheet.Cells(2, 1).Activate
    MyApplication.ActiveWindow.FreezePanes = True
    MyApplication.ActiveWindow.DisplayGridlines = False

    Most cell formatting via the font object:
    (specific cell)
    MyWorksheet.Cells(2, 1).Font.Size = 16
    (all cells in a workbook)
    MyWorksheet.Cells.Font.ColorIndex = 13

    Size column width:
    MyWorksheet.Columns(2).EntireColumn.AutoFit
    MyWorksheet.Cells(1, 2).ColumnWidth = exSheet.Cells(1, 2).ColumnWidth + 3.5

    Code that checks the datatype of the recordset field and aligns the corresponding excel column right if numeric data type:
    Code:
    intRstFieldType = MyRecordset.Fields(MyIterant).Type
    If intRstFieldType = 2 Or intRstFieldType = 3 Or intRstFieldType = 4 Or intRstFieldType = 5 Then
     
          MyWorksheet.Columns(MyIterant + 1).HorizontalAlignment = xlRight
          MyWorksheet.Cells(1, (MyIterant + 1)).HorizontalAlignment = xlLeft
    End If
    Colour borders:
    MyWorksheet.Range("A1:E1").Borders.Color = RGB(0, 0, 0)

    Add autofilter:
    MyWorksheet.Range("A1:E1").AutoFilter

    Misc:
    MyWorksheet.Cells.WrapText = False
    MyApplication.ActiveWindow.Zoom = 80
    MyWorksheet.PageSetup.Orientation = xlLandscape
    MyApplication.ActiveWindow.TabRatio = 0.8
    MyWorksheet.Range("J2", "K2").Merge
    MyWorksheet.Columns("A:C").EntireColumn.Hidden = True
    MyWorkbook.Windows(1).TabRatio = 0.948
    MyWorksheet.Cells(1, 1).interior.ColorIndex = 23
    MyWorksheet.Rows("2:12").Delete Shift:=xlUp
    MyWorkbook.Save
    MyWorkbook.SaveAs MyNameAndPath.xls

    Depends on your needs but that should give you the resources to do quite a lot of specific formatting.

    HTH
    Last edited by pootle flump; 01-16-06 at 05:58.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Had a look at XML option Izy - nice - I like that

    BTW - one other gotcha for Excel - obvious but I didn't think things through properly once - remember that changes to some of the application properties will be permanent (until the user puts them back), for example (in my case) the number-of-worksheets-in-a-new-workbook property.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've been seriously looking at the XML option - looks really promising.

    However - there might be a problem applying it to my app. Before I found the CopyFromRecordset method I would iterate every value and write them one by one to an Excel cell. However, some of the more substantial Excel reports could take minutes writing a single cell at a time. This is why I hunted out an alternative method.

    Presumably with the XML option you need to iterate through each and every value and write to the text file? Shame you can't format a range or make use of an XSD file or something.... or can you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Pootle,

    i think it was Teddy who suggested XML here a couple of weeks ago: since then i've been playing with it - seems like a neat trick. i do a lot of static HTML pages from A-code, but i'm a rank beginner with XML.

    ?? maybe a new XML-specific post to try to tickle Teddy (if it was he) into commenting.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't understand the XML option. I tried an XML export from Access and when I opened it in Excel all I got was junk. Not sure how I would retain any formatting.

    Pootle, thanks for the comprehensive brain-dump. I'm sure that will save me going down many dead-ends.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    I don't understand the XML option. I tried an XML export from Access and when I opened it in Excel all I got was junk. Not sure how I would retain any formatting.
    I'm not 100% on this (check out this thread for possibly more info) but I think the idea is to create your excel report manually and then save that as XML. Open in Excel and your report is there. Open in notepad\ IE and you can see the XML (incl formatting).

    I'm not sure, though, how you use this to programmatically create an XML file incl the formatting for excel to open.

    Anyway - good luck and let us know if you hit any brick walls.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    attached is a trivial XML file with reasonably stupid formatting that
    ...should open in Excel as a normally edit-able spreadsheet
    ...should open in notepad to reveal the source

    file was an XLS saved in Excel as XML

    top of the file is housekeeping stuff that includes the formatting that Excel made for me

    data block begins with:

    <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1"
    x:FullRows="1">

    ...that row count needs fixing each report (recs# + 1 for the headings). then comes the column headings:

    <Row>
    <Cell sstyleID="s22"><Data ss:Type="String">Name</Data></Cell>
    <Cell sstyleID="s22"><Data ss:Type="String">Rank</Data></Cell>
    <Cell sstyleID="s22"><Data ss:Type="String">Number</Data></Cell>
    </Row>

    then a series of data rows:

    <Row ss:Height="52.5">
    <Cell sstyleID="s23"><Data ss:Type="String">Fred</Data></Cell>
    <Cell><Data ss:Type="String">Private</Data></Cell>
    <Cell sstyleID="s24"><Data ss:Type="Number">1099986</Data></Cell>
    </Row>

    then some more housekeeping stuff.

    it's probably a horrible abuse of XML but it seems to be a real simple way to get formatting into an XLS with Excel doing all the work of defining the Styles for you.

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks for that Izy

    Could I possibly trouble you for the code (or Pseudo code if preferred) that would create that when exporting data for the users? Do you just write everything from the top down to
    Code:
    </Styles>
    into a text file and then iterate through the values and wrap them up in e.g.
    Code:
    - <Cell ss:StyleID="s22">
     <Data ss:Type="String">
    & TheData &
    Code:
    </Data>  </Cell>
    ?????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    leave me a while to put my keyboard where my mouth is. i'll try a slightly more realistic demo (tables n queries n stuff).

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •