Putting together CSV Cells in Excel with a macro
- by Eric Kinch
So, I have a macro to export data into CSV format and it's working great (Code at the bottom). The problem is the data I am putting into it. When I put the data in question in it comes out
Firstname,Lastname,username,password,description
I'd like to change it so I get
Firstname Lastname,Firstname,Lastname,username,password,description
What I'd like to do is manipulate my existing macro so to accomplish this. I'm not so good at VBS so any input or a shove in the right direction would be fantastic.
Thanks!
Sub Make_CSV()
Dim sFile As String
Dim sPath As String
Dim sLine As String
Dim r As Integer
Dim c As Integer
r = 1 'Starting row of data
sPath = "C:\CSVout\"
sFile = "MyText_" & Format(Now, "YYYYMMDD_HHMMSS") & ".CSV"
Close #1
Open sPath & sFile For Output As #1
Do Until IsEmpty(Range("A" & r))
'You can also Do Until r = 17 (get the first 16 cells)
sLine = ""
c = 1
Do Until IsEmpty(Cells(1, c))
'Number of Columns - You could use a FOR / NEXT loop instead
sLine = sLine & """" & Replace(Cells(r, c), ";", ":") & """" & ","
c = c + 1
Loop
Print #1, Left(sLine, Len(sLine) - 1) 'Remove the trailing comma
r = r + 1
Loop
Close #1
End Sub
