import txt to excel

peterhall

New Member
Joined
Aug 10, 2010
Messages
2
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 -->
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mrnacar

Board Regular
Joined
Jan 27, 2010
Messages
184
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.
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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.

HTH
 

peterhall

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

Code:
Sub ImportarTextosGrandes()
    Dim ultimaFila, fila, contador As Long
    Dim linea, NomeArquivo As String
    'Calcula a última coluna da sheet
    Selection.End(xlDown).Select
    ultimaColuna = Selection.Column
    Selection.End(xlUp).Select
    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
    Loop
    
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,133,715
Messages
5,660,450
Members
418,580
Latest member
JP82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top