PDA

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



AdamHasslert
07-15-2008, 07:41 AM
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
07-15-2008, 01:10 PM
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
07-16-2008, 01:53 AM
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