import txt to excel


New Member
Aug 10, 2010
Hi there. I'm trying to import a text file to a sheet in excel, where I can click a button, open the explorer, select the file and import to the active sheet, all this in VB.

I've got also a file in txt with more than 300 columns to import, but in this case, I want to split in 2 sheets when I'm importing, because excel doenst accept all of them...

Can you explain me how to do it?

Tks in advance...<!-- google_ad_section_end --><!-- / message -->

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.


Board Regular
Jan 27, 2010
To record your macro, follow the steps below:

•Step 1
Go to "Tools > Macro." Choose "Record New Macro" from the options.
•Step 2
Type a name into the macro name box.
•Step 3
Type the location of the macro into the box labeled "Store Macros In." Click "OK."
•Step 4
You are ready to begin recording your macro. Perform the series of tasks you would like to create the macro for.
•Step 5
When you are finished, click "Stop Recording" on the toolbar.

To create your button for running your macro, follow the steps below:

•Step 1
Click "Button" from your forms toolbar.
•Step 2
While holding the left mouse button, drag your mouse to create the button.
•Step 3
Highlight the macro you created to assign and click "ok".
•Step 4
You may wish to right click the button to edit text.

Not sure how to split in 2 sheets.


Well-known Member
Sep 10, 2008
Where simply recording a macro might fall down here is if the data is 'wider' than your version of xl will allow (300+ rows won't go into 2003 for example).

I'd suggest you look up "OpenAsTextStream" in xl's help file and import each row 1 line at a time. You can use split() to divide each string based on commas or whatever other delimiter you're using, which will create a 1 dimensional array which you can then dump 1 element at a time into whichever cells you choose. You just repeat this until the end of the file is reached.



New Member
Aug 10, 2010
this is what i have:

Sub ImportarTextosGrandes()
    Dim ultimaFila, fila, contador As Long
    Dim linea, NomeArquivo As String
    'Calcula a última coluna da sheet
    ultimaColuna = Selection.Column
    Set oSistemaArquivo = CreateObject("Scripting.FileSystemObject")
    'Nome do arquivo a importar
    NomeArquivo = Application.GetOpenFilename(FileFilter:="Text File (*.out),*.out")
    Set arquivo = oSistemaArquivo.OpenTextFile(NomeArquivo, 1, False, -2)
    fila = 1
    contador = 1
    Do While arquivo.AtEndOfStream <> True
        linea = arquivo.ReadLine
        Cells(fila, "a").Value = linea
        'Actualiza barra de status
        Application.StatusBar = "Lendo linha número = " & contador
        fila = fila + 1
        contador = contador + 1
        'Cria nova sheet quando a actual está cheia
        If fila > 51091 Then
            Worksheets.Add after:=ActiveSheet
            fila = 1
        End If
End Sub
this code imports the file that I choose, goes to line that I want and add another sheet to continue. what I want now is, to don't "add" a new one, but continue in another one, ex:

running in sheet 1 (when it ends)
moving to sheet 2 ( and not adding sheet 4)

this means that is breaking the row and adding another sheet and I want to move, not add.

I also need to break the column and move also to another sheet... I',ve tryed a few things, but without sucess...

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...