Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: Excel reports from Access

    How to produce an Excel reports (Formatted and with SubTTLs) from Access?
    At this time I manually format and subttl 30 reports.
    Need help to automate this process

    Thank you for your help.

    Below please find Columns:
    I need subttl for all columns based on Units and Dollars.

    Director
    RegionDescription
    AcctExec
    Name
    SalesGroup
    ProductGroup
    Fashion/Basic
    FabricGroup
    Style_Number

    Total Units
    Total Dollars
    Jan'07 U
    Jan'07 $
    .
    .
    Dec'07 U
    Dec'07 $

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can use Access VBA to create an Excel spreadsheet/workbook formatted any way you like, even down to pretty borders and a final print ready solution.

    Theres soem excellent code in the code bank from that little weasel Pootleflump, well actually a kind of good guy if you ignore the flumpiness. which sad to admit is very well thought out, well executed and a damn good starting point for this sort of thing.

    you do need to be up to speed with developing code using VBA.

    one of the tricks to developing code in Excel is to use the macro recorder.. the same tricks work when developing code in Access. use the Excel macro to record what you want to do and then port that code (use cut & paste) into ahe access code window.. you need to chage a few things so the VB code knows you are suign a specific Excel instance but it works.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    that little weasel Pootleflump
    That's it buddy - you just made the ignore list. There'll be no flumpiness where you are concerned. That'll learn ya.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Thank you for the code bank - I found the ExcelAutomation_V4.zip
    But I'm having bit dufficulty converting this code to my use. I am a beginer in vb programming. I got to the point where the app takes and loads my data to xls, but i cannot make it work to do subttl. The message I have application-defined or object-defined error?


    Also, how to adjust this code to use my columns.
    I have TotalUnits, TotalDollars and monthly brke in U and $ (Jan U, Jan $.....to Dec U, Dec $). Hpe Changes I made - the correct once.?

    ''''''If the field is a Quarter field....
    '((((((((((((((((((((((((((((((((((((((((((((((((( (((((((((((((((((((((((((((
    'This is somewhere where you could make use of meta data.
    'Have a table that includes report level information
    'including the field to group on.
    'Relate that to a table of fields to sum a subtotal report on.
    'You can then code a generic class that looks up arguments
    'for this sort of thing rather than hard coding for each report.
    '))))))))))))))))))))))))))))))))))))))))))))))))) )))))))))))))))))))))))))))

    If Right(fld.Name, 2) = "Total Units" Or Right(fld.Name, 2) = "Total Dollars" Or Right(fld.Name, 2) = "U" Or Right(fld.Name, 2) = "$" Then

    If TotCols(0) <> 0 Then
    ReDim Preserve TotCols(UBound(TotCols) + 1)
    End If
    'Write the ordinal position (compensating for the collection being zero based) to array
    TotCols(UBound(TotCols)) = i + 1

    End If

    Next i


    ')))))))))))))))))))))SUB TOTAL PART))))))))))))))))))))))))))))))))))))))))))))

    'Instantiate range object to make the method a little more readable
    Set exRange = exSheet.Range(exSheet.Cells(HEADER_ROW, LEFTMOST_COL), exSheet.Cells(HEADER_ROW + NoOfRows, NoOfCols + LEFTMOST_COL - 1))

    'Apply subtotal using the array we populated when writing the headers
    exRange.Subtotal rs.Fields("Director").OrdinalPosition + 1, xlSum, TotCols(), , False, xlSummaryBelow

    How to apply subtotal using the array above, not only for Director Column, but for all columns listed:
    Director
    RegionDescription
    AcctExec
    Name
    SalesGroup
    ProductGroup
    Fashion/Basic
    FabricGroup
    Style_Number

    Thank you so much for all the help!!!

  5. #5
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    I was able to pass by all the errors, again - leave and learn.!!!

    Now my question is - how to remove GrandTotal after its Subtotaling by Each Col name?

    'Apply subtotal using the array we populated when writing the headers
    exRange.Subtotal rs.Fields("Director").OrdinalPosition + 1, xlSum, TotCols(), False, xlSummaryBelow

    exRange.Subtotal rs.Fields("Region Description").OrdinalPosition + 1, xlSum, TotCols(), , False, xlSummaryBelow

    exRange.Subtotal rs.Fields("Acct Exec").OrdinalPosition + 1, xlSum, TotCols(), , False, xlSummaryBelow

    exRange.Subtotal rs.Fields("Name").OrdinalPosition + 1, xlSum, TotCols(), , False, xlSummaryBelow

    I need Grand Total only once - at the end.

    Grand Total
    BROWN OFF PRICE Total
    Grand Total
    Off Price Total
    Grand Total
    Alles Total
    Grand Total

    When in Excel Subttl window you may unclick the Replace current subttl, but how to make it work via vba code?

    Thank you

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ypal
    When in Excel Subttl window you may unclick the Replace current subttl, but how to make it work via vba code?
    I'm afraid I don't know quite what you mean nor have time to play around but as Healdem mentioned you can use the Excel Macro recorder and manually make the changes. Excel will then write the vba code that you can pick up and adapt.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Thank you, I will try to use Excel macro recorder

Posting Permissions

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