Nope - never got a resolution to this one.
Posts by Sferical
-
-
Imbuzi, thanks for that. It looks like that will sort the file opening issue. Where abouts would I place this in the existing code?
Can anyone else help to combine the first two bits of code so that I can import large delimited text files.
Thanks,
-
I am importing large debug log text files in to excel. I trawled the threads and couldn't find a suitable solution to this one. The files are often more than 65536 rows and I have got the code for importing more than 65536 rows from the MS Website. However, this only does a straight import and puts all the data in the first cell in each row. I have my own code for importing the text as delimited values in to seperate cells but this doesn't deal with importing files larger than 65536 rows. Therefore I need a way of marrying the two together. I've done some VBA in Access before but am pretty rusty. Finally if somewhere can finish it off by getting the code to prompt for a file to import ( which includes a browse feature to select the file ) I would be most grateful. Many thanks for any help.
Code is as follows:
From MS:
Code
Display MoreSub LargeFileImport() '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 = InputBox("Please enter the Text File's name, e.g. test.txt") '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 xl97 and later change 16384 to 65536 If ActiveCell.Row = 16384 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 [B]From Me:[/B] Sub Import_Data With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\BES_LOG.txt", _ Destination:=Range("A1")) .Name = "BES_LOG" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileOtherDelimiter = ")" .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub