PDA

View Full Version : Create CSV files with Multiple sheets columns



AhmedBayagon
05-18-2015, 09:38 PM
Hello Guys,

I am trying to create a Button on Sheet1 named Create CSV's where it will take selective columns from other sheets i.e. Sheet 2, 3 etc and create csv file.

How should I write a macro that will do something like this.

Any Inputs appreciated.

Regards,
AB

Aussiebear
05-18-2015, 10:36 PM
Construct a basic recorder macro, carrying out all the steps you need then post the code and we can go from there

jonh
05-19-2015, 02:26 AM
MkCSV arguments:
Filename you want to write to
Seperator
Ranges to output.


Sub Example()
MkCSV _
"C:\test.csv", _
", ", _
Sheets("sheet1").Range("a1:b3"), _
Sheets("sheet2").Range("b1:c3")
End Sub




Sub MkCSV(sFile As String, sep As String, ParamArray r() As Variant)
Dim itm, j, k, s, tf, ars As Integer, rws As Integer
ars = UBound(r): rws = r(0).Rows.Count
For Each itm In r
If itm.Rows.Count <> rws Then
MsgBox "Row count mismatch": Exit Sub
End If
Next
Set tf = CreateObject("Scripting.FileSystemObject").CreateTextFile(sFile, True)
For j = 0 To rws
For Each itm In r
For k = 1 To itm.Columns.Count
If Len(s) Then s = s & sep
s = s & itm.Cells(j + 1, k)
Next
Next
tf.writeline s
s = ""
Next
tf.Close
MsgBox "done"
End Sub

snb
05-19-2015, 03:05 AM
Sub M_snb()
MsgBox Join(Application.Transpose(Sheet1.Columns(1).SpecialCells(2)), vbCrLf) & Join(Application.Transpose(Sheet2.Columns(7).SpecialCells(2)), vbCrLf)
End Sub