range pasted to string

[iknowu99]

I am trying to get the column besides the header to be exported to a text file. how to get the line to work:

strText= Range("C2:C" & Range("C:C").Rows)

 

[Jon von der Heyden]

Do you want the values in C2:Cx written in a single line in your text file, or as multiple lines? And how are you actually writing it to a text file?

 

[iknowu99]

Hi buddy, thanks for responding!
i'm not going european time here, sometimes i wish i was,...trying to get this done!

please see http://www.mrexcel.com/forum/showthread.php?t=145924
it's what i found similar to my deal

 

[Jon von der Heyden]

This works without the loop:

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] phoo()
 
    [COLOR=blue]Dim[/COLOR] arr()
    [COLOR=blue]Dim[/COLOR] rngText [COLOR=blue]As[/COLOR] Range
    [COLOR=blue]Dim[/COLOR] iFile [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR]
    [COLOR=blue]Dim[/COLOR] strText [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR], strFileName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
 
    [COLOR=blue]Set[/COLOR] rngText = Range("C2", Range("C" & Rows.Count).End(xlUp))
    arr = Application.Transpose(rngText)
    strText = [COLOR=blue]Join$[/COLOR](arr(), vbCrLf)
 
    iFile = [COLOR=blue]FreeFile[/COLOR]()
 
    strFileName = "C:/TestIt.txt"
    [COLOR=blue]Open[/COLOR] strFileName [COLOR=blue]For[/COLOR] [COLOR=blue]Output[/COLOR] [COLOR=blue]As[/COLOR] #iFile
        [COLOR=blue]Print[/COLOR] #iFile, strText
    [COLOR=blue]Close[/COLOR] #iFile
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[iknowu99]

like a charm

 

[iknowu99]

like a charm because i was gettin lucky:P

it is breaking down when there are more than 32000 records...it only carries over so many characters to the text file...anybody know why?

 

[Jon von der Heyden]

I didn't realise there were so many, so now lets try write it to the text file one at a time:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] phoo()
    [COLOR="Blue"]Dim[/COLOR] rngCell [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Dim[/COLOR] iFile [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] strFileName [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    
    iFile = [COLOR="Blue"]FreeFile[/COLOR]()
 
    strFileName = "C:/TestIt.txt"
    [COLOR="Blue"]Open[/COLOR] strFileName [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Output[/COLOR] [COLOR="Blue"]As[/COLOR] #iFile
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] rngCell [COLOR="Blue"]In[/COLOR] Range("C2", Range("C" & Rows.Count).End(xlUp))
            [COLOR="Blue"]Print[/COLOR] #iFile, rngCell.Value
        [COLOR="Blue"]Next[/COLOR] rngCell
    [COLOR="Blue"]Close[/COLOR] #iFile
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]