Saving as Text without delimeters

[jhall07]

I am trying to save an worksheet as txt format. Everytime I try to perform this, I am getting quotation marks around each line that includes a comma. So I tried saving it as .prn to avoid the quotation marks. Now, it cuts off any line that has more than 250 characters. Is there any text format that I save my file as that will not add quotations or limit line lengths?

 

[Rick Rothstein]

I am trying to save an worksheet as txt format. Everytime I try to perform this, I am getting quotation marks around each line that includes a comma. So I tried saving it as .prn to avoid the quotation marks. Now, it cuts off any line that has more than 250 characters. Is there any text format that I save my file as that will not add quotations or limit line lengths?

Show us your code so we know which approach you are using to do your saves.

 

[NeonRedSharpie]

Show us your code so we know which approach you are using to do your saves.

I don't think he's using code. jhall, have you tried going into the windows settings:

Export or Save Excel Files with Pipe (or Other) Delimiters Instead of Commas

 

[jhall07]

ChDir strFolder
ActiveWorkbook.SaveAs Filename:= _
strFolder & fname & ".properties", FileFormat:=xlTextPrinter _
, CreateBackup:=False
ActiveWindow.Close (saveChanges = True)

 

[jhall07]

I do not want to save with any delimiters. The issue is that excel see's a comma in the text and wants to put quotations around that line when saving in a text format. The only text format that excel will ignore this is .prn. The issue with .prn is that it limits each line to 240 characters. I am just looking to see if anyone knows of a way around this. I could use a batch file to open the generated file to find/replace the quotation marks, but there are some of them that are within the text and need to be there.

 

[Rick Rothstein]

I do not want to save with any delimiters.

Don't you need some kind of delimiter between your data? Without a delimiter, all your cell values on the same row will be concatenated together into one long text string.

 

[jhall07]

Each row in the excel file is saved on its own row in the text file, and that is the intention. Also, there is only 1 column that I am exporting.

 

[Rick Rothstein]

Each row in the excel file is saved on its own row in the text file, and that is the intention. Also, there is only 1 column that I am exporting.

Ah, only one column... good... that makes things nice and easy. Let's say Column A is what you want to save, then Dim a variable named FF as Long, then use this code to write your column of data (assumed to be Column A in the code below) out to you indicated path and filename...

  FF = FreeFile()
  Open strFolder & fname & ".properties" & ".txt" For Output As #FF
  Print #FF, Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value), vbNewLine)
  Close #FF

 

[jhall07]

I entered in the code that you provided. I am getting a run time error '13': Type Mismatch for this line:


Print #FF, Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value), vbNewLine)

 

[Rick Rothstein]

I entered in the code that you provided. I am getting a run time error '13': Type Mismatch for this line:


Print #FF, Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value), vbNewLine)

Do you have more than one cell with data in it in Column A? And are you running the macro with the sheet containing your data selected.