Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO GetRows and Excel (2000)

    Thank for the replies to my earlier posting which I am digesting. I have another problem:

    I am using more code modified from the Getz/Gilbert/Litwin Handbook to copy an Access recordset into a newly created Excel spreadsheet. The book recommends creating an ADO recordset, using the GetRows method to populate an array and then to pass this to Excel using the following line:

    objXLRange.FormulaArray = objXLApp.Transpose(varResults)

    where objXLRange is an Excel range object, objXLApp is an Excel application object and varResults is the array.

    Unfortunately this line gives a Run-time error 13, Type mismatch error.
    varResults is dimensioned as a Variant and in the immediate window '? UBound(varResults, 2)' returns 1827 which is the number of records in the array, so I assume it must be an array.
    objXLRange is created using the line:

    Set objXLRange = objWorkSheet.Range("A4:K" & 4 + UBound(varResults, 2))

    There are 11 columns to export hence the cell range from A to K.

    Can anybody think why I am getting this error.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO GetRows and Excel (2000)

    I suspect it's a question you might need to ask in Excel or VB/VBA.
    Charlotte

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

    Re: ADO GetRows and Excel (2000)

    Transpose is not a method of the Excel Application object, but a worksheet function. Try

    objXLRange.FormulaArray = objXLApp.WorksheetFunction.Transpose(varResults)

Posting Permissions

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