Macro for Append to csv

R

rooperi

Hi guys,
I'm new here, if I'm posting in the wrong place, feel free to yell a
me.
I have a dynamic range, which is defined by the number of non empty row
in a worksheet. (typically 10-20 rows). The number of columns are awlay
the same (6). So, If I have 10 rows, my range would be A1:F10.
Now, I need a macro to append this range to an existing csv file, an
have no idea how to do it, my abilities with VBA are limited, to say th
leat.
Any ideas
 
A

Auric__

rooperi said:
I'm new here, if I'm posting in the wrong place, feel free to yell at
me.
I have a dynamic range, which is defined by the number of non empty rows
in a worksheet. (typically 10-20 rows). The number of columns are awlays
the same (6). So, If I have 10 rows, my range would be A1:F10.
Now, I need a macro to append this range to an existing csv file, and
have no idea how to do it, my abilities with VBA are limited, to say the
leat.

Try this:

Sub AppendToExistingCSV()
Dim ro As Range, cl As Range, fn As Long, outP As String
fn = FreeFile
Open "C:\full\path\to\your.csv" For Append As fn
For Each ro In Range("A1:" & Cells.SpecialCells(xlCellTypeLastCell) _
.Address).Rows
outP = ""
For Each cl In ro.Columns
If VarType(cl.Cells.Value) = vbString Then
outP = outP & """" & CStr(cl.Cells.Value) & ""","
Else
outP = outP & CStr(cl.Cells.Value) & ","
End If
Next
If outP <> String(ro.Columns.Count, ",") Then
Print #fn, Left(outP, Len(outP) - 1)
'To continue after blank line, remove next 2 lines:
Else
Exit For
End If
Next
Close fn
End Sub

(As with everything I post, almost certainly not the best way to do it,
but it works.)

A few notes:
- Change "C:\full\path\to\your.csv" to point at the actual CSV.
- The path to the CSV (but not necessarily the CSV itself) must already
exist. (This will create the file if needed, but not the directory/ies.)
- The CSV can't already be open in Excel (or any other program that locks
files for writing) or this will fail. (You'll probably get error 70,
"Permission denied".)
- If you want this to continue on after encountering a blank line, delete
the two lines I marked.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top