Excel VBA Runtime error 429 ActiveX Component can't create object.

Question

Split from this thread.

i) i changed the code in my VBA Code as per comments,

It is working fine & select Empty cell also and I change the code for add new column as per below , it is working

Set rng = ws. Range("A2:AA" & lastRow)

ii) But I copy the full code & run , the error has come

Answer 1

Remove the space from "ADODB. Connection" so that it reads "ADODB.Connection")

Answer 2

The following error has come. after removing the space

Answer 3

row.Value is a two-dimensional array. Join works only with one-dimensional arrays.

Try this instead:

        values = "'" & Application.TextJoin("','", True, row) & "'"

Answer 4

I am using excel version 2010 & after changing the code. The following error has come

Answer 5

OK - TextJoin is not available in Excel 2010.

Replace the values = ... line with

        Dim cel As Range
        For Each cel In Row
            values = values & "','" & cel.Value
        Next cel
        values = Mid(values, 3) & "'"