import few text files from subdirectories

[bratek]

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

 

[Bernie Deitrick]

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(xlUp)(2)
myBook.Close
Next i
End If

ErrHandler:
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

 

[bratek]

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

 

[Bernie Deitrick]

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(xlUp)(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]

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?

 

[bratek]

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

 

[Bernie Deitrick]

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]

Hi Bernie!

That was not a problem.. because I tried both way

But finaly I made it..

myRows = ThisWorkbook.Worksheets(1).Range("A65536").End(xlup).Row + 1

thank You for Your Help!!!!