search all worksheets in a folder 2

[SharonMee]

Hello All:

Please tell me if the following is possible with a macro, if so, please help me:

The following pertain to Excel. I have a folder called Claims and it’s in another folder called Cost Department.

In the Claims folder, I have folder called Data and workbooks that are labeled January 2004 e.t.c. to July 2004, next month, a new worksheet will be created next month for August 2004.

In the January 2004 workbook, I want to search in 2 columns in the Raw Data worksheet, this is the same worksheet name for the other workbooks e.g. February 2004, March 2004 e.t.c.

I know that I can record a macro to open all the worksheets available right now in the Data folder. An example of the macro I recorded to open a worksheet in a particular month folder is:

Sub openfolders()
Workbooks.Open Filename:= _
"C:\Documents and Settings\XXX\Desktop\Cost Department\Claims\Data\January 2004.xls"
End Sub

Question:
How do I write a macro that will open all the available workbooks in the Data folder? Say at the time I am designing this code the last month workbook available is: July 2004. If I am using this code in December, how do I make sure the code opens all the available month workbooks.
E.g. I would like Excel to open the Cost Department folder in my desktop, then open the Claims folder, then open the Data folder, open January 2004 workbook, open the Raw Data worksheet and then close it (I will be posting another question for searching through the worksheet later).
Then open the February 2004 workbook, open the Raw Data worksheet, and then close it, and do the same for all the other workbooks in the Data folder.

Is it possible?

We presently have a very very manual way of searching through these worksheets, and I was thinking a macro could help out, please help.

Thanks again.


SharonMee

 

[BrainB]

In the absence of another reply, this will hopefully help you on your way :-

'===============================================
'- Generic code for transferring data from
'- all sheets in all workbooks contained in a folder
'- to a master sheet.
'-
'- workbooks must be the only ones in the folder
'- worksheets must be contain tables which are
'- identical to the master, headings in row 1.
'- master sheet is remade each time.
'- run this code from the master book
'-
'----------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'----------------
Sub NEW_MASTER()
'----------------
    Application.Calculation = xlCalculationManual
    ChDrive ActiveWorkbook.Path
    ChDir ActiveWorkbook.Path
    ToBook = ActiveWorkbook.Name
    Set ToSheet = ActiveWorkbook.Worksheets(1)
    NumColumns = ToSheet.Range("A1").End(xlToRight).Column
    ToRow = ToSheet.Range("A65536").End(xlUp).Row
    '- clear master
    If ToRow <> 1 Then
        ToSheet.Range(Cells(2, 1), Cells(ToRow, NumColumns)).ClearContents
    End If
    ToRow = 2
    '- main loop to open each file
    FromBook = Dir("*.xls")
    While FromBook <> ""
        If FromBook <> ToBook Then
            Application.StatusBar = FromBook
            Transfer_data
        End If
        FromBook = Dir
    Wend
    '-- close
    MsgBox ("Done.")
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'-------------------------------------------------

Sub Transfer_data()
    Workbooks.Open FileName:=FromBook
    For Each FromSheet In Workbooks(FromBook).Worksheets
        LastRow = FromSheet.Range("A65536").End(xlUp).Row
        '- copy paste
        FromSheet.Range(Cells(2, 1), Cells(LastRow, NumColumns)).Copy _
            Destination:=ToSheet.Range("A" & ToRow)
        '- set next ToRow
        ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
    Next
    Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP ======================================

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[BrainB]

Have a look at this too :-

http://www.tek-tips.com/viewthread.cfm?SQID=876270&SPID=707&page=1

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[Chance1234]

To follow on from BrainB

to make that code just to refer to spreadsheets you need to add the lines

If LCase(Right(fiL.Name, 4)) = ".xls" Then
Debug.Print fiL.Name
End If

instead of

Debug.Print fiL.Name



Chance,

Filmmaker, gentleman and soon to be a mac user

http://WWW.5YLAC.S5.COM

,

http://WWW.HOWTOBESPOOKY.CO.UK

 

[SharonMee]

Thanks, guys, I am going to try the code out, if I am having problems, I'll let you know. Thanks a lot.

 

[bodo62]

Hello all.

How do I do if I instead of tranferring data from files in the folder to one sheet, want to copy the worksheets from each file as a new sheet in the Masterbook ?

I have let's say 10 files with one sheet each, all sheets have the same name as the workbook names which contains them (they are generated automatically by another program each month).

Example: In the folder we have files SA_SE.XLS, SA_US.XLS and SA_DE.XLS with sheet names SA_SE, SA_US and SA_DE.

I want the code to copy those 3 sheets into the MasterBook, whenever I want to run the code. The number of files of course can vary from one month to another and in this case I need the Masterbook to always be emptied from sheets, before the code copies the new sheets into it.