none
[Excel + SQL + VBA] Copying text cell to another cell not done at 100%

    Question

  • Hi everyone,

    I use this SQL line to copy text from a cell to another cell:

    'Overall Comments
    Set rs = cn.Execute("SELECT [ColumnName2] FROM [sheet2$] WHERE [ColumnName1]= '" & Sheets("sheet1").Range("A1") & "' AND [ColumnName3]= '" & Sheets("sheet1").Range("B1") & "' AND [ColumnName4]= '" & Sheets("sheet1").Range("C1") & "'")
            .Sheets("sheet1").Range("D1").CopyFromRecordset rs

    The issue I'm having is that if the text in the original cell is too long, then just portion of it will be copied in the destination cell!

    How can I fix it, please?

    Thursday, December 06, 2012 3:21 PM

All replies

  • Hi everyone,

    fixed, the OLEDB engine was the problem and replacing it by the JET fixed the issue!

            If Application.Version < 12 Then
                cn.ConnectionString = _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & .FullName & ";" & _
                  "Extended Properties=Excel 8.0"
            Else
                cn.ConnectionString = _
                  "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & .FullName & ";" & _
                  "Extended Properties=Excel 8.0"
            End If

    Regards
    • Marked as answer by Admin-Dev Thursday, December 06, 2012 7:13 PM
    • Unmarked as answer by Admin-Dev Tuesday, December 11, 2012 11:08 PM
    Thursday, December 06, 2012 7:13 PM
  • Hi Admin-Dev,

    I'm glad to hear you have solved your issue. Thanks for sharing your experience here, it really beneficial for other community members who have similar questions to see how you solved it.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 07, 2012 6:27 AM
  • Hi Tom,

    I have Excel 2010, unfortunately it only work for me, those who have Excel 2003 still can`t full copy the content...

    Any idea how to fix it, please?

    Tuesday, December 11, 2012 11:09 PM
  • Hi Admin-Dev,

    Sorry for later response. I will do further research on it, and consult Microsoft's engineers for it. I will back if I have new update.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 17, 2012 7:19 AM