Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
csv file convert to excel file
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
csv file convert to excel file
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 wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
csv file convert to excel file
did not work, maybe the orignal file have "|" to separate each
columns, how can I auto delimited "|" and save as excel format? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
csv file convert to excel file
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 wrote in message ... did not work, maybe the orignal file have "|" to separate each columns, how can I auto delimited "|" and save as excel format? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
csv file convert to excel file
Yes, all the data in column A....
How can I write the marco to auto delimited and save as excel file? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
csv file convert to 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 wrote in message ... Yes, all the data in column A.... How can I write the marco to auto delimited and save as excel file? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert email address file in Excel 2000 to Word file with commas | Excel Discussion (Misc queries) | |||
Convert Excel XLS file to Quickbooks QIF file format | Excel Discussion (Misc queries) | |||
How do I convert an Excel file to a Phonebook Data File? | Excel Discussion (Misc queries) | |||
How do I convert an excel file into a comma separated values file | Excel Discussion (Misc queries) | |||
How do I convert excel file into ASCII text file with alignment? | Excel Discussion (Misc queries) |