Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
Hello
Now I've got big problem :) I've directories like this: c:\backup\XXXX\Y where XXXX is the year e.g. c:\backup\2006\Y and Y is the month e.g c:\backup\2006\5 but c:\backup\2006\10 My text files which are in those directories are named hhmmssddmmyyyy.txt e.g 10123425082006.txt They look like this: 2006-08-25 13:33:20 82,8 g 2006-08-25 13:34:10 58,5 g between each column there is Tab My problem is that I would like ask the user from which month he would like to import text files to workbook.sheet2 (I've already done it by DateAdd) and then my macro should import all the text files in directory to one sheet one after another... I can't make it... Thx for any help!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
Bratek,
Try the sub below. HTH, Bernie MS Excel MVP Sub Consolidate() Dim mnthNum As Integer Dim myBook As Workbook With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With With Application.FileSearch .NewSearch 'Change this to your directory mnthNum = Application.InputBox("What month Number?", Type:=1) On Error GoTo ErrHandler: .LookIn = "C:\backup\2006\" & mnthNum .SearchSubFolders = False .Filename = "*.txt" If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) Range("A1").CurrentRegion.Copy _ ThisWorkbook.Worksheets(1).Range("A65536").End(xlU p)(2) myBook.Close Next i End If ErrHandler: End With With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "bratek" wrote in message ups.com... Hello Now I've got big problem :) I've directories like this: c:\backup\XXXX\Y where XXXX is the year e.g. c:\backup\2006\Y and Y is the month e.g c:\backup\2006\5 but c:\backup\2006\10 My text files which are in those directories are named hhmmssddmmyyyy.txt e.g 10123425082006.txt They look like this: 2006-08-25 13:33:20 82,8 g 2006-08-25 13:34:10 58,5 g between each column there is Tab My problem is that I would like ask the user from which month he would like to import text files to workbook.sheet2 (I've already done it by DateAdd) and then my macro should import all the text files in directory to one sheet one after another... I can't make it... Thx for any help!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
everything works almost perfectly... almost.. the problem is that it
does search and open but then it doesn't show anything... when I turn off the line myBook.Close it's show every file in new workbook :( How to make it works?? that means all files showed in one sheet in one workbook?? thx :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
Brateck,
If you simply open one of your text files, which cells are filled with values? My assumption was that the txt files would start in cell A1, and have contiguous data. You could try changing this line: Range("A1").CurrentRegion.Copy _ ThisWorkbook.Worksheets(1).Range("A65536").End(xlU p)(2) to this myRows = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count ActiveSheet.UsedRange.Copy _ ThisWorkbook.Worksheets(1).Cells(myRows + 1, 1) You may need to add Dim myRows As Long at the top of your sub. HTH, Bernie MS Excel MVP "bratek" wrote in message ps.com... everything works almost perfectly... almost.. the problem is that it does search and open but then it doesn't show anything... when I turn off the line myBook.Close it's show every file in new workbook :( How to make it works?? that means all files showed in one sheet in one workbook?? thx :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
Hi Bernie,
It doesn't work properly unfortunate.. I change the code a little bit: myRows = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count ActiveSheet.USEDRANGE.Copy _ ThisWorkbook.Worksheets(1).Cells(myRows, 1) myRows = myRows + 1 And everything now is almost good... the problem is: if I have e.g. 3 files and sum of rows to import is 20 it will import only 18... there won't be the last lines of first two files... why? I am trying to solve it.. any ideas why it happens like this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
It imports well (I checked with debuger) but when it imports next files
it overwrites the last line of previous file... well... any idea how to change it? :) It would be great.... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
You need to use the code that I posted:
myRows = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count ActiveSheet.UsedRange.Copy _ ThisWorkbook.Worksheets(1).Cells(myRows + 1, 1) The last row is overwritten because you were incrementing myRows after you did the copy, not before. HTH, Bernie MS Excel MVP "bratek" wrote in message oups.com... It imports well (I checked with debuger) but when it imports next files it overwrites the last line of previous file... well... any idea how to change it? :) It would be great.... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
import few text files from subdirectories
Hi Bernie!
That was not a problem.. because I tried both way But finaly I made it.. myRows = ThisWorkbook.Worksheets(1).Range("A65536").End(xlu p).Row + 1 thank You for Your Help!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import text files into EXCEL | Excel Discussion (Misc queries) | |||
Import multiple text files (Macro) | Excel Discussion (Misc queries) | |||
Import 2 text files into 2 separate columns? | Excel Discussion (Misc queries) | |||
Import of Multiple Text Files | Excel Programming | |||
How do I import several text files into excel or access ? | Excel Discussion (Misc queries) |