Transposing a range to a .txt file

[RobbieC]

Hi there, I have a series of sheets which I have been transposing the contents of to a text file. I have a series of subroutines which 'append' each sheet contents to make a master text file.

This has all been working fine, but I have just noticed that it has been failing to append certain sheets. This is the code which, so far, has been working fine:

Sub ExportFILE()
    Dim WS As Worksheet, txt As String, ff As Integer, i As Long
        With Sheets("DATA_FILE").UsedRange
            For i = 1 To .Rows.count
                If .Columns.count = 1 Then
                    txt = txt & vbCrLf & Chr(34) & .Cells(1).Value & Chr(34)
                Else
                    txt = txt & vbCrLf & Chr(34) & Join$(Application.Transpose _
                    (Application.Transpose(.Rows(i))), Chr(34) & "," & Chr(34)) & Chr(34)
                End If
            Next
        End With
        ff = FreeFile
        Open ThisWorkbook.Path & "\DATA_Temp.txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff]#ff[/URL] 
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff]#ff[/URL] , Mid$(txt, 3)
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff]#ff[/URL] 
        txt = ""
End Sub

The code basically takes the data in Sheets("DATA_FILE") and creates a comma seperated file 'DATA_TEMP.txt'

After looking at the data in the range for the failing modules, it seems that if some of the cells contain a large amount of text, it fails.

Is my code limiting the amount of characters in a string? If so, how can I get 'round this?

If you can point me in the right direction, I'd be most grateful. Thanks

 

[RobbieC]

Forgot to mention that the error it comes up with is RunTime Error 13 - Type Mismatch... if this helps

 

[RobbieC]

Ok, I have discovered more information... If one of my cells in the Sheet DATA_FILE contains more than 255 characters, it FAILS.

Anything less than that is fine. How can I overcome this? I thought a string could contain many more characters than 255...

 

[RobbieC]

I'm still struggling with this one... Any thoughts anyone?

 

[RobbieC]

Ok, so I found another thread from a few years ago with a similar problem:

https://www.mrexcel.com/forum/excel-questions/1062961-transpose-array-type-mismatch-error.html

I'm not sure if he was able to fix it either, but Norie's suggestion to use a loop to write the file sounds good...

 

[eduzs]

...n/a

 

[eduzs]

this code is ok? It's look strange to me.

Join$(Application.Transpose(Application.Transpose(.Rows(i))), Chr(34) & "," & Chr(34)) & Chr(34)

 

[RobbieC]

Apologies for the late reply. I've been away this past week...

the code:

[COLOR=#333333]Join$(Application.Transpose(Application.Transpose(.Rows(i))), Chr(34) & "," & Chr(34)) & Chr(34)

works perfectly fine, but only if the string has less than 255 characters.

In the link I posted above, [/COLOR]Norie's suggested to use a loop to write the file rather than transpose... I'm not 100% sure about how to go about this...