export to CSV more than 65k rows
 

Hi
I need to export one colum from Excel to CSV. If the total rows are < 65000
then its not a problem. But I need to export about 150K rows from multiple
colum into one CSV file. How can I do this.

Here is my code to export from one column:
The CSV file path (full path) is pecified on a parmsheet. Basically I create
a new workbook and save that as CSV file.

Sub ExportToCSV()
' Save selected range as a CSV text file
Dim ThisBook As Workbook
Dim path As String
Dim lastrow As Double

ExecuteExcel4Macro "echo(false)"
path = ThisWorkbook.Sheets("parmsheet").Range("B2").Value
'

Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row


'lastrow = ThisWorkbook.Sheets(1).Range("A1").End(xlUp).Selec t
ThisWorkbook.Sheets(1).Range("A2:A" & lastrow).Select
Set ThisBook = ActiveWorkbook

Selection.Copy

Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues ' one of several options used
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path, FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
ThisBook.Activate
ThisBook.Worksheets(1).Range("A1").Activate

MsgBox "The CSV file has been created in" & Chr(13) & path, vbOKOnly, "CSV
Export"

End Sub

export to CSV more than 65k rows
 

You could write the file manually but it may take some time.

open "c:\output.csv" for output as #1

for cnt = 1 to 65000
print #1, range("A" & cnt).Text
next cnt

for cnt = 1 to 65000
print #1, range("B" & cnt).Text
next cnt

close #1

Also you could create 2 files, test1.csv and test2.csv and then trun the
command

copy /b test1.csv + test2.csv output.csv

which will combine the 2 files into the 3rd larger file.

"Mohan" wrote in message
...
Hi
I need to export one colum from Excel to CSV. If the total rows are <
65000
then its not a problem. But I need to export about 150K rows from
multiple
colum into one CSV file. How can I do this.

Here is my code to export from one column:
The CSV file path (full path) is pecified on a parmsheet. Basically I
create
a new workbook and save that as CSV file.

Sub ExportToCSV()
' Save selected range as a CSV text file
Dim ThisBook As Workbook
Dim path As String
Dim lastrow As Double

ExecuteExcel4Macro "echo(false)"
path = ThisWorkbook.Sheets("parmsheet").Range("B2").Value
'

Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row


'lastrow = ThisWorkbook.Sheets(1).Range("A1").End(xlUp).Selec t
ThisWorkbook.Sheets(1).Range("A2:A" & lastrow).Select
Set ThisBook = ActiveWorkbook

Selection.Copy

Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues ' one of several options used
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path, FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
ThisBook.Activate
ThisBook.Worksheets(1).Range("A1").Activate

MsgBox "The CSV file has been created in" & Chr(13) & path, vbOKOnly, "CSV
Export"

End Sub