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
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