insert text to a specific cell

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

You need three code segments:

Open and Close file:

Please Login or Register  to view this content.

Startbutton called Start with following code

Please Login or Register  to view this content.

Main Procedure filling the Cells:

Please Login or Register  to view this content.

don't know if it works perfectly (written in about 1 minute )
Simon

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

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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

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

Please Login or Register  to view this content.

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.

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>