View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Mike Fogleman Mike Fogleman is offline
external usenet poster
 
Posts: 1,092
Default Need help trying to open a file and stream data into a sheet...

Susan,
The macro recorder will help you do this. Turn on the recorder and select
Data/Get External Data/Import Text File and use Import wizard to get the
..asc file imported as you want. Once you have this recorded, substitute the
variable filetoopen as the path in the recorded macro. Rem out then delete
parameters you don't need in the query to shorten it up. It may look
something like this:

Sub Import ()
Sheet2.Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;"& filetoopen, Destination:=Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With

Good luck
Mike F
<Susan Hayes wrote in message
...
I have finished writing my program but need help in getting the data
into sheet2. In sheet1 a command button is supposed to have the user
select a file, have the data streamed to sheet2 and delimit it using
the comma.

So far I have this: (partly from vba help in excel and vba excel
book)


filetoopen = Application.getopenfilename("Data Files (*.asc),
*.asc")

Dim fs, f

Set fs = createobject("Scripting.filesystemobject")
set f = fs.opentextfile(filetoopen, 1, tristatetrue)
data = f.readall

with worksheets("sheet2").select
with data
.textfileparsetype=xldelimited
.textfilecommadelimiter=true
endwith
endwith

'data = f.readall
'worksheets("sheet2").select
'cells(1, "a")=data

The remarked above works but puts everything in cell A1.

Any help even to just stream the data to sheet2 is appreaciated. The
macro recorder is no help
The file being loaded has data from tests represent by each line
seperate by commas
ie
2,3,5.6, , 7 test1
4,7,4,8, , 10 test2 etc

Many Thanks
Susan