Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Sending data to Excel

    I am using Access97 and Excel97 to create graphs of data extracted from Access tables, and using a procedure based originally on code in the example in the "Access97 Developer's Handbook" by Litwin, Getz and Gilbert. To transfer the data and load the Excel cells, I am using a line like:
    objXLRange.FormulaArray = objXLApp.Transpose(varResults)
    where varResults is a variant array with the required data.

    It works like a charm except in the following situations, and so I would be very grateful for any suggestions:

    1. If there are any null cells in varResults, the operation fails. My workaround has been to replace nulls by a character, and then later instruct Excel to blank out cells with that character, but it is slow and messy.

    2. I am trying to transfer dates, and Excel interprets them correctly if the date is unambiguous (eg 13-Jan-2001) - my computer uses the short date style of "dd/mm/yy" by default. However, for dates like 3-Apr-2001 there seems to be no way to tell Excel how to interpret the date correctly. For example, if I set varResults(0,1) = #3-Apr-2001# and then use the above function, the result in Excel is "4-Mar-2001". My workaround has been to change all dates to numbers, and send them to Excel, but again, it seems there should be a "correct" way to do it! Any suggestions, please?

    Thanks.
    Geoff

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending data to Excel

    I don't know if this is any help or not, but I alwys use the CopyFromRecordset method to dump data into Excel from Access:<pre>WS.Range("A2").CopyFromRecordset Rst</pre>

    (where WS is a Worksheet object and Rst is a DAO Recordset).

    As far as I know it should handle nulls without any problem.

    As for the date problem, I'm not sure. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Sending data to Excel

    Thanks Mark. There are usually several ways to solve a problem, and your suggestion has been most helpful. It seems to solve both problems!
    Geoff

Posting Permissions

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