import txt to excel

[peterhall]

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 -->

 

[mrnacar]

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]

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]

this is what i have:

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...

 

[mancubus]

this may help:

http://www.rondebruin.nl/txtcsv.htm