?
Solved

VBA Excel: How to save in text file format with double quotes

Posted on 1998-10-08
4
Medium Priority
?
1,347 Views
Last Modified: 2009-12-16
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)
0
Comment
Question by:Lewis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
mkmccreary earned 400 total points
ID: 1438924
Here is a little VB function to write your data.  You may have to modify it for your particular situation:

Function WriteTextFile() As Long

Dim sCellData As String
Dim lCounter As Long
Dim iFileHandle As Integer

    iFileHandle = FreeFile
    Open "c:\temp\Cells.txt" For Output As iFileHandle
    lCounter = 1
    sCellData = Worksheets("Sheet1").Cells(lCounter, 1).Value
    While sCellData <> ""
        Print #iFileHandle, sCellData
        lCounter = lCounter + 1
        sCellData = Worksheets("Sheet1").Cells(lCounter, 1).Value
    Wend
   
    Close iFileHandle
   
    WriteTextFile = True
   
End Function


As For question 3, you can use Chr$(34) which is double quotes.  
Ex:
    Worksheets("Sheet1").Cells(4, 1).Value = "Value=" & Chr$(34) & "blabla" & Chr$(34)

0
 

Author Comment

by:Lewis
ID: 1438925
Hi,
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)&Sheet1!B1&Chr$(34)
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
0
 
LVL 2

Expert Comment

by:mkmccreary
ID: 1438926
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(lCounter, 1).Value
    While sCellData <> ""
        sTotal = sTotal & sCellData
        lCounter = lCounter + 1
        sCellData = Worksheets("Sheet1").Cells(lCounter, 1).Value
    Wend
   
    Worksheets("Sheet1").Cells(lCounter + 5, 1).Value = "Value = " & Chr$(34) & sTotal & Chr$(34)
    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
0
 

Author Comment

by:Lewis
ID: 1438927
Hi Martin,
I found it myself. You just have to put &"""" into your formula bar.

Thanks for your prompt response,
Lewis.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question