Export to CSV

D

D Van

I have my data in columns, for example

06932776
06944399
06967452
06996549
07020414
05405107


I want to export the data to a CSV format, is this possible.

06932776,06944399,06967452, etc.

TIA
 
R

Ron de Bruin

Hi

You can save your sheet as a CSV file

Sub Save_ActiveSheet_CSV_File()
Dim wb As Workbook
Dim strdate As String
Dim Fname As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Fname, FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
D

D Van

Ron

I guess I was confusing. I want the data to be in a CSV format, not save
the actual workbook in CSV.

I want all data in the columns to be on one line.

See below
06932776,06944399,06967452, etc.
 
R

Ron de Bruin

Sorry my mistake

You can use a function like this one
Copy the function in a normal module

=Rangecat(A1:A100,",")
Use this in the worksheet


From J.E. McGimpsey
It is working for rows an columns

Public Function RangeCat(rng As Excel.Range, _
Optional delimiter As String = "", _
Optional direction As Integer = 1) As Variant
Dim myColumn As Range
Dim cell As Range
If direction = 1 Then 'by rows
For Each cell In rng
RangeCat = RangeCat & delimiter & cell.Text
Next cell
ElseIf direction = 2 Then 'by cols
For Each myColumn In rng.Columns
For Each cell In myColumn.Cells
RangeCat = RangeCat & delimiter & cell.Text
Next cell
Next myColumn
Else
RangeCat = CVErr(xlErrNA)
Exit Function
End If
RangeCat = Mid(RangeCat, 1 + Len(delimiter))
End Function
 
D

D VanDerMark

Thanks Ron

Ron de Bruin said:
Sorry my mistake

You can use a function like this one
Copy the function in a normal module

=Rangecat(A1:A100,",")
Use this in the worksheet


From J.E. McGimpsey
It is working for rows an columns

Public Function RangeCat(rng As Excel.Range, _
Optional delimiter As String = "", _
Optional direction As Integer = 1) As Variant
Dim myColumn As Range
Dim cell As Range
If direction = 1 Then 'by rows
For Each cell In rng
RangeCat = RangeCat & delimiter & cell.Text
Next cell
ElseIf direction = 2 Then 'by cols
For Each myColumn In rng.Columns
For Each cell In myColumn.Cells
RangeCat = RangeCat & delimiter & cell.Text
Next cell
Next myColumn
Else
RangeCat = CVErr(xlErrNA)
Exit Function
End If
RangeCat = Mid(RangeCat, 1 + Len(delimiter))
End Function
Click to expand...
 
H

Harlan Grove

...
...
You can use a function like this one
Copy the function in a normal module

=Rangecat(A1:A100,",")
Use this in the worksheet
Click to expand...
...

As an alternative, OP could download and install Laurent Longre's MOREFUNC.XLL
add-in, available at http://longre.free.fr/english/, and use the MCONCAT
function it provides to do this as

=MCONCAT(A1:A100,",")

Unlike the Rangecat udf, it could also handle number/text more like CSV.

=MCONCAT(IF(ISTEXT(A1:G1),""""&A1:G1&"""",A1:G1),",")

The latter is an array formula.
 
Top