Consolidate Data from several Excel Files

[mathon]

Hello

I have several Excel-Files which contains among others the following information:

A B C D E F
1
2
3
4 Name Project1
5
6
7
8
9
10 TimeDistribution Hours
11 0001 ProjecMember1 12
12 0002 ProjectMember2 32
13 0003 ProjectMember3 11
14
15
16

There are several Excel-Files which contains such information, there could be more projectmembers quoted or less or equal.
I want to extract from each of these Excel-Files and put it
in a new Excel-File which should contain the following information:
Projektname MemberId MemberName Hours
zB. Projekt1 0001 ProjectMember3 11

It could be possible that the information in the several
Excel-Sheets are placed not everytime at the same row for example. So maybe I have to look for the Name cell and take
the column next to this cell or I have to look for the TimeDistribution and then parse all ProjectMembers as long as there is a projectmember quoted.

Can anybody help in developing the solution for that? that would be great.

Regards

PS:the submission of the posting destroyed the formatting a little bit. the rows are correct, the name is placed in column A, the actual projectname in column B, the time distribution is in column A, the memberids in column A, the memberNames in project B and the Hours in column F.

 

[SkipVought]

Hi,

Check out the Find method, Offset method, CurrentRegion Method, Rows.Count, Columns.Count.

Post back with your code with any problems.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[mathon]

hi,

thank you for your reply, I have written the following code to go through the files:

Sub Consolidate()
   
    Dim aktSh, wb, sh, Pfad, Dateityp, Datei, Zeile
    Set aktSh = ActiveSheet
    [A1] = "project_name"
    [B1] = "Name"
    [C1] = "Hours"
    Path = "D:\Documents and Settings\tex.texter\My Documents\My Projects\Test\Tracking\reports\"
    FileType = "*.xls"
    File = Dir(Path & FileType)
   
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    Application.ScreenUpdating = False
    Row = 1
    
    While File <> ""
        Row = Row + 1
        Set wb = Workbooks.Open(Path & File)
        aktSh.Cells(Row, 1) = File
        Application.StatusBar = "WorkOn " & Path & File
        Dim count As Integer
        For Each sh In wb.Sheets
            'aktSh.Cells(Zeile, sh.Index + 1) = sh.Name
             Cells.Find(What:="Name").Activate
            
        Next
        wb.Close False
        File = Dir()
    Wend
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Set aktSh = Nothing
    Set wb = Nothing
    Columns.AutoFit
    MsgBox "Finished!"
End Sub

Is this correct? - But I do not really know how to code the consolidation process... I looked in the Excel help for Offset, CurrentRegion Method but didnt find anything...

pat

 

[SkipVought]

sorry Offset & CurrentRegion are properties.

Try setting a range...

Dim rng as range
'...
set rng = Cells.Find(What:="Name")
if not rng is nothing then
  'found it. now do something!
  with ThisWorkbook.Sheets("[b]YourConsolidationSheet???[/b]")
     lRowOut = .[A1].currentregion.rows.count + 1
    'assuming the name goes in column A
     .cells(lRowOut, 1).Value = rng.offset(0,1).value
  end with
end if

also assumes that you're running this code in the consolidation workbook.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[mathon]

Yes I wrote my code in a new Excel-File (the consolidation)...unfortunately I cannot translate your pseudocode in real vba code...maybe you can give me some more advise and if my code - opening the files, etc. is correct.

 

[SkipVought]

it is not pseudocode. The only thing pseudo in it is the name of your condolidation worksheet.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}