Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 9th, 2004, 11:32 AM
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing a text file to excel using VB

hello there - I am a newbie and could do with a bit of a hand. I have this bit of code which is taking data from a text file and importing it in to excel.

However i am having a few problems with the code that i cant solve:
1) When I run it brings up the contents of the text file 7 times in column A1 - as there are 7 items in the txt file.

2) When I import the data it does not split the text for e.g in the text file i have a description caled SC and a value of 24.00 (tab delimited) but the code brings everyting in one cell.

I would post the text file but I dont think I can attach anything on this forum.

Any help is more than appreciated

Cheers

Option Explicit

Sub Read_Text_File()
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoTS As Scripting.TextStream
Dim vaData As Variant
Dim i As Long, j As Long

Set fsoObj = New Scripting.FileSystemObject
Set fsoFile = fsoObj.GetFile("c:\testing.txt")
Set fsoTS = fsoFile.OpenAsTextStream(ForReading, TristateFalse)

vaData = Split(fsoTS.ReadAll, Chr(13))

fsoTS.Close

i = UBound(vaData)

For j = 0 To i
    vaData(j) = Application.Clean(vaData(j))
Next j

Range(Cells(1, 1), Cells(i, 1)).Value = vaData

Set fsoFile = Nothing
Set fsoObj = Nothing
End Sub
 
Old April 10th, 2004, 03:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This seems to work:

Sub Read_Text_File()
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoTS As Scripting.TextStream
Dim vaData As Variant
Dim i As Long, j As Long

Set fsoObj = New Scripting.FileSystemObject
Set fsoFile = fsoObj.GetFile("c:\testing.txt")
Set fsoTS = fsoFile.OpenAsTextStream(ForReading, TristateFalse)


j = 1
Do Until fsoTS.AtEndOfStream
    vaData = Split(fsoTS.ReadLine, Chr(44))
    Range(Cells(j, 1), Cells(j, 2)).Value = vaData
j = j + 1
Loop

fsoTS.Close
Set fsoFile = Nothing
Set fsoObj = Nothing
End Sub




Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Multiple Files Into Excel Using VB bsac Excel VBA 4 January 27th, 2014 11:37 AM
importing text file into excel stealthdevil Excel VBA 2 July 10th, 2007 11:28 AM
Importing text file into excel anamarijaf Excel VBA 4 January 27th, 2007 05:50 AM
Importing a text file DBN Beginning VB 6 6 April 24th, 2006 02:52 PM
importing Excel into Oracle using VB hagit Oracle 0 January 21st, 2004 07:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.