Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make one excel sheet from multiple text files
Hi,
I have 200 text files with 3 lines of text and I want to make one sheet of all those files, so it would be a sheet with 600 lines - all the text from those files. Any help would be great! Marko Svaco |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make one excel sheet from multiple text files
This code needs to be modified, but works pretty well for excel spreadsheets.
Some people have had problems with the FileSearch function. Put all you files in one directory and change Lookin directory. and Filetype (to text). The .COPY statement will also need tto be modified. Sub GetData() Set fs = Workbooks.Application.FileSearch With fs .LookIn = "c:\Temp" .FileType = msoFileTypeExcelWorkbooks .Execute End With For i = 1 To fs.FoundFiles.Count MyfileName = fs.FoundFiles(i) Workbooks.Open Filename:=MyfileName, ReadOnly:=True ' Removed pathname from file name so it can be referenced in this program. 'Basic doesn't like the full pathname???? stupid microsoft Do While (1) CharPosition = InStr(MyfileName, "\") If CharPosition 0 Then MyfileName = Mid(MyfileName, CharPosition + 1) Else Exit Do End If Loop Workbooks(MyfileName).Worksheets("Sheet1").Range(" B22:D24").Copy _ Destination:=Workbooks(ThisWorkbook.Name).Workshee ts("Sheet1"). _ Range("A10").Offset(rowOffset:=0, columnOffset:=0) Workbooks(MyfileName).Close SaveChanges:=False Next i End Sub "cosva" wrote: Hi, I have 200 text files with 3 lines of text and I want to make one sheet of all those files, so it would be a sheet with 600 lines - all the text from those files. Any help would be great! Marko Svaco |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make one excel sheet from multiple text files
this will get around the problem of having the sheet name and more closely
aligns to the OP's original request. To the best of my knowledge, there is no Filetype argument for txt, so you would need to use the method illustrated here. Sub GetData() Dim bk as Workbook, fs as FileSearch Dim i as Long Set fs = Workbooks.Application.FileSearch With fs .NewSearch .LookIn = "c:\Temp" .FileName = ".txt" .SearchSubFolders = False .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count set bk = Workbooks.Open(fs.FoundFiles(i), ReadOnly:=True) Filename:=MyfileName, ReadOnly:=True bk.Worksheets(1).Range("1:3").Copy _ Destination:=ThisWorkbook.Worksheets("Sheet1"). _ Cells(rows.count,1).End(xlup) _ .Offset(1,0) bk.Close SaveChanges:=False Next i End if End With End Sub -- Regards, Tom Ogilvy "Joel" wrote: This code needs to be modified, but works pretty well for excel spreadsheets. Some people have had problems with the FileSearch function. Put all you files in one directory and change Lookin directory. and Filetype (to text). The .COPY statement will also need tto be modified. Sub GetData() Set fs = Workbooks.Application.FileSearch With fs .LookIn = "c:\Temp" .FileType = msoFileTypeExcelWorkbooks .Execute End With For i = 1 To fs.FoundFiles.Count MyfileName = fs.FoundFiles(i) Workbooks.Open Filename:=MyfileName, ReadOnly:=True ' Removed pathname from file name so it can be referenced in this program. 'Basic doesn't like the full pathname???? stupid microsoft Do While (1) CharPosition = InStr(MyfileName, "\") If CharPosition 0 Then MyfileName = Mid(MyfileName, CharPosition + 1) Else Exit Do End If Loop Workbooks(MyfileName).Worksheets("Sheet1").Range(" B22:D24").Copy _ Destination:=Workbooks(ThisWorkbook.Name).Workshee ts("Sheet1"). _ Range("A10").Offset(rowOffset:=0, columnOffset:=0) Workbooks(MyfileName).Close SaveChanges:=False Next i End Sub "cosva" wrote: Hi, I have 200 text files with 3 lines of text and I want to make one sheet of all those files, so it would be a sheet with 600 lines - all the text from those files. Any help would be great! Marko Svaco |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a text box accessible in a protected Excel sheet? | Excel Worksheet Functions | |||
How can I make Excel Files save as the text appers in a cell, can. | Excel Discussion (Misc queries) | |||
Multiple files created from 1 sheet are corrupt - Excel 2000 | Excel Programming | |||
importing a lot of text files to excel sheet at one go | Excel Programming | |||
findinf text in multiple excel files | Excel Programming |