Using "GetString" with ADO

[Guest]

Hello, I am using Excel XP with Win 2000 and ADO 2.5.

1) When converting an ADO recordset into a string using "GetString", does
anyone know of any bugs, row limitations, length or size restrictions, etc.
with this method?

I'm considering using this method for importing data when there is too much
to fit into an Excel sheet. Alternatively, I will port the data into a CSV
text file and I just wanted to know how stable this method is. I would use
the syntax shown below:

rsADO.GetString(adClipString, , ",", vbCr)

2) Also, does anyone know if this method automatically (internally)
surrounds each column with double quotation marks? If not how can I get it to
do this? Please illustrate the syntax.

Thanks much in advance.

 

[The Vision Thing]

I haven't used GetString myself, but I use getRows a lot to write a
recordset into an array

Dim arrRs as variant
arrRs = rsAdo.getRows

You can then write the array to the spreadsheet (you'll need to convert it
from base 0 to base 1) or write it to a text file.

Let me know if you need further code to do this.

Regards,
Wayne C.

 

[Jamie Collins]

I haven't used GetString myself, but I use getRows a lot to write a
recordset into an array
You can then write the array to the spreadsheet (you'll need to convert it
from base 0 to base 1)

There is no need to convert the array. To test:

Sub test()

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

With rs
.CursorLocation = 3
.Fields.Append "Col1", 12
.Fields.Append "Col2", 12

.Open

.AddNew _
Array("Col1", "Col2"), _
Array("A1", "A2")

.AddNew _
Array("Col1", "Col2"), _
Array("B1", "B2")

.AddNew _
Array("Col1", "Col2"), _
Array("C1", "C2")

.Update

Dim vntArray As Variant
.MoveFirst
vntArray = .GetRows

.Close

End With

' Prove it's a base zero array
MsgBox vntArray(0, 0)

' Write base zero array to Range
Sheet1.Range("A1:C2").Value = vntArray

End Sub


Jamie.

--

 

[Jamie Collins]

I use getRows a lot to write a
recordset into an array
You can then write the array to the spreadsheet (you'll need to convert it
from base 0 to base 1)

By amazing coincidence, on an unrealted matter I just turned up this
thread from the archive:

http://groups.google.com/[email protected]

Tom Ogilvy was correct: no problem writing a base 0 array to a Range.
You replied, "I believe you cannot directly write an array to a
spreadsheet range
unless it is base(1)" and no one at the time reasserted that your
assumption was incorrect.

Jamie.

--