Macro - worksheets to text files without double quotes

Carey22

New Member
Joined
Dec 19, 2016
Messages
10
Hi

I’m using the macro below to save a large number of worksheets to text files. The macro works but because the worksheet cells contain commas and quotes it adds opening and closing quotes and extra quotes around the original quotes to the text file (see sample below). Having done a search on Mr Excel I know this is a common issue but because I am new to macros I cannot copy and paste the suggested solutions to my macro as it doesn't work. Please can someone offer some help as I don't want to manually remove the extra quotes.

Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xtxtFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xtxtFile = xWs.Name & ".html"
Application.ActiveWorkbook.SaveAs Filename:=xtxtFile, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub

Each worksheet has only one cell populated with data, a sample is

content="text/html; charset=windows-1252" http-equiv="content-type"> name="viewport" content="width=device-width, initial-scale=1.0"

When the macro runs, it creates one text file for each worksheet as uses the worksheet name as the file name as required but the contents of the text file appear as below

“content=”"text/html; charset=windows-1252”" http-equiv=”"content-type"”> name=”"viewport" content=”"width=device-width, initial-scale=1.0"”

The excel content is HTML for a website so it cannot contain double quotes.

Any help would be really appreciated.

Thanks
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You could try writing the file directly, instead of using Excel's built-in SaveAs method.

Rich (BB code):
Public Sub SaveFile()
Dim xWs As Worksheet
Dim fso As Object, FileOut As Object

    On Error GoTo Oops:
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    For Each xWs In ActiveWorkbook.Worksheets
        Set FileOut = fso.CreateTextFile(xWs.Name & ".html", True, True)
        FileOut.write xWs.Cells(1, "A").Value
        FileOut.Close
        Set FileOut = Nothing
    Next xWs
    
    Set fso = Nothing
    Exit Sub

Oops:
    MsgBox "Something went wrong!"
    
End Sub

Change the reference in red to the cell where your data is. If you have multiple lines per file, you'll need to add a LF character Chr(10) to the end of each line. If you add a path before the file name "C:\Myfolder" & xws.name & ".html", you can tell it what folder to save it in.

Hope this helps.


Edit: There should be a backslash after Myfolder, but the forum software here keeps deleting it.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top