Write Base(0) Array to Cell Range

[The Vision Thing]

I'm using the following code to write a database recordset to an array.

Set objConn = New ADODB.Connection
Set rsSet = New ADODB.Recordset
objConn.ConnectionString = connString
objConn.Open
rsSet.Open sqlString, objConn
If Not (rsSet.BOF Or rsSet.EOF) Then
arrFieldTypes = rsSet.GetRows()
End If

I want to write the array 'arrFieldTypes' to a cell range, but the GetRows
method always creates a base(0) array even if the module is explicitly set
to base(1). So the following code always fails as it expects a base(1)
array.

Sheet.Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn)) =
arrFieldTypes.

Is there a quick and easy way to convert a base(0) array into a base(1)
array or do I need to write a function for it or can I force the GetRows()
method to create a base(1) array?

Thanks,
Wayne C.

[Tom Ogilvy]

I think you are having some other type problem.

Sub Base1Write()
Dim myArray(1 To 10)
For i = 1 To 10
myArray(i) = i ^ 3
Next
Range("A1").Resize(1, 10).Value = myArray
End Sub

writes a base 1 array to a range with no problem.

2D base1 array - again, no problem:

Sub Base1Write2D()
Dim myArray(1 To 10, 1 To 3)
For i = 1 To 10
For j = 1 To 3
myArray(i, j) = (i ^ 3) * j
Next
Next
Range("A1").Resize(10, 3).Value = myArray
End Sub

Regards,
Tom Ogilvy

The Vision Thing <mi...@etelligence.com> wrote in message
news:#PGDC1YP...@TK2MSFTNGP10.phx.gbl...

[The Vision Thing]

Tom,

I'm not having a problem writing a base(1) array to a range. My problem is
the getRows() recordset method generates a base(0) array from the recordset,
even if the option base method is set to 1. I either want to force getRows
to generate a base(1) array or convert a base(0) array to a base(1) array.

Regards,
Wayne C.

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:%239WKxEZ...@tk2msftngp13.phx.gbl...

[Alan Beban]

Notwithstanding the stated subject of the OP, I think he is concerned
not with writing to the range per se, but with the fact that he wants
the array to be base 1 before it is written to the range (because
otherwise he gets an error message with his code to write it to the
range, I guess because his FirstRow and FirstColumn variables are set
equal to the lower bounds of the arrays dimensions).

Redefining the FirstRow and FirstColumn variables might be the way to
fix it. Or he can write a function or, if the functions in the file at
http://home.pacbell.net/beban are available to the OP's workbook, he can
use the ConvertBase function, a la

Dim myArray()
ReDim myArray(0 To 3, 0 To 4)
myArray = ConvertBase(myArray, 1, 1)
MsgBox LBound(myArray) & " " & LBound(myArray, 2) '<--displays 1 1

Alan Beban

[The Vision Thing]

Dear Alan,

Your ConvertBase function was exactly what I was looking for, for which I am
extremely grateful.

Essentially my problem was:
a) I believe you cannot directly write an array to a spreadsheet range
unless it is base(1).
b) The recordset getRows() method only generates base(0) arrays from a
recordset, irrespective of the option base setting at the top of the module.

Many thanks,
Wayne C.

"Alan Beban" <be...@pacbell.net> wrote in message
news:3EFDCB8C...@pacbell.net...

[Alan Beban]

You're welcome. I directed you to the website, but the ConvertBase
function is independent of the other procedures in the file at the
website, so I can email it to you so you can avoid downloading the file
of procedures from the website. If you already have downloaded them,
fine; if not and you would like me to email the ConvertBase function
separately, let me know by email.

By the way, your belief in a) below is incorrect; e.g.

Sub testab2002()
Dim myArray(), i As Integer
ReDim myArray(0 To 3)
For i = 0 To 3
myArray(i) = i
Next
MsgBox LBound(myArray) '<----displays 0
Range("A3:D3").Value = myArray '<---this works
End Sub

Though you didn't post the code that would clarify it, I believe your
problem was not that you can't dump a 0-based array to the worksheet,
but the fact that in your particular code your FirstRow variable was 0,
so Excel didn't understand . . .Cells(0, . . .

Alan Beban

[Alan Beban]

By the way, although I suggested code that called the function with

myArray = ConvertBase(myArray, 1, 1)

the folowing also works:

ConvertBase myArray, 1, 1

Alan Beban

[Tom Ogilvy]

Alright, no problem writing a base 0 array:

Sub Base1Write()
Dim myArray(0 To 9)
For i = 0 To 9

myArray(i) = i ^ 3
Next
Range("A1").Resize(1, 10).Value = myArray
End Sub

Sub Base1Write2D()
Dim myArray(0 To 9, 0 To 2)
For i = 0 To 9
For j = 0 To 2

myArray(i, j) = (i ^ 3) * j
Next
Next
Range("A1").Resize(10, 3).Value = myArray
End Sub

Regards,
Tom Ogilvy

The Vision Thing <mi...@etelligence.com> wrote in message

news:OWd43bZP...@TK2MSFTNGP11.phx.gbl...

[Tom Ogilvy]

By the way, this worked for me:

Sub TesterVision()
Dim DBFullName As String

Set objConn = New ADODB.Connection
Set rsSet = New ADODB.Recordset

DBFullName = "C:\Program Files\Microsoft" & _
" Office\Office\Samples\Northwind.mdb"

connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
objConn.ConnectionString = connString
objConn.Open
rsSet.Open "Employees", objConn

If Not (rsSet.BOF Or rsSet.EOF) Then

arrFieldtypes = rsSet.GetRows()
lb1 = LBound(arrFieldtypes, 1)
ub1 = UBound(arrFieldtypes, 1)
lb2 = LBound(arrFieldtypes, 2)
ub2 = UBound(arrFieldtypes, 2)
For i = lb1 To ub1
For j = lb2 To ub2
If i = 14 Then
arrFieldtypes(i, j) = "Photo"
End If
Next
Next
Range(Cells(1, 1), _
Cells(ub1 + 1, ub2 + 1)).Value = _
arrFieldtypes
End If

End Sub

Using the Northwind.mdb Employees table. One of the fields apparently
contains blobs (photos), so I had to remove these before I could write the
array to the worksheet. Also, the array/recordset is transposed from what
you would expect it to be - each record is in a separate "column".

But after clearing the photos, I could write it to the sheet.

Regards,
Tom Ogilvy

The Vision Thing <mi...@etelligence.com> wrote in message

news:OWd43bZP...@TK2MSFTNGP11.phx.gbl...