Saving .csv files from excel without final Carriage Return.

[Purdydog]

Apologies if this has been answered before, I can find similar topics but not an exact solution

I need to create a .csv file from data in an excel worksheet for use in another application.

When the csv file is created there is always a final carriage return / blank line which requires manually deleting before the file can be used.

Is there a way to stop this happening?

I'm using excel 2016 bundled with office 365


Thanks.

 

[rpaulson]

here is what i use:

Sub outputtotextfile()
wline = ""

For r = 1 To Range("A65536").End(xlUp).Row
    lcol = Cells(r, 256).End(xlToLeft).Column
    wline = wline & Join(Application.Transpose(Application.Transpose(Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
    
 
   Open "C:\results\test2.txt" For Output As #1  'Replaces existing file
   Print #1 , wline
   Close #1 

End Sub

hth,
Ross

 

[Purdydog]

Hi Ross,

Thanks for the reply, (I think I found your code on via an earlier google search)

Showing my VBA naivety, I don't fully understand it but have given it a go.

Here the code :-I'm getting a type mismatch error on the line highlighted in bold.
Sub outputtotextfile()
wline = ""
For r = 1 To Range("A65536").End(xlUp).Row
lcol = Cells(r, 256).End(xlToLeft).Column
wline = wline & Join(Application.Transpose(Application.Transpose(Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r


Open "E:\testa.txt" For Output As #1 'Replaces existing file
Print #1 , wline
Close #1
End Sub


I'm guessing I'm missing something??



Thanks,

Barry.