Need help to get rid of unwanted " in text output file from an excel macro

[AdamHasslert]

Hi,
I have created a macro that:
1) imports some data from a file
2) modifies and structures the data in two different formats
3) Saves the data in two text-files

The issue that I am having is that one of the formats gets unwanted " before and after the text, the other format dosen't, and I can't figure out why.

Format 1 = "ChangeMSISDN(219021005589015,385915050644)"
Format 2 = Msisdn(385959086039)

Does anyone know what i could be?

Thanks in advance!

Adam

Format 1:

Sub prepaid_ccbs_modify(PathName As String)
Dim LastRow As Integer

'find out which is the last row
LastRow = Range("A65536").End(xlUp).Row - 2

'split the text to columns
    Columns("A:A").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
        
'copy column A and paste in column E
    Columns("A:A").Select
    Selection.Cut
    Columns("E:E").Select
    ActiveSheet.Paste

'add text in column A
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ChangeMSISDN("
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A" & LastRow)
    
'add a comma in collumn C
    Range("C1").Select
    ActiveCell.FormulaR1C1 = ","
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C" & LastRow)
    
'add text in column D
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "385"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "385"
    Range("D1:D2").Select
    Selection.AutoFill Destination:=Range("D1:D" & LastRow)
    
'add text in column F
    Range("F1").Select
    ActiveCell.FormulaR1C1 = ")"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:F" & LastRow)

'concatenate columns
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
    Range("G1").Select
    Selection.AutoFill Destination:=Range("G1:G" & LastRow)

'copy column G and replace column A
    Columns("G:G").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'delete columns
    Columns("B:G").Select
    Selection.Delete Shift:=xlToLeft

'Disable save to clipboard alert
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:=PathName & "\" & Format(Date, "mmdd") & "prepaid_ccbs.txt", FileFormat:=xlUnicodeText
    ActiveWindow.Close
    
    Application.DisplayAlerts = True

End Sub

Format 2:

Sub prepaid_sld_modify(PathName)
Dim LastRow As Integer

'find out which is the last row
LastRow = Range("A65536").End(xlUp).Row - 2

'split the text to columns
    Columns("A:A").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
        
'copy column A and paste in column E
    Columns("A:A").Select
    Selection.Cut
    Columns("B:B").Select
    ActiveSheet.Paste

'add text in column A


Range("A1").Select
    ActiveCell.FormulaR1C1 = "Msisdn(385"
    Range("A1").Select
    Selection.Copy
    Range("A2:A" & LastRow).Select
    ActiveSheet.Paste
  
'add a text in collumn C
    Range("C1").Select
    ActiveCell.FormulaR1C1 = ")"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C" & LastRow)
    

'concatenate columns
    Range("D1").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D" & LastRow)

'copy column D and replace column A
    Columns("D:D").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'delete columns
    Columns("B:D").Select
    Selection.Delete Shift:=xlToLeft

'Disable save to clipboard alert
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:=PathName & "\" & Format(Date, "mmdd") & "prepaid_sld.txt", FileFormat:=xlUnicodeText
    ActiveWindow.Close
    
    Application.DisplayAlerts = True

End Sub

[mdmackillop]

Hi Adam,
Welcome to VBAX
Can you post a workbook with sample data. Use Manage Attachments in the Go Advanced reply section
Regards
MD

[AdamHasslert]

Hi,
I added the workbook and the input files in the attached zip file.

To clarify: There are two input files that both needs to be formated in to two different formats. So it is 4 output files.

Another question if I may - I tryed to get one of the formats saved with the suffix .ccbs - but i didn't get it to work. Can't Excel 2002 work with 4 letter suffixes?

BR
Adam