Transposing a range to a .txt file

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
376
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
376
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
376
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
376
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
695
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
...n/a
 
Last edited:

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
695
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
376
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,281
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top