Hi

I'm trying to insert text from a .txt file into excel 03 and have i
start in a specific cell. the information also needs to be delimite
with the delimiters being the tabs.

the info is about 2200rows long and 17 colomns wide. Ideally th
information should start from c7. I can get the text to fill the scree
but nothing i do seems to change its position.

any help would be great

Mat

--
matthewwooki
-----------------------------------------------------------------------
matthewwookie's Profile: http://www.excelforum.com/member.php...fo&userid=2750
View this thread: http://www.excelforum.com/showthread.php?threadid=47017

Hi

Thanks for the help...can't believe it just took you a minute...i'm
slightly jealous

i've put it into excel....however when i run it i get a 'run time error
424' Object Required.

It might be just be putting it in wrong and having no understanding of
the code...well, not really anyway.

Public Sub open_textstream(path As String)
Set fs = CreateObject("Scripting.FileSystemObject")
Set file = fs.GetFile(path)
filesize = file.Size 'wird im Moment nicht benötigt
Set ts = file.OpenAsTextStream(1, -2)
End Sub


Public Sub close_textstream()
ts.Close - This was where the error occured.
End Sub


Private Sub start_click()
Call open_textstream("C:\Documents and Settings\bbnr843\My
Documents\frank.txt")
Call main
Call close_textstream
End Sub

Sub main()
Do While ts.endofstream < ""
data = ts.readline()
dat = Split(data, Chr(9)) 'i think chr(9) was TAB
For i = 0 To UBound(dat())
Cells(countrows + 3, i + 7) = dat(i)
Next i
countrows = countrows + 1
Loop
End Sub


Thanks for your help

Matt


--
matthewwookie
------------------------------------------------------------------------
matthewwookie's Profile: http://www.excelforum.com/member.php...o&userid=27503
View this thread: http://www.excelforum.com/showthread...hreadid=470177

edit the code like this:


Code:
--------------------

dim ts
Public Sub open_textstream(path As String)
Set fs = CreateObject("Scripting.FileSystemObject")
Set file = fs.GetFile(path)
filesize = file.Size 'wird im Moment nicht benötigt You can delete this line, it was just a fragment of my own code ;)
Set ts = file.OpenAsTextStream(1, -2)
End Sub
--------------------


The problem was, that the sub's didn't know the textstream cause they
didn't create on their own. if you "dim" the variable before
referencing to textstream it becomes global, so any sub could access
it.

Now it should work,
Simon


--
moondark
------------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php...o&userid=27390
View this thread: http://www.excelforum.com/showthread...hreadid=470177

Matt,
Unless you have good reason to use the FSO, Excel has it own import feature;
check out Data Get External Data Import Text File.
Record a macro as you do it if you need the code.

NickHK

"matthewwookie"
wrote in message
news:matthewwookie.1vt6id_1127473531.6434@excelfor um-nospam.com...

Hi

I'm trying to insert text from a .txt file into excel 03 and have it
start in a specific cell. the information also needs to be delimited
with the delimiters being the tabs.

the info is about 2200rows long and 17 colomns wide. Ideally the
information should start from c7. I can get the text to fill the screen
but nothing i do seems to change its position.

any help would be great

Matt


--
matthewwookie
------------------------------------------------------------------------
matthewwookie's Profile:
http://www.excelforum.com/member.php...o&userid=27503
View this thread: http://www.excelforum.com/showthread...hreadid=470177

Thanks for that last bit of info about the importing it from another
source. However, for some reason, the option to do so is always grayed
out! is there a prereq that i have to do before i'm allowed to import?

Thanks for the code update...however in the main section...the
AtEndOfStream line has a debug error. any ideas?

Cheers for the help

Matt


--
matthewwookie
------------------------------------------------------------------------
matthewwookie's Profile: http://www.excelforum.com/member.php...o&userid=27503
View this thread: http://www.excelforum.com/showthread...hreadid=470177

Here's the whole code again (check to change the path to textfile;))


Code:
--------------------
Dim ts
Public Sub open_textstream(path As String)
Set fs = CreateObject("Scripting.FileSystemObject")
Set file = fs.GetFile(path)
Set ts = file.OpenAsTextStream(1, -2)
End Sub


Public Sub close_textstream()
ts.Close
End Sub


Private Sub commandbutton1_click()
Call open_textstream("N:\wtsrv\profiles\ziviw3\Desktop\ test.txt")
Call main
Call close_textstream
End Sub

Sub main()
Do While ts.AtEndOfStream < True
data = ts.readline()
dat = Split(data, Chr(9)) 'i think chr(9) was TAB
For i = 0 To UBound(dat)
Cells(countrows + 7, i + 3) = dat(i)
Next i
countrows = countrows + 1
Loop
End Sub

--------------------


I think the problem was the "dim ts" line. I got the same problem after
deleting it. I tried this one and it worked at my computer, so i think
it should work at yours, too. If it doesn't work, blame Microsoft ;)

Regards,

Simon


PS: If it still doesnt work, try to contact me via ICQ (123696416)
everyday after 6pm(CET) or weekends.


--
moondark
------------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php...o&userid=27390
View this thread: http://www.excelforum.com/showthread...hreadid=470177

Matt,
Can't say I have ever seen that, but at a guest it may be because the text
filters were not installed initially.
rerun the Office installation, choosing Add/remove features and see if there
anything obvious to included.

NickHK

"matthewwookie"
wrote in message
news:matthewwookie.1vykyd_1127725525.5013@excelfor um-nospam.com...

Thanks for that last bit of info about the importing it from another
source. However, for some reason, the option to do so is always grayed
out! is there a prereq that i have to do before i'm allowed to import?

Thanks for the code update...however in the main section...the
AtEndOfStream line has a debug error. any ideas?

Cheers for the help

Matt


--
matthewwookie
------------------------------------------------------------------------
matthewwookie's Profile:
http://www.excelforum.com/member.php...o&userid=27503
View this thread: http://www.excelforum.com/showthread...hreadid=470177