Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    13

    Unanswered: Trying to open and populate an Excel sheet.

    I am trying to open an Excel sheet an populate the cells with data from a MySql table. The Excel sheet opens, but then I get the error "runtime error 2147467259 Method 'Copy from Recordset' of object 'Range' failed". Here is my code:
    Code:
    Dim rs2 As ADODB.Recordset
    Dim DB2 As New ADODB.Connection
    Dim sSQL2 As String
    
    sSQL2 = "select * from item_tracker where user_name like ('" & cmboUserList.Text & "');"
    DB2.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False; UID=root@localhost ; database=mysql; option=3"
    DB2.Open "data source=myodbc"
    DB2.Execute ("USE Mysql;")
    Set rs2 = New ADODB.Recordset
    rs2.CursorType = adOpenKeyset
    rs2.LockType = adLockOptimistic
    rs2.CursorLocation = adUseClient
    rs2.Open sSQL2, DB2, adOpenForwardOnly, adLockReadOnly, adCmdText
    Dim xlApp As New Excel.Application
    Dim xlBook As Workbook
    Dim xlws As Worksheet
    Set xlws = xlApp.ActiveSheet
    Set xlBook = xlApp.Workbooks.Add
    xlApp.Visible = True
    Set xlws = xlBook.Worksheets("Sheet1")
    xlws.Cells.CopyFromRecordset rs2
    Any help would be appreciated. Thank you very much for your time.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Inistead of using automation, why not use ADO to connect to the Excel spreadsheet? Then, you could insert records into it using the JET provider...

    ref Using ADO with Excel from one of our sister sites.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Feb 2007
    Posts
    13
    Thanks for your reply. I am too much of a newbie to try different connections. Any suggestions? Thanks again.

  4. #4
    Join Date
    Feb 2007
    Posts
    13
    I can open the Excel sheet, but when I get to
    Code:
    xlws.Cells(2, 1).CopyFromRecordset rs2
    I get the error.

  5. #5
    Join Date
    Mar 2007
    Posts
    5
    I dont think you specified a range, maybe the following line would help instead of using xlws.Cells.CopyFromRecordset rs2.
    Code:
    xlws.Range("A1").CopyFromRecordset rs2
    If that doesn't work this bit of code works for me:
    Code:
    sData = rs.GetString(adClipString)
    Clipboard.Clear
    Clipboard.SetText sData, vbCFText
    Set xlBook = xlApp.Workbooks.Add
    xlApp.Visible = True
    Set xlws = xlBook.Worksheets("Sheet1")
    xlApp.ActiveWorkbook.ActiveSheet.Range("A1").Select
    xlApp.ActiveWorkbook.ActiveSheet.Paste

  6. #6
    Join Date
    Feb 2007
    Posts
    13
    I added the ("A1") and that did it. Thanks so much!!!

Posting Permissions

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