Remove double quotes in text (csv) file

[Kenneth Hobs]

Welcome to the forum! Please start your own thread when a thread is old or marked as solved. You can always paste the link or if less than 5 posts, show the thread number, 39895 for this one. When you start your own thread, you can attach a sample text file so that we can test.

If you reply, please do so in your new thread.

I suspect that your folder in filepath has a permission restriction. As for setting the worksheet object, maybe you do not have a sheet name of "Sample" in that workbook?

[Paul_Hossler]

Set wks = ThisWorkbook.Worksheets("Sample") 

Does the workbook with the macro (ThisWorkbook) have a worksheet named "Sample" ?

Paul

[Charles_life]

Thanks a lot GTO, this solution works great for me! This is saving me hours, I am an happy camper now! Thanks again!

Charles

Hi All,

I could be off base, but I think that the csv is tacking in the extra quotes around sections that contain commas. In addition, there are quote marks designating inches.

If you are wanting to save it in .txt format, as comma delimited, maybe substitute something for the comma?

[VBA]
Option Explicit

Sub exampleExport()
Dim FSO As Object ' FileSystemObject
Dim TStream As Object ' TextStream
Dim wks As Worksheet
Dim rngLastRowCell As Range
Dim rngLastCellCol As Range
Dim aryVals As Variant
Dim x As Long
Dim y As Long
Dim strText As String

Set wks = ThisWorkbook.Worksheets("Sample")
With wks
Set rngLastCellCol = FindRange(RangeArg:=.Cells, ByColOrRow:=xlByColumns)
If rngLastCellCol Is Nothing Then Exit Sub
Set rngLastRowCell = FindRange(.Cells)

aryVals = .Range(.Cells(1), .Cells(rngLastRowCell.Row, rngLastCellCol.Column))
End With

Set FSO = CreateObject("Scripting.FileSystemObject")
Set TStream = FSO.CreateTextFile(ThisWorkbook.Path & "\Test.txt", True)

For x = 1 To UBound(aryVals, 1)
strText = vbNullString
For y = 1 To UBound(aryVals, 2)
strText = strText & Replace(aryVals(x, y), ",", ";") & ","
Next
TStream.WriteLine Left(strText, Len(strText) - 1)
Debug.Print Left(strText, Len(strText) - 1)
Next
TStream.Close
End Sub

Function FindRange(RangeArg As Range, _
Optional ByVal FindWhat As Variant = "*", _
Optional FindAfter As Range, _
Optional LookWhere As XlFindLookIn = xlValues, _
Optional WholeOrPart As XlLookAt = xlPart, _
Optional ByColOrRow As XlSearchOrder = xlByRows, _
Optional NextOrPrevious As XlSearchDirection = xlPrevious, _
Optional MatchCaseBool As Boolean = False, _
Optional MatchFound As Boolean = False) As Range

If FindAfter Is Nothing Then
Set FindAfter = RangeArg(1, 1)
End If

Set FindRange = RangeArg.Find(What:=FindWhat, _
After:=FindAfter, _
LookIn:=LookWhere, _
LookAt:=WholeOrPart, _
SearchOrder:=ByColOrRow, _
SearchDirection:=NextOrPrevious, _
MatchCase:=MatchCaseBool)
MatchFound = Not FindRange Is Nothing
End Function
[/VBA]

[GTO]

Thanks a lot GTO, this solution works great for me! This is saving me hours, I am an happy camper now! Thanks again!

Charles

Greetings Charles,

You are welcome and thank you for the nice feedback. I am afraid that between being busy and lacking Excel on the same PC that I have internet access on, I have become disengaged from answering questions for several months; so it is a real treat to see that some old thread provided help.

I see that you just joined, so let me be the first to say "Welcome to VBAX!". There are a lot of great folks here and I am certain you will be happy you joined.

Have a great day and again, thank you for the feedback,

Mark