SaveAs Fileformat:=xlText adds " " to cell data 1

[dokR]

Hi,

I want to save a worksheet of a workbook as a text-document.

My newbie problem is that the VBA macro adds quote marks "" around the contents of some cells; whereas the manual File-Save As ... does not.

I found a work-around by delimiting the length of the string in that cell, which was a very long like in the example.

Sub MacroProducesQmarks()

Cells(2, 1) = "{This is a very long string which I want to save without any quotation marks and this works when doing save as manually, but when I record a macro and run it then it will add the quotes}"

ActiveWorkbook.SaveAs Filename:="D:\UserData\longStringOutput.txt", _
FileFormat:=xlText, CreateBackup:=False
End Sub

Thanks for your help

 

[PHV]

For me, xlText is not a valid Excel.XlFileFormat.
you may try to use xlTextWindows instead.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dokR]

Hi PHV,

Thanks for your reply;

The xlTextWindows-switch you propose produces the same quotation marks in my environment;

However, your response has let me think that text is no excel format, and now I export the spreadsheet using a FileSystemObject writing line by line into a text file.

That works.

 

[JazzyLee]

dokR,
How did you get this to work? I have the same problem. Mind sharing your codes?
Thanks much!

 

[dokR]

Hi JazzyLee,

I saved line by line into a file, and used a book to find how to do it.

In principle you create a file, then you loop throuth the cells of a line to create one string per line, and then you write this line-string into the file and so forth:

' Microsoft Scripting Runtime must be enbabled:
Public objFSO As New FileSystemObject


sub exportWorkSheet

Dim objFile As Object
Dim ws As Worksheet
Dim path, file, separator As String
Dim i As Long
dim k As Integer


path = "D:\whatever\"
file = "myfile"
separator = ";"

Set ws = Workbooks("myworkBookName").Worksheets("workSheetName")
Set objFile = objFSO.CreateTextFile(path & file)

lineStr = ""
For i = 1 To ws.Cells.SpecialCells(xlCellTypeLastCell).Row
For k = 1 To ws.Range("IV" & i).End(xlToLeft).column
lineStr = lineStr & ws.Cells(i,k) & separator
Next k
objFile.WriteLine lineStr
Next i

Set ws = Nothing
Set objFile = Nothing
End Sub

I haven't tested this reduction of my code, but roughly it should work. Attention, there is no error handling here; also remind that it is good practice to check whether the export file is already existing and eventually do something in this case.

 

[JazzyLee]

Thanks! I'll build on this. It's a BIG start and a whole lot more than I would have come up with on my own.