Need help trying to open a file and stream data into a sheet...

[Susan Hayes]

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

 

[Mike Fogleman]

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]

Hi
I did as you said and it worked great, but I have some problems making
the open file window show only *.asc. I have removed the filetoopen
line and it also works.

How can I change this part to just show *.asc files

Private Sub CommandButton1_Click()


filetoopen = Application.GetOpenFilename("Data Files (*.asc),*.asc")
Sheet2.Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & filetoopen, Destination:=Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With

End Sub

Thanks again
This group is a great help