I run the report is csv file format, but I need to convert to excel
file, the problem is the csv file are very large, I need to manual
copy and paste to Sheet 1, Sheet 2, Sheet 3 ... ... because I'm using
excel 2003, each Sheet max only have 65xxx rows.

How can I write a marco to auto convert to excel file with header in
different sheet?

Try the macro below. It is a slight madification of the standard, to copy the header row to all the
sheets.

HTH,
Bernie
MS Excel MVP


Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim mySht As Worksheet
'Ask User for File's Name
FileName = Application.GetOpenFilename
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
'For xl95 change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
' and copy the header row from the last sheet
Set mySht = ActiveSheet
ActiveWorkbook.Sheets.Add
mySht.Cells(1, 1).EntireRow.Copy ActiveSheet.Cells(1, 1).EntireRow
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub


<(E-Mail Removed)> wrote in message
news:3d6cf778-526e-4e9b-bdd3-(E-Mail Removed)...
>I run the report is csv file format, but I need to convert to excel
> file, the problem is the csv file are very large, I need to manual
> copy and paste to Sheet 1, Sheet 2, Sheet 3 ... ... because I'm using
> excel 2003, each Sheet max only have 65xxx rows.
>
> How can I write a marco to auto convert to excel file with header in
> different sheet?

did not work, maybe the orignal file have "|" to separate each
columns, how can I auto delimited "|" and save as excel format?

What do you mean "Did not work" did your file not get opened, or was all the data in column A?

Try selecting column A and using Text to columns using | as your delimiter.

HTH,
Bernie
MS Excel MVP


<(E-Mail Removed)> wrote in message
news:3fa9b193-6eaa-491d-ad44-(E-Mail Removed)...
> did not work, maybe the orignal file have "|" to separate each
> columns, how can I auto delimited "|" and save as excel format?

Yes, all the data in column A....
How can I write the marco to auto delimited and save as excel file?

Add this line:

Range("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
Other:=True, OtherChar:="|"


just before this line

Set mySht = ActiveSheet

and again just after the line

Loop



HTH,
Bernie
MS Excel MVP


<(E-Mail Removed)> wrote in message
news:0a6ab4b3-1d67-4c2a-918e-(E-Mail Removed)...
> Yes, all the data in column A....
> How can I write the marco to auto delimited and save as excel file?