Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a delimiter when importing a txt file
I've got a very large txt file and I'm using amacro from microsoft to
automatically create new sheets when I reach the 65536 row. The problem is it throws everything into one column, and I'd like to use " as the delimiter. I can get around this by going into DataText to Columns and choosing the delimiter, I just can't find a way to add it to the following macro: Sub 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 Excel versions before Excel 97, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting tab-delimiter file - 16 rows | Excel Discussion (Misc queries) | |||
Flat File with @ as delimiter | Excel Discussion (Misc queries) | |||
Need Help Importing Text File Using Two or More Spaces as the Delimiter | Excel Discussion (Misc queries) | |||
Tab Delimiter File | Excel Discussion (Misc queries) | |||
How do I specify a specific char delimiter for my file, when I sa. | Excel Discussion (Misc queries) |