Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export report to Excel and format (2000/XP)

    Having painstakingly created a report to mimic output previously typed into an Excel spreadsheet, I've now been told the output is also required in Excel.

    I have exported queries/tables before using transfer spreadsheet and done some basic formatting (such a column A width = x, text= 12pt BOLD etc). This was a few good years ago thought.

    Just wondering if someone has an example of doing this where their report includes group headings and the detail section may contain subreports.

    Edit: Have had a quick look at report as exported fromt he file menu - it doesn't look 'too' bad. However, I have 2 major issues.

    1. Text is truncated - huge problem as there is a memo sized 'remarks' column
    2. The second column contains text similar to 1/1, 1/1a, 1/2, 1/2a, 1/2c etc - the text without letters has turned into numbers e.g. 1/1 =39448, 1/2 = 39479, 1/3 = 39508 ???????

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Export report to Excel and format (2000/XP)

    Thinks you may be up for quite a deal of VBA programming to do what you want.

    With that text field you could create the illusion of a text field by appending a space to the end of that field in the access query.

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

    Re: Export report to Excel and format (2000/XP)

    I think you'd be better off exporting a table or query, and modifying the resulting worksheet as needed.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export report to Excel and format (2000/XP)

    That's kind of what I'm after really.

    I've tried various things and it almost looks ok with mimimal manual modification needed. Unfortunately, on closer inspection I've found on exporting a report with many subreports (each one as a column), the subreport data gets put in the same column in excel.

    Fortunately, there are <500 records so even if I come up with some clunky inefficient code it shouldn't take too long to run it.

    I think I need to do something with nested recordset loops, i.e first record, paste the columns into excel, move to query for 1st related table, paste data etc, somehow counting so I can move around cells in the worksheet using relative offsets.

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

    Re: Export report to Excel and format (2000/XP)

    Good luck! Feel free to ask questions if needed.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export report to Excel and format (2000/XP)

    Thanks, I don't think it's going to be as hard as I thought.

    I found this https://www.planet-source-code.com/vb/scrip...=47409&lngWId=1 and this http://www.aspfree.com/c/a/Database/Data-A...from-Excel-VBA/ pretty helpful.

Posting Permissions

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