Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV
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 -- Regards Ron de Bruin http://www.rondebruin.nl "D Van" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV
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. "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "D Van" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV
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 -- Regards Ron de Bruin http://www.rondebruin.nl "D Van" wrote in message ... 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. "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "D Van" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV
Thanks Ron
"Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "D Van" wrote in message ... 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. "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "D Van" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export to CSV
"Ron de Bruin" wrote...
... You can use a function like this one Copy the function in a normal module =Rangecat(A1:A100,",") Use this in the worksheet ... 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. -- To top-post is human, to bottom-post and snip is sublime. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Excel tuncating leading zeros while export to excel from da | Setting up and Configuration of Excel | |||
Chart.Export images are shrinking as I export more images | Charts and Charting in Excel | |||
export re-order input fields to export file [csv] | Excel Worksheet Functions | |||
export as csv | Setting up and Configuration of Excel | |||
CSV export help | Excel Discussion (Misc queries) |