Results 1 to 6 of 6
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Report / Data suggestion (2003/SP 2)

    Looking for suggestions here.

    Have a table for example with Name, Date, hours. (Holds hours charged each week by Name). Users wanted a report that show hours per week with the weeks going across the top of the page (horizontally) and names vertically and have the data sent to excel. Created a crosstab report and sent to excel without any problem.

    Now users want to add a column after each week that takes the total hours worked and divides the hours by 40 to show percent worked each week by person. So report or output would look like Name, Wk1, Pct hrs wk 1, Wk2, Pct hrs Wk 2, etc.

    What is the best way to do this. Can't do it "neatly" with a crosstab. Did write some code selecting the crosstab as a recordset to transfer the data to excel then had the code add in the pct weekly column while the data was transferring to excel but is takes too long to process. Perhaps transferring the crosstab data to excel and then manipulating excel would work. Looking for some suggestions on what to do here as I can't think of an easy way to do this.
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report / Data suggestion (2003/SP 2)

    It's probably easiest to run a macro in Excel on the exported data:

    Sub InsertCols()
    Dim lngC As Long
    Dim lngMaxC As Long
    Dim lngR As Long
    Dim lngMaxR As Long
    lngMaxR = Cells(Rows.Count, 1).End(xlUp).Row
    lngMaxC = Cells(1, Columns.Count).End(xlToLeft).Column
    For lngC = lngMaxC + 1 To 3 Step -1
    Columns(lngC).Insert
    Cells(1, lngC) = "Perc. " & Cells(1, lngC - 1)
    With Range(Cells(2, lngC), Cells(lngMaxR, lngC))
    .FormulaR1C1 = "=RC<!t>[-1]<!/t>/40"
    .NumberFormat = "0.0%"
    End With
    Next lngC
    End Sub

    If you want to run this from Access, you'd have to use Automation to start an instance of Excel, open the workbook, and then fully qualify each Excel object.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Report / Data suggestion (2003/SP 2)

    Wow,

    Thanks. This is much more then I was expecting. Let me try it out and I will let you know how it goes. ... and yes, I am using automation to control excel from access and will make the necessary adjustments.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Report / Data suggestion (2003/SP 2)

    Thank you, Thank you, Thank you

    That is one great solution. Works great. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Report / Data suggestion (2003/SP 2)

    The final code using automation from Access to Control Excel is shown below. Note that each excel reference IS fully qualified or the code will fail the second time it is run due to errant references. For this example, the excel file is created after selecting a report name from a combo box (Combo_SelectReport). The after update event of the combo box transfers a crosstab query data to excel. The data in excel is then manipulated using automation. Thanks again to Hans for helping with the code.

    <pre>Private Sub Combo_SelectReport_AfterUpdate()

    Dim stDocName As String
    Dim xlapp As Excel.Application
    Dim xlwbk As Excel.workbook
    Dim xlsht As Excel.worksheet
    Dim sheet As Object
    Dim strname As String
    Dim lngC As Long
    Dim lngMaxC As Long
    Dim lngR As Long
    Dim lngMaxR As Long

    Select Case Me.Combo_SelectReport

    Case "PCT IDL by Empl"

    'Create Excel File from crosstab query but do not start excel
    DoCmd.OutputTo acOutputQuery, "PCT IDL by Empl", acFormatXLS, "C:PctIdlByEmpl.xls", 0
    strname = "C:PctIdlbyEmpl.xls"

    'Start Excel
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Visible = True
    Set xlwbk = xlapp.Workbooks.Open(Filename:=strname)
    Set xlsht = xlwbk.Worksheets("PCT IDL by Empl")

    'Manipulate Excel Data
    lngMaxR = xlsht.Cells(xlsht.Rows.Count, 1).End(xlUp).Row
    lngMaxC = xlsht.Cells(1, xlsht.Columns.Count).End(xlToLeft).Column
    For lngC = lngMaxC + 1 To 6 Step -1
    xlsht.Columns(lngC).Insert
    xlsht.Cells(1, lngC) = "Pct" ' & .Cells(1, lngC - 1)
    With xlsht.Range(xlsht.Cells(2, lngC), xlsht.Cells(lngMaxR, lngC))
    .FormulaR1C1 = "=RC[-1]/40"
    .NumberFormat = "0.0%"
    End With
    Next lngC

    xlsht.Cells.Select
    xlsht.Columns.AutoFit

    Case Else ' Other values.

    End Select

    Set xlsheet = Nothing
    Set xlwbk = Nothing
    Set xlapp = Nothing

    End Sub
    </pre>

    Regards,

    Gary
    (It's been a while!)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report / Data suggestion (2003/SP 2)

    Thanks for sharing the final result.

Posting Permissions

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