Arrays and Join Function

[Stefano Condotta]

Hello,

This code fails on "Wholeline=Join(vCellArray, sSeperator)". When a two
dimensional array of N rows and M columns with M being greater than 1 is
passed to the routine, the function works great. When the array is N rows
with only one column is passed, the function gives me a type 13 error on the
line previously mentioned. Can anyone explain this?

Dim objFS As Object
Dim objTextFile As Object
Dim sWholeLine As String
Dim vRow As Variant
Dim vCellArray As Variant

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFS.OpenTextFile(sFileName, ForAppending, True, 0)

For Each vRow In ExportRange.Cells.Rows
' With 2-D arrays we need to get values in a horizontal 1-D
array with two transposes!? (Thanks Tom Ogilvy)
vCellArray =
Application.Transpose(Application.Transpose(Worksheets(vRow.Parent.Name).Range(vRow.Address).Value))
sWholeLine = Join(vCellArray, Seperator) '<----Error 13 Type
Mismatch
objTextFile.WriteLine sWholeLine
Next

It almost seems that vCellArray needs to contain more than one item. And
vcellArray does contain a correct value.
I'm stumped.

Regards,
Stefano Condotta

 

[Jim Cone]

Stefano,

The "Join" function requires an array as the first argument.
When ExportRange is only one column then vCellArray is
returning a string not a Variant containing an array.
The following appears to work...

vCellArray = Application.Transpose _
(Application.Transpose(Worksheets(vRow.Parent.Name).Range(vRow.Address).Value))
If IsArray(vCellArray) Then
sWholeLine = Join(vCellArray, Separator)
Else
sWholeLine = vCellArray
End If

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Stefano Condotta" <[email protected]>
wrote in message
Hello,
This code fails on "Wholeline=Join(vCellArray, sSeperator)". When a two
dimensional array of N rows and M columns with M being greater than 1 is
passed to the routine, the function works great. When the array is N rows
with only one column is passed, the function gives me a type 13 error on the
line previously mentioned. Can anyone explain this?

Dim objFS As Object
Dim objTextFile As Object
Dim sWholeLine As String
Dim vRow As Variant
Dim vCellArray As Variant

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFS.OpenTextFile(sFileName, ForAppending, True, 0)

For Each vRow In ExportRange.Cells.Rows
' With 2-D arrays we need to get values in a horizontal 1-D
' array with two transposes!? (Thanks Tom Ogilvy)
vCellArray = Application.Transpose _
(Application.Transpose(Worksheets(vRow.Parent.Name).Range(vRow.Address).Value))
sWholeLine = Join(vCellArray, Seperator) '<----Error 13 Type Mismatch
objTextFile.WriteLine sWholeLine
Next

It almost seems that vCellArray needs to contain more than one item. And
vcellArray does contain a correct value.
I'm stumped.
Regards,
Stefano Condotta

 

[Stefano Condotta]

Thanks Jim,

Your idea has led me to a similar good solution.

Stefano