How to remove inverted commas in PIPE Delimitted file created from VBA

[sesgiri]

Hi All,

i have a Macro, its creating unwanted inverted commas at the start and end of the File.can someone please have a look?


File:

"SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w |w|w|w|ww|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||

1234567890"
Expected File:|SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w| w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||

1234567890|

VBA:

Option Explicit

Sub txtt_export()
Dim buf As String, col As Long, r As Long, ws As Worksheet

Set ws = ActiveSheet

Open "D:\Temp\PEGAFileRequest.txt" For Output As #1
For r = 2 To ws.UsedRange.Rows.Count
For col = 1 To ws.UsedRange.Columns.Count
If col = ws.UsedRange.Columns.Count Then
buf = buf & "|" & ws.Cells(r, col).Text
Else
buf = buf & "|" & ws.Cells(r, col).Text & ","
End If
Next col
Write #1, Mid(Replace(buf, ",", ""), 2)
buf = ""
Next r
Close #1
End Sub

Thanks in advance,
Sesgiri.

 

[NARAYANK991]

Hi ,

The output is being created by the Write # statement ; this is the statement that is introducing the double quote characters , since the output is text , and text data is demarcated using double quote characters.

If you replace the Write # by Print # , as in :

Print #1, Mid(Replace(buf, ",", ""), 2)

you will not get the double quote characters in the output file.

Narayan

 

[sesgiri]

Thanks Narayan.Appreciate your help.

Regards,
sesgiri.