Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export to Excel - Header (Access 2002)

    Hi

    I am currently using the following code to export records from a table into an excel spreadsheet:

    Location = "H:Invoice" & Format(Date, "yyyyMMdd") & ".csv"
    DoCmd.TransferText acExportDelim, , "qryInvoiceExport_Query", Location, True

    My problem is the change requested to the output. Specifically, in the first line they wish to add some text (apparently this is used for some purpose when loading into the finance system), followed by the header row and then the records. So the output in the excel spreadsheet should look like this

    Format Batch Standard 1.0 Import
    HEADER1, HEADER2, HEADER3 ETC
    RECORD1, ETC
    RECORD2, ETC

    The first attempt using a union query got the first line in, but below the header row. When changing the DoCmd.TransferText command to not include the header row and amending the union query to put in 'dummy' header rows, I can't get the information in the correct order, regardless of how I play around with the order command. See attached text file giving the union query I have been playing around with. I'm not sure whether this is even the right approach.

    Any suggestions would be helpful.

    Thanks & Regards
    WTH

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

    Re: Export to Excel - Header (Access 2002)

    Does the query in the attached text file do what you want?

    Otherwise, you could simply export the original query including field names, and use Automation to edit the resulting workbook.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel - Header (Access 2002)

    Hi Hans

    No the query does not work. The order is incorrect. I was hoping someone may have a cunning plan on how it could be ordered correctly. I'm reluctant to use automation mainly due to lack of knowledge in that area.

    Regards
    WTH

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

    Re: Export to Excel - Header (Access 2002)

    So what should the sort order be? If you don't provide any infomation we cannot help.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel - Header (Access 2002)

    Hi Hans

    Sorry, should have been clearer. When running the union query as per the attachment in my first post I get the following as per the attached excel file (union_query - sheet 1). When I order it by DOCID DESC I get the headers right but the records are reversed (ie the numbers in DOCID should start at 1) - see (union_query - sheet 2). Sheet 3 shows what it should look like.

    Does that help to explain the problem better?

    Regards
    Warren

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

    Re: Export to Excel - Header (Access 2002)

    Does the attached version do what you want? If there's a problem with alphanumeric sorting, let me know.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel - Header (Access 2002)

    Hi Hans

    Brilliant. Works perfectly and is really obvious after the fact. Saved me heaps of time, many thanks.

    Regards
    WTH

Posting Permissions

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