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 |
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 |
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 |
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 |
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 |
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. |
All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com