Solved: (VBA) Export to .txt only specific columns without empty cells

[Henkel75]

Hey guys
I have a problem.

Im working with Excel 2010 and VBA 7.0
In Sheet1 I do a lot of calculations, which then I would like to Export to a file .TXT
I would like to save only specific columns from Sheet1 (the results of the calculations).
I need a macro for this.
For example, column G contains the time value (e.g.2013-02-01 15:00), and the column M contains the result (99.87)

I would like to save these columns to a TXT file and separate them with a semicolon and spaces
For example,
2013-02-01 15:00 ;; 99.87
2013-02-01 15:00 ;; 99.87
2013-02-01 16:00 ;; 99.82
2013-02-01 17:00 ;; 99.92
2013-02-01 18:00 ;; 99.91
2013-02-01 19:00 ;; 99.82
2013-02-01 20:00 ;; 99.81
.......................................
2013-04-01 21:00 ;; 65.71

In some cases, I want to save in .TXT more columns such as G (time), M, R, W (result)
e.q 2013-02-01 15:00 ; 15 ; 99.87 ; 32.42
One date and three different data separated by semicolons and spaces.

Macro should write to TXT file only those records in columns which I can see some values. The remaining lines where I used the condition, e.q.
= IF (H139> 0; (R139 * S139); "") In this case, the result is an empty cell, but as I tried to save the TXT files is also saves the empty cells (in the form of new lines)- spoiling the data for the chart.

Do you have any idea how to solve it?
If this problem is big, maybe some easier way?
From Sheet1 to Sheet2 send only the selected column e.q. M and R, H, Z … (time and the results of operations, without formulas and empty cells).


I'll be very grateful for your help

[patel]

attach please a sample file with desired result

[p45cal]

- spoiling the data for the chart.

Just to be clear, you're creating a chart in Excel? You're having problems with with blank cells spoiling the chart? I feel that we should be able to deal with that, hopefully without having to copy the data elsewhere.
If it's for a chart outside Excel, say so and I'll write a general bit of code to export the data as you wish (or to put the data on another sheet).

[Henkel75]

Hi, Thank you for your response.

I attach a sample excel file, and examples of the results that I would like to receive.

I use "Amcharts.com" to generate charts, so I need exactly the files. Txt like "N W.txt columns.txt" or "columns NUV x.txt"

[p45cal]

Try[VBA]Sub blah()
Set x = Application.InputBox("Which columns to export (use control key while selecting if columns aren't contiguous", "Export", , , , , , 8)
Set DataToExport = Intersect(Range("1:999"), x.EntireColumn)
'delimiter = " ; "
delimiter = vbTab & ";" & vbTab
exportstr = ""
For Each rw In DataToExport.Columns(1).Cells
If Len(rw.Text) > 0 Then
i = 0
For Each cll In Intersect(Rows(rw.Row), DataToExport).Cells
If i = 0 Then exportstr = exportstr & cll.Text Else exportstr = exportstr & delimiter & cll.Text
i = i + 1
Next cll
exportstr = exportstr & vbCrLf
End If
Next rw
'name the file:
For Each are In DataToExport.Areas
For Each colm In are.Columns
myName = myName & Split(colm.Address, "$")(1)
Next colm
Next are
ff = FreeFile
Open "C:\Documents and Settings\P45cal\My Documents\vbaexpress46080\TESTFILE " & myName & ".txt" For Output As #ff
Print #ff, exportstr
Close #ff
End Sub
[/VBA] Tweak it first so that destination folder exists.
It asks you to select which columns and will output them in the same order as they were selected.
You said you wanted to separate values "a semicolon and spaces" but one file has semicolons and tabs and so I have put a commented out line so you can choose which.
There's one or two extra blank lines in each file at the end, we can lose these if you need to.

[Henkel75]

Great job, everything works!

If only you could remove those two last blank line, I will be very grateful

[p45cal]

try[VBA]Sub blah()
Set x = Application.InputBox("Which columns to export (use control key while selecting if columns aren't contiguous", "Export", , , , , , 8)
Set DataToExport = Intersect(Range("1:999"), x.EntireColumn)
'delimiter = " ; "
delimiter = vbTab & ";" & vbTab
exportstr = ""
For Each rw In DataToExport.Columns(1).Cells
If Len(rw.Text) > 0 Then
i = 0
For Each cll In Intersect(Rows(rw.Row), DataToExport).Cells
If i = 0 Then exportstr = exportstr & cll.Text Else exportstr = exportstr & delimiter & cll.Text
i = i + 1
Next cll
exportstr = exportstr & vbCrLf
End If
Next rw
exportstr = Left(exportstr, Len(exportstr) - 2) 'remove last vbcrlf
'name the file:
For Each are In DataToExport.Areas
For Each colm In are.Columns
myName = myName & Split(colm.Address, "$")(1)
Next colm
Next are
ff = FreeFile
Open "C:\Documents and Settings\P45cal\My Documents\vbaexpress46080\TESTFILE " & myName & ".txt" For Output As #ff
Print #ff, exportstr;
Close #ff
End Sub
[/VBA]Note the semicolon at the end of this line:
Print #ff, exportstr;
which is essential to remove the second blank line at the end of the file.

[Henkel75]

Thanks You very much for help ! Everything is Perfect !