Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help trying to open a file and stream data into a sheet...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help trying to open a file and stream data into a sheet...
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 On Sun, 08 Aug 2004 14:23:37 GMT, "Mike Fogleman" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does a Multi-sheet/user file open on sheet two every time? | Excel Discussion (Misc queries) | |||
In-sheet link to open file not working | Excel Discussion (Misc queries) | |||
hide sheet on file open | Excel Discussion (Misc queries) | |||
Bloomberg overwrites data-need to save stream of data in a new she | Excel Worksheet Functions | |||
hidden sheet to record open of file | Excel Discussion (Misc queries) |