Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004

    Unhappy Unanswered: really stuck for ideas on exporting to excel

    Hi everyone.

    I'm not very experienced using Access (2k) but I need to do something a little involved.

    I already have a form which runs some VB that performs multiple queries but I now need to transfer the results to an excel spreadsheet with one worksheet for each result.

    I have used DoCmd.TransferSpreadsheet and this works fine for one worksheet but I cant use it for multiple ones.

    The alternative seems to centre around
    Set rs = db.OpenRecordset(sqlString)

    Then controlling where it ends up on the spreadsheet with:
    While rs.EOF <> True

    .Worksheets(1).Cells(TotalsRow, 1) = rs![Type]

    This would be ideal for me because my queries are generated via SQL so it would integrate well with what I have already.

    However, I've spent about 2 frustrating days trying to get this to work with no joy. It always throws an error when it gets to
    Set rs = db.OpenRecordset(sqlString)
    complaining of type mismatch. I believe this has something to do with the fact access has changed recordsets from DAO to ADO as default so you need to explicitly declare rs as dao.recordset not just recordset.

    Every time I do this access completely freezes, the process takes up about 99% of CPU activity and I have to shut it down.

    ummm, in short.... I'd really appreciate some guidance from you guys.


  2. #2
    Join Date
    Feb 2005
    The Hague, Netherlands
    I'm not too experienced too, but read this: In VBA, click on the Tools Menu, and select References. Scroll down to the Microsoft DAO 3.6 Object Library. If it is not checked, check it. That will ensure you have the proper commands and features available to you.
    Maybe that is the problem :E

  3. #3
    Join Date
    Dec 2004
    hey thanks for the tip and the quick response.

    wow its fixed - for some reason some of my old unused code was causing the crash.

    I've got it working nice now - sending recordset contents into nice separate worksheets in excel.

    my only remaining niggle is they are called sheet1, sheet2 e.t.c

    do you know of a way to rename these through code?

    thanks again

  4. #4
    Join Date
    Dec 2004

    oh wait -
    Set objSheet2 = objWorkbook.Worksheets(2)
    objSheet2.Name = "Two"

    seems to work a treat.

Posting Permissions

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