CODEWITHSUNDEEP
vbaexcel
Windmill
Windmill

Reputation: 163

Excel VBA Export to txt file without Quotation marks

I'm needing to export to a text file without " marks example

exporting this

Create bts; sitemask = "0110"; pcmlink = 40

exports like this

"Create bts; sitemask = ""0110""; pcmlink = 40"

This code I found for doing this works and strips off the " marks

Sub Export()
Dim r As Range, c As Range
Dim sTemp As String

Open "c:\MyOutput.txt" For Output As #1
For Each r In Selection.Rows
    sTemp = ""
    For Each c In r.Cells
        sTemp = sTemp & c.Text & Chr(9)
    Next c

    'Get rid of trailing tabs
    While Right(sTemp, 1) = Chr(9)
        sTemp = Left(sTemp, Len(sTemp) - 1)
    Wend
    Print #1, sTemp
Next r
Close #1
End Sub

This code works from selecting the cells running the code which exports the current selected cells, my question is this how can I modify this code to work on a predefined cell range eg A1 to A10 for exporting rather than the current selection?

Also is there away to modify the file path to save in the same directory as the active excel sheet instead of having to put the full file path"C:\MyOutput.txt" instead "MyOutput.txt" (or something like that)?

Many thanks

Upvotes: 0

Views: 12258

Answers (5)

user3079695
user3079695

Reputation: 1

Save your file as ASCII file not unicode

fso.CreateTextFile(path, True, False)

Upvotes: 0

Andrew Slentz
Andrew Slentz

Reputation: 99

From what I've seen in VBA 2010, the Write function creates output with the unwanted wrapping quotation characters.

However the similar Print function writes to the file without the quotes.

But if you wanted quotes in the text file, you could create a string concatenation at the quote locations to add them in with Chr(39), eg.

For an output of: Create bts; sitemask = "0110"; pcmlink = 40

Code as:

"Create bts; sitemask = " & Chr(39) & "0110" & Chr(39) & "; pcmlink = 40"

Upvotes: 1

Administrator
Administrator

Reputation: 11

You have an excel file, and when You save the file as .txt the Quotation marks appear. Mark the data ( CTRL + A), copy the data to clipboard ( CTRL + C). Open notepad, paste the data ( CTRL + V) save the data... There, data without Quotation marks.

Upvotes: 1

user2271770
user2271770

Reputation:

The modification would be pretty obvious: assuming that your workbook is named "ThisWorbook.xls", and the worksheet that holds the range you want to write to file is named "This Worksheet", then you'll adapt the code like

' ... Previous original code

' Open file in the same folder as the worksheet
Open Workbooks("ThisWorbook.xls").Path & "\MyOutput.txt" For Output As #1

' Loop in predefined range instead of current selection
For Each r In Worksheets("This Worksheet").Range("A1:A10").Rows

' ... Following original code

Upvotes: 2

Othya
Othya

Reputation: 420

Don't open it directly:

Sub Export()
 Dim r As Range, c As Range
 Dim sTemp As String
 Dim fn As Long
 Dim sfilename

 sfilename = "C:\MyOutput.txt"
 fn = FreeFile

 Open sfilename For Output As #fn
  For Each r In Selection.Rows
    sTemp = ""
    For Each c In r.Cells
        sTemp = sTemp & c.Text & Chr(9)
    Next c

    'Get rid of trailing tabs
    While Right(sTemp, 1) = Chr(9)
        sTemp = Left(sTemp, Len(sTemp) - 1)
    Wend
    Print #fn, sTemp
 Next r
 Close #fn
End Sub

Upvotes: 1

Related Questions