Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Question Unanswered: Export Access 2003 report to Excel doesn't follow the format of the report

    I have a problem with exporting a report to excel.
    I used : DoCmd.OutputTo acOutputReport to export an existing report to Excel.
    When i export a simple report with the following collumsate , Activity, Time and "Notes" the Excel sheet has the following collums Notes, Date, Activity, Time.

    I can't find the reason why the Notes colum is put in front of the rest.
    Any help is appriciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The objects are exported according to the internal index number they have, not in the order they appear on the screen or on paper.

    Try this code:
    Code:
    Function ListControls()
    
        Dim obj As Object
        Dim ctl As Control
        Dim i As Integer
        
        ' For a report
        DoCmd.OpenReport <ReportName>, acDesign
        Set obj = Reports(<ReportName>)
        
        ' For a form
        DoCmd.OpenForm <FormName>, acDesign
        Set obj = Forms(<FormName>)
        
        ' Loop through the collection of controls
        ' i is the index order of the control in the 
        ' Controls collection of its parent object (form or report)
        For i = 0 To obj.Controls.count - 1
            Set ctl = obj.Controls(i)
            Debug.Print i, obj.Controls(i).Name
        Next i
    
        ' For a report
        DoCmd.Close acReport, <ReportName>
        
        ' For a form
        DoCmd.Close acForm, <FormName>
        
    End Function
    If you want to export in a determined order you should export from a query that you could dynamically build from the report, using its RecordSource, OrderBy and Filter properties.

    Have a nice day!

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    I run your small function on the report which i try to export to excel.
    If i understand it correctly the field with lowest id will be the first collum and the highest Id the last collum
    So this means :[date_logged],[activity],[time spend],[additional notes]
    But in excel i get [additional notes],[date_logged],[activity], [time spend]
    0 Label8
    1 start-date
    2 end-date
    3 Label29
    4 Date_Logged_Label
    5 Activity_Label
    6 Time Spend_Label
    7 Label15
    8 Label16
    9 Line34
    10 Label35
    11 Date_Logged
    12 Activity
    13 Time Spend
    14 TOE
    15 P&I
    16 TimeWorked
    17 Aditional notes
    18 total timespend
    19 Label20
    20 Line30
    21 sumofworkedhours
    22 Text9
    23 Text10
    24 Line12

    regards

Posting Permissions

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