Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help using macro
Hi ... I need to use excel to open a certain text file that is over 65k lines.... How can i use macros to directly generate to a 2nd and 3rd spread sheet and so on ...using macros? can anybody give a little bit of help ? Thanks guys Cheers Adrian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help using macro
Excel is just over 65,000 lines, rows. Should work if it is less tham that.
-- David "Adrian Q via OfficeKB.com" wrote: Hi ... I need to use excel to open a certain text file that is over 65k lines.... How can i use macros to directly generate to a 2nd and 3rd spread sheet and so on ...using macros? can anybody give a little bit of help ? Thanks guys Cheers Adrian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help using macro
Hi Adrian,
Perhaps the following routine, posted by Berie Deitrick, would help: '============================ Sub LargeFileImport() ' Bernie Deitrick 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double '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 ActiveWorkbook.Sheets.Add 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 '<<============================ --- Regards, Norman -- --- Regards, Norman "Adrian Q via OfficeKB.com" wrote in message ... Hi ... I need to use excel to open a certain text file that is over 65k lines.... How can i use macros to directly generate to a 2nd and 3rd spread sheet and so on ...using macros? can anybody give a little bit of help ? Thanks guys Cheers Adrian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |