Import and transpose multiple txt files 1

[SurfdudeNL]

Hi,
I have multiple .txt files, each containing 3 lines of variable lengts. I want to import them into Excel and make every line a database record (transpose?).

.txt file example:
John
Pete Sampras
Ike Turner

Excel should look like this:
John Pete Sampras Ike Turner
etc.

Any suggestions?

 

[SkipVought]

hi SurfdudeNL

You read the file to EOF into an array that you ReDim Preserve with each read.

Then write the array to the next available row.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SurfdudeNL]

Thanks Skip,

I'm not that familiar with array's can you help me with some code?

 

[SkipVought]

Is this a classroom problem? You posted as a programmer.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SurfdudeNL]

Ah no,

In the past I accidentally signed up as a programmer, I am certainly not one. The proof: I don't even know how to change this into something more appropriate ;-)

Arnie

 

[SkipVought]

Arnie,

Option Base 1
Sub Main()
    Dim fs, f, fl, fc, Recs()
    fn = Application.GetOpenFilename
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(ParsePath(fn))
    Set fc = f.Files
    For Each fl In fc
        Open fl For Input As #1
        i = 1
        Redim Recs(i) 
        Do While Not EOF(1)
           Redim Preserve Recs(i)
           Line Input #1, Recs(i)
           i = i + 1
        Loop
        Close #1
        for i = 1 to ubound(recs, 1)
           with Sheet1
             r = .cells(1,1).currentregion.rows.count + 1
             .Cells(r, i).Value = recs(i)
           end with
        next
    Next
End Sub

Make sure that you put a row of headings in row 1 before running this code.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SurfdudeNL]

Thanks, but I get an ActiveX-... can't make object error on the CreateObject command.

What's wrong?

 

[SkipVought]

In the vb editor tools/references, what libraries are referenced?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SurfdudeNL]

Vis Basic For apps
Ms Excel 11.0 Object lib
OLE autom

Thats all

 

[SkipVought]

well it is in the VB library.

and you pasted the code EXACTLY as it was posted?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SurfdudeNL]

Yep,

I first get a sub of function error on ParsePath.

 

[SkipVought]

So sorry -- I did not post the function

Function ParsePath(fn)
    Dim i, s
    For i = Len(fn) To 1 Step -1
        s = Mid(fn, i, 1)
        Select Case s
        Case "\"
            ParsePath = Left(fn, i - 1)
            Exit Function
        End Select
    Next
End Function

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SurfdudeNL]

I still get the ActiveX error 429...

 

[SkipVought]

Is this code in a Module or an Excel Object (sheet or workbook boject)

Should be in a Module.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SurfdudeNL]

It's in A module...

 

[SkipVought]

send your workbook to

skip @ theofficeexperts.com

and remove spaces in e mail address

I will take a look in about 2 hours.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[PHV]

Seems you don't have WSH installed.

Hope This Help
PH.

 

[SurfdudeNL]

Thanks PHV that did it!