Transposing a range to a .txt file

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
367
Office Version
  1. 2010
Platform
  1. Windows
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:

Code:
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
367
Office Version
  1. 2010
Platform
  1. Windows
Forgot to mention that the error it comes up with is RunTime Error 13 - Type Mismatch... if this helps
 

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
367
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Dec 14, 2016
Messages
367
Office Version
  1. 2010
Platform
  1. Windows
I'm still struggling with this one... Any thoughts anyone?
 

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
538
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
...n/a
 
Last edited:

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
538
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
this code is ok? It's look strange to me.

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

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
367
Office Version
  1. 2010
Platform
  1. Windows
Apologies for the late reply. I've been away this past week...

the code:

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...
 

Watch MrExcel Video

Forum statistics

Threads
1,109,163
Messages
5,527,163
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top