Lewis
asked on
VBA Excel: How to save in text file format with double quotes
I am trying to save a worksheet into a .txt file. All the
data is in the first column of the worksheet, compiled from
other worksheets:
1) Whenever I save this worksheet all the double quotes (")
are preceded by an additional double quote (""). The result
in the file looks like ("text=""blabla"""). It should look
like text="blabla". So I got one new double quote for all
quotes and two that enclose the whole string (unwanted as
well)
2) "Save as" .prn format works but is limited to
something like 239 characters. I have lines that are
longer!
3) General question (1 and 2 are more important).
Is there some kind of esape sequence
in Excel so that I can display a double quote in a cell
without the stupid workaround I'm using right now (Paste a
double quote in an unused cell and refer to it whereever
I need double quotes)
data is in the first column of the worksheet, compiled from
other worksheets:
1) Whenever I save this worksheet all the double quotes (")
are preceded by an additional double quote (""). The result
in the file looks like ("text=""blabla"""). It should look
like text="blabla". So I got one new double quote for all
quotes and two that enclose the whole string (unwanted as
well)
2) "Save as" .prn format works but is limited to
something like 239 characters. I have lines that are
longer!
3) General question (1 and 2 are more important).
Is there some kind of esape sequence
in Excel so that I can display a double quote in a cell
without the stupid workaround I'm using right now (Paste a
double quote in an unused cell and refer to it whereever
I need double quotes)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lets try something. Execute the code below in your workbook.
Function CombineCells() As Long
Dim sCellData As String
Dim sTotal As String
Dim lCounter As Long
lCounter = 1
sTotal = ""
sCellData = Worksheets("Sheet1").Cells
While sCellData <> ""
sTotal = sTotal & sCellData
lCounter = lCounter + 1
sCellData = Worksheets("Sheet1").Cells
Wend
Worksheets("Sheet1").Cells
CombineCells = True
End Function
It is basically the same thing you had before, except it combines all the fields and then writes this combination to the cell five below the last cell. See if it does what you are talking about.
Later,
Martin
Function CombineCells() As Long
Dim sCellData As String
Dim sTotal As String
Dim lCounter As Long
lCounter = 1
sTotal = ""
sCellData = Worksheets("Sheet1").Cells
While sCellData <> ""
sTotal = sTotal & sCellData
lCounter = lCounter + 1
sCellData = Worksheets("Sheet1").Cells
Wend
Worksheets("Sheet1").Cells
CombineCells = True
End Function
It is basically the same thing you had before, except it combines all the fields and then writes this combination to the cell five below the last cell. See if it does what you are talking about.
Later,
Martin
ASKER
Hi Martin,
I found it myself. You just have to put &"""" into your formula bar.
Thanks for your prompt response,
Lewis.
I found it myself. You just have to put &"""" into your formula bar.
Thanks for your prompt response,
Lewis.
ASKER
the code for saving works great (I connected it with a button on the Excel worksheet).
Thanks. But I still need a solution for the problem with the double quotes.
I would like to concatenate cells from one worksheet and put double quotes around
it (cell1: Value, cell 2: abc123 should result in: Value="abc123"). I tried it with
=Sheet1!A1&"="&Chr$(34)&Sh
but this doesn't work.
So what I would need is something like an escape character for double quotes like the
backslash in C where with \\ you produce a single \.
Maybe I should post this question not here in the VB section but in another one for
office products. If you cannot answer I will give you your (well earned) points and try it
there.
/Lewis