Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Export Report (Office2000)

    I

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting Export Report (Office2000)

    Hi,
    To get the dates in the right format, you could change your query to use something like:
    datedata: Format([datefieldhere],"mm/dd/yyyy")
    If you really want all the totals as positive numbers even if they are actually negative you could use something like:
    PosSum: abs([totalfieldhere])
    If you're using an OutputTo macro I think you're stuck with the headings, though you can change what they will be by changing the names in the query (eg using the examples above, your headings would be datedata and PosSum)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Export Report (Office2000)

    Thank you Rory,
    I want to display all the headings except for one, Is there anyway I could do this? Anyway at all.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting Export Report (Office2000)

    Sort of. Open the query in design view, right-click somewhere in the field that you don't want the heading for, and choose properties. Then under Caption, enter a space. When the export is run, you will still have a column heading (just a space) but it won't look like you do! Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Export Report (Office2000)

    I tried it but it doesn't seem to work Rory. No worries. Thanks alot for the help. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting Export Report (Office2000)

    Strange - it works on mine. Does the column heading appear when you run the query within Access? There's also the VBA alternative if you don't mind coding.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Export Report (Office2000)

    The following is what I had in the caption of that feild name: " "
    yeah, the heading appears when I run the query.
    Nah, When it comes to Visual basic, I have no idea what Im doing. so I better leave that out.

    Thanks Rory.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting Export Report (Office2000)

    Did you actually enter the quotes or was that just to show there was a space in your post? You shouldn't enter the quotes in the Caption otherwise your heading will be " ".
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Export Report (Office2000)

    Tell me exactly how i should enter space, please.
    I thought that was how you enter a space by having a space in between quotes. I must be wrong then. I entered the quotes as well but my heading didn't change at all.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting Export Report (Office2000)

    Hi,
    I'm surprised your heading didn't change at all! In design view, right-click the column for the field you don't want the caption for, choose properties (which brings up a Field Properties dialog with two tabs on it) and click in the Caption box then just press the space bar. Save the query and your column heading should have changed to a space (i.e. it looks blank). Is this not what happens for you?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Export Report (Office2000)

    It works, it works.
    THANK YOU SOOOOOO MUCH.
    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    It works on the query but not on the exported report. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting Export Report (Office2000)

    Hi,
    When you say "not on the exported report", I presume you mean the exported query? What does your macro do and how exactly?
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Export Report (Office2000)

    When I said not in the exported report, I meant not in the spreadsheet. The following is the code of the macro Im using to export:
    '------------------------------------------------------------
    ' ExportSFMReport
    '
    '------------------------------------------------------------
    Function ExportSFMReport()
    On Error GoTo ExportSFMReport_Err

    ' Turn System warnings off
    DoCmd.SetWarnings False
    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    ' Run Append query to add SFM records to the table
    DoCmd.OpenQuery "AppendToSFMReportSource", acNormal, acEdit
    ' Turn System warnings on.
    DoCmd.SetWarnings True
    ' Export records to spreadsheets
    DoCmd.TransferSpreadsheet acExport, 8, "SFMTradeReport", "S:SRI_WORK_AREATRADEA~1TRADES~2.XLS", True, ""
    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    ' Display message
    Beep
    MsgBox "Data has been exported successfully.", vbInformation, "Export Confirmation"
    ' Open spreadsheet with records
    Call Shell("P:Office2000ProfPFILESMSOFFICEOFFICEexcel.e xe S:SRI_WO~1TRADEA~1TRADES~2.XLS", 1)
    Exit Function


    ExportSFMReport_Exit:
    Exit Function

    ExportSFMReport_Err:
    MsgBox Error$
    Resume ExportSFMReport_Exit

    End Function

    Thank you.

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formatting Export Report (Office2000)

    I thought you didn't like code? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>That's VBA, not a macro!
    Try changing your code to this:
    Function ExportSFMReport()
    On Error GoTo ExportSFMReport_Err

    ' Turn System warnings off
    DoCmd.SetWarnings False
    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    ' Run Append query to add SFM records to the table
    DoCmd.OpenQuery "AppendToSFMReportSource", acNormal, acEdit
    ' Turn System warnings on.
    DoCmd.SetWarnings True
    ' Export records to spreadsheet and open it
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, "S:SRI_WORK_AREATRADEA~1TRADES~2.XLS", True

    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    ' Display message
    Beep
    MsgBox "Data has been exported successfully.", vbInformation, "Export Confirmation"

    Exit Function


    ExportSFMReport_Exit:
    Exit Function

    ExportSFMReport_Err:
    MsgBox Error$
    Resume ExportSFMReport_Exit

    End Function

    and see if that does the trick.
    Hope it helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Export Report (Office2000)

    I don't like the code but I've converted the macro into visual basic code and sent the to you.
    THAT WORKS PERFECTLY FINE! THANK YOU soooooooooooooooooooooooooooooo Much <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/kiss.gif border=0 alt=kiss width=15 height=15>

    What did you do to that code anyway?

Page 1 of 2 12 LastLast

Posting Permissions

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