Excel to text file transfer of data.

[ramanan256]

I have a data in excel sheet which I would like to convert into a text file. In the text file, the data format should be with the brackets and comma separated.

For example:
In excel sheet:

12 2 45 25
23 1 35 50
24 1 21 23



In text file the same data should be in the following format:

(12,2,45)[25]
(23,1,35)[50]
(24,1,21)[23]

The above is for one data set . In other data set, the brackets may change places for example:
(1,23)[12,34,56]

I have many data sets in which I would like to put brackets at the necessary places according to the requirement. Kindly help

Thanking you,
Ramanan

[rylo]

Hi

Here's one quick way.

Sub aaa()
  Set newbk = Workbooks.Add
  ThisWorkbook.Activate
  
  For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    holder = "(" & ce.Value & "," & ce.Offset(0, 1).Value & "," & ce.Offset(0, 2).Value & "," & ce.Offset(0, 3).Value & ")[" & ce.Offset(0, 4).Value & "]"
    newbk.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = holder
  Next ce
  On Error Resume Next
  Kill "c:\temp\!aaa.txt" 'remove any existing file
  On Error GoTo 0
  newbk.SaveAs Filename:="C:\TEMP\!aaa.txt", FileFormat:=xlText
  
  newbk.Close savechanges:=False
  
End Sub

rylo

[ramanan256]

Hi Rylo,
In the text file I am getting the data within quotes which I would like to avoid. I am not able to find out how to modify your code to eliminate those double quotes in the text file. Kindly help
Ramanan

[rylo]

Hi

Had a funny feeling that may be an issue. Try this one.

Sub bbb()
  Open "c:\temp\!aaa.txt" For Output As 1
  
  For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    holder = "(" & ce.Value & "," & ce.Offset(0, 1).Value & "," & ce.Offset(0, 2).Value & "," & ce.Offset(0, 3).Value & ")[" & ce.Offset(0, 4).Value & "]"
    Print #1, holder
  Next ce
  
  Close #1

End Sub

rylo

[ramanan256]

Thanks for the help..I finally got it..
Ramanan