PDA

View Full Version : export to csv



Andrew01
04-23-2012, 03:16 AM
Hi,

Wondering if someone could help me as I'm quite new to VBA. I have an .xlsm file and a .csv file and I am trying to export a 5 values ( eg Range("A1:E1)) from the xlsm workbook to the csv, in the following format:

Value1,Value2,Value3,Value4,Value5

I know this should be quite simple but I'm a newbie, any help would be greatly appreciated

Tinbendr
04-23-2012, 06:31 AM
Sub ExportCells()
Dim aCell As Range
Open "MyFile.csv" For Output As #1
For Each aCell In ActiveSheet.Range("A1:E1")
Write #1, aCell
Next
Close #1
End Sub
Sub ImportCells()
Dim MyValue As Variant
Dim aCell As Range
Open "MyFile.csv" For Input As #1
For Each aCell In ActiveSheet.Range("A1:E1")
Input #1, MyValue
aCell = MyValue
Next
Close #1
End Sub

Andrew01
04-23-2012, 11:25 PM
Hey David thanks that works great.
One thing though, it exports the values one by one down the first column eg
Value1
Value2
Value3
Value4
Value5
Any simple way to achieve the following format?
Value1,Value2,Value3,Value4,Value5

Thanks again for your help!

snb
04-24-2012, 12:58 AM
sub export_snb()
createobject("scripting.filesystemobject").createtextfile("G:\OF\example.csv").write join(application.index(range("A1:E1"),1,0),",")
end sub

sub import_snb()
workbooks.open "G:\OF\example.csv"
end sub

Andrew01
04-24-2012, 01:45 AM
I appreciate your help snb, however I think your method is a bit over my head as I am new to VBA. If anyone could suggest a way to modify the code tinbendr sent through so as to achieve the comma seperated format required I would be extremely grateful as I am pulling my hair out!

Thanks heaps.

Tinbendr
04-24-2012, 01:59 AM
Sub ExportCells()
Dim aCell As Range
Dim Temp$
Open "MyFile.csv" For Output As #1
For Each aCell In ActiveSheet.Range("A1:E1")
Temp$ = Temp$ & aCell.Text & ","
Next
Temp$ = Left(Temp$, Len(Temp$) - 1)
Print #1, Temp
Close #1
End Sub

Sub ImportCells()
Dim MyValue As Variant
Dim aCell As Range
Open "MyFile.csv" For Input As #1

Line Input #1, MyValue

ActiveSheet.Range("A1:E1").Value = Split(MyValue, ",")

Close #1
End Sub

Andrew01
04-24-2012, 02:38 AM
This is much better however it outputs the values in adjacent cells:
Value1 Value2 Value3 etc
Rather than having all the values in the one cell seperated by a comma. Thanks for the help, I'm almost there. Any ideas?

BrianMH
04-24-2012, 02:40 AM
Are you sure? If you edit the CSV file in notepad it should show it as separated by comas. Excel handles that delimiter automatically so will show as normal.

Tinbendr
04-24-2012, 02:45 AM
You lost me.

Andrew01
04-24-2012, 03:13 AM
Hey your right thats great! Thanks heaps guys.
Just a quick one. I now have over a hundred values that I want to export to the csv. When I open the csv in a text editor (notepad), would it be possible to show for example 4 values seperated by commas on each line? Just for greater clarity. eg.
Value1,Value2,Value3,Value4
Value5,Value6,Value7,Value8
etc.
Cheers

BrianMH
04-24-2012, 05:10 AM
I'm assuming you're doing a range of rows. So you want it to output each row as a seperate line in your csv file. If so this will do that.


Sub ExportCells()
Dim aCell As Range
Dim aRow As Long
Dim aRange As Range
Dim Temp$
Set aRange = ActiveSheet.Range("b1:E50")


Open "C:\MyFile.csv" For Output As #1
For aRow = 1 To aRange.Rows.Count
For Each aCell In aRange.Rows(aRow).Cells
Temp$ = Temp$ & aCell.Text & ","
Next aCell
Temp$ = Left(Temp$, Len(Temp$) - 1)
Temp$ = Temp$ & Chr(13) & Chr(10)
Next aRow
Print #1, Temp
Close #1
End Sub

Andrew01
04-24-2012, 05:32 AM
Hey BrianMH thanks so much that does exactly what I needed! Thankyou all for all your help, deeply appreciated. Finally can get some sleep now