What format do I need to prevent quotations marks being exported to a Tab Delimited Text file?

[Rhothgar]

Hi

I've tried several formats to try and resolve the following issue. Any advice is greatly appreciated.

I use a text file to upload products to Google Shopping and our website system produces the following "shipping" field.

::Europe:8.00,::Royal Mail 2nd Class Signed For:4.00,::UK Outlying Areas:2.50

I've imported it into latest Excel and when I convert to Tab Delimited Text it exports this:-

"::Europe:8.00,::Royal Mail 2nd Class Signed For:4.00,::UK Outlying Areas:2.50"

This results in Google effectively deleting all our products from their shopping.

I've tried changing format of column to: General, Text and Special but I cannot get my head around the Custom option.

The field length changes and could be:-

::Courier (UK Mainland):0.00,::UPS Next Day Delivery (Zone 1 only):0.00

or even

::Royal Mail 1st Class Signed For:6.55,::Free (Hermes 3 - 5 Working Days):0.00,::Courier (UK Mainland):6.00,::Europe:8.00,::Hermes:0.00,::Royal Mail 2nd Class Signed For:4.00,::Royal Mail 1st Class Signed For (Weight Based):4.55,::UPS Next Day Delivery (Zone 1 only):0.00

It obviously doesn't like the colons but they need to be there.

Is there a way of resolving this issue automatically please?

 

[VBE313]

I have never used a .tsv but I had the same issue with my .csv file. I opened NotePad in my code and then sent a bunch of keys to remove the parentheses.

    fileToOpen = Application.ActiveWorkbook.FullName
    fileToOpen = Shell("Notepad.exe " & fileToOpen, 1)
    Application.SendKeys "^a", True 'this selects all the text
    Application.SendKeys "^h", True 'this opens the Replace box
    Application.SendKeys "{BACKSPACE}" 'this removes any text in the textbox
    Application.SendKeys """" 'This adds " to remove
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Application.SendKeys "{TAB}"
    Application.SendKeys "~" 'This presses enter
    Application.SendKeys "{TAB}"
    Application.SendKeys "~"
    Application.SendKeys "{TAB}"
    Application.SendKeys "~"

Make sure Microsoft Scripting Runtime is checked in your VBA References

 

[kennypete]

Sendkeys is a risky approach. Why not just custom write a Sub to write to a .txt file. Code like this does it:

Sub tsv()
Dim rng As Range
Dim fso As Object, oFile As Object
Dim r As Integer, c As Integer
Dim txt As String
Set rng = Sheet1.Range("A1").CurrentRegion
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = fso.CreateTextFile(Environ("TEMP") & "\file.txt")
For r = 1 To rng.Rows.Count
    txt = ""
    For c = 1 To rng.Columns.Count
        If c > 1 Then txt = txt & vbTab
        txt = txt & rng.Cells(r, c).Value2
    Next c
    txt = txt
    oFile.WriteLine txt
Next r
oFile.Close
Set fso = Nothing
Set oFile = Nothing
End Sub

Where the Sheet1 has

Book1
	A	B
1	1	::Royal Mail 1st Class Signed For:6.55,::Free (Hermes 3 - 5 Working Days):...,::UPS Next Day Delivery (Zone 1 only):0.00
2	2	::Europe:8.00,::Royal Mail 2nd Class Signed For:4.00,::UK Outlying Areas:2.50
Sheet1

Output file to TEMP\file.txt is