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!!

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!!

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 :)

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 :)

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?

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....

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....

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!!!!