The following macro provided by Jason of this forum, saves the content of a cell in column B as a unicode text
file with file name from the adjacent cell in column A.
I am using this macro to format dictionary entries for use
with Mobipocket Creator (ebook software) which requires that each entry in the dictionary is saved as a separate text file.
This macro enables me to convert dictionaries that are in an excel spreadsheet to a form that can be used by Mobipocket
However, I am having a problem with quotation marks. Whenever there is a quotation mark in a cell in
column B, Excel puts additional quotation marks in the output file.
For example, when the macro saves the
He said, "Hi!"
I open the resulting text file and it shows:
Excel has enclosed the quotation marked section in quotation marks and
enclosed the whole sentence in quotation marks as well. How can I get it to save the text properly?
Dim Wb As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Cell As Range, Rng As Range
Dim lastRow As Long
Dim fPath As String, sWb As String, sPath As String, sWs As String
Set Wb = ThisWorkbook
sWb = Wb.Name
sPath = Wb.Path
Set Ws1 = Sheets("Sheet1") 'change to name of sheet with the data
Set Ws2 = Sheets("Sheet2") 'change to name of a blank sheet
fPath = "C:Temp" 'change to the correct path for text files
sWs = Ws2.Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastRow = .Range("A" & Cells.Rows.Count).End(xlUp).Row
Set Rng = .Range("A1:A" & lastRow)
For Each Cell In Rng
Ws2.Range("A1") = Cell.Offset(0, 1)
ThisWorkbook.SaveAs fPath & Cell & ".txt", xlUnicodeText
Ws2.Name = sWs
ThisWorkbook.SaveAs sPath & Application.PathSeparator & sWb, xlWorkbookNormal
Application.ScreenUpdating = True
Application.DisplayAlerts = True