Macro to save as .txt is inserting double quotes around some data???

[jmthompson]

Good afternoon,
I have written the following macro to do a number of tasks, but ultimately it is supposed to generate a simple tab-delimited txt file, which it does, but it also inserts quotes around some, but not all data in column "D". No idea why.

Any thoughts?

Sub Populate_Breakout()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    Dim lastRow As Long
    Dim lastRowTemp As Long
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
ThisWorkbook.SaveAs ("G:\PCard Directory\Closes\" & MyYear & " Closes\" & MyMonth & " " & MyYear & "\" & MyMonth & " " & MyYear & " Statement Recon.xls")
    
Sheets("Template").Select
    lastRow = (Cells(Rows.Count, 7).End(xlUp).Row)
    Dim c As Range
    Range("M2:M" & lastRow).Select
    For Each c In Selection.SpecialCells(xlCellTypeVisible)
    If c <> "" Then
    c.Offset(, -12).FormulaR1C1 = "=VLOOKUP(LEFT(RC[12],5),'Account list'!C:C[1],2,FALSE)"
    End If
    Next c
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:A" & lastRow).Value = Range("A2:A" & lastRow).Value
ThisWorkbook.Save
lastRowTemp = Cells(Rows.Count, 7).End(xlUp).Row
   
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyFile = ThisWorkbook.Name
MyFormula = "=VLOOKUP(RC[-2],'[" & MyFile & "]Template'!R2C1:R" & lastRowTemp & "C7,7,FALSE)"
    Workbooks.Open Filename:= _
        "G:\PCard Directory\Closes\JPMC PCARD Pmnt Proc Template.xls"
MyBreak = ActiveWorkbook.Name
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C2:C" & lastRow - 1).FormulaR1C1 = MyFormula
    Range("C2:C" & lastRow - 1).Value = Range("C2:C" & lastRow - 1).Value
    Range("C2:C" & lastRow - 1).Select
        Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Workbooks(MyBreak).SaveAs ("G:\PCard Directory\Closes\" & MyYear & " Closes\" & MyMonth & " " & MyYear & "\" & MyMonth & " " & MyYear & " JPMC PCARD Pmnt Proc Template.xls")
MyBreak = (MyMonth & " " & MyYear & " JPMC PCard Pmnt Proc Template.xls")
Workbooks(MyBreak).Close
Workbooks(MyFile).Save
Workbooks(MyFile).SaveAs ("G:\PCard Directory\Closes\TRecs files\XXGLIxxx" & MyMonth & " " & MyYear & ".xls")
Sheets("Template").Select
    
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells.Select
    Selection.RemoveSubtotal
    
      Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 2 Step -1
    If (Cells(i, "B").Value) = "" Then
    Cells(i, "B").EntireRow.Delete
    End If
    Next i
    
    
    Columns("B:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:N").Select
    Selection.Delete Shift:=xlToLeft
    
        
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & lastRow + 1).Value = lastRow
Range("D" & lastRow + 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Range("D" & lastRow + 1).Value = Range("D" & lastRow + 1).Value
Range("D2:D" & lastRow + 1).Select
    Selection.Replace What:=""""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ChDir "G:\PCard Directory\Closes\TRecs files"
    ActiveWorkbook.SaveAs Filename:= _
        "G:\PCard Directory\Closes\TRecs files\XXGLIxxx" & MyMonth & " " & MyYear & ".txt", FileFormat:= _
        xlText, CreateBackup:=False
 Kill ("G:\PCard Directory\Closes\TRecs files\XXGLIxxx" & MyMonth & " " & MyYear & ".xls")
Kill ("G:\PCard Directory\Closes\statement.txt.")
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[SamTYler]

idunnonotink, but try replacing Selection.Replace What:=""""" with Selection.Replace What:="""

 

[jmthompson]

Thanks, that was my first attempt at a solution too, but vba doesn't like the unresolved double quote, no matter howl many other sets of quotes surround it.

I did find the solution. The double quotes are automatic for any data containing a common delimiter, like a comma, which some of my data contains. Since I don't need the commas, I stripped them from my data prior to converting to a text document. I think there is vba code that would have allowed me to keep the commas and save as text without the quotes, but I opted for the kiss approach.

 

[SamTYler]

Glad you solved it.

 

[afatac]

I did find the solution. The double quotes are automatic for any data containing a common delimiter, like a comma, which some of my data contains. Since I don't need the commas, I stripped them from my data prior to converting to a text document. I think there is vba code that would have allowed me to keep the commas and save as text without the quotes, but I opted for the kiss approach.

I have the same problem and I need to keep the comma. The data are going to be saved as unicode tab delimited text and imported to another application.

Can someone provide a solution?