Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Talking Unanswered: Exporting Query Results To Excel

    Hi All,

    I have a query which produces lets say 10 rows with various fields ie ID, Name, Address, DOB... that kind of thing.

    I know how to export all the results to one workbook, but how do i split each row on to its own seperate work book?

    Thankyou,

    Jnr.

  2. #2
    Join Date
    Aug 2003
    Posts
    106

    Create Excel as Object

    The best way to do this is to create Excel as an object and the paste one row at a time from a recordset

    Dim rst as Recordset
    Dim Excel as Object

    'Set table as a recordset
    set rst = currentdb.openrecordset("YourTable")

    'Loop through the rows of your table one at a time
    Do While not rst.eof

    'Create Excel as an Object
    set Excel = CreateObject("Excel.Application")

    Excel.Application.Visible = False 'Or true if you want to view
    'the workbook while your code is running

    'Create a new workbook
    Excel.Application.Workbooks.Add

    'Type the values into Excel
    Excel.Cells(1, 1).FormulaR1C1 = rst("Field1")
    Excel.Cells(1, 2).FormulaR1C1 = rst("Field2")

    'Save the file if required
    Excel.ActiveWorkbook.SaveAs Filename:="myFile.xls"

    'Make the Workbook visible
    Excel.Application.Visible = True

    Set Excel = Nothing

    'move to next row of table
    rst.movenext
    loop
    rst.close

    hoep this helps :0)
    sugarflux

  3. #3
    Join Date
    Oct 2003
    Posts
    58

    Re: Create Excel as Object

    Originally posted by sugarflux
    hope this helps :0)
    sugarflux
    So do I !....

    only joking much apperciated i will give it a go now, it was the sort of solution i wanted but just didnot know how to code!


    Thanks,

    Jnr.

  4. #4
    Join Date
    Oct 2003
    Posts
    58

    Smile Re: Create Excel as Object

    Hi all,

    really i wanted to say,

    For Each row in the query

    export to excel

    next row

    How can i code this?

Posting Permissions

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