Looping through multiple spreadsheets

[NickSmith]

I have a series of spreadsheets all set up using the same layout but with different data in each.

What I need to do is loop through each spreadsheet and take values from certain cells and copy these to a new spreadsheet which will hold all the data.

For each spreadsheet I will always need cells C1 C3 and E4 and these should form the first 3 columns of each row of data I extract.

Then I need to use a lookup table held in another spreadsheet that has 2 columns a date and a column reference to identify the column reference to grab my next piece of data from. The initial date to look up will the next fridays date after todays date. Eg if running it today I would want to look up the column reference associated with 11th march 2011. I then need to take the value from this column (and the date from the lookup) and add this to my row.

So I will end up with each row containing the values of C1 C3 and E4, a date and the value of the appropriate cell for the column.
The next row will have the same data but will have moved on a week and this will continue until the end of the lookup table.
Once all rows have been created these need to be written to a spreadsheet that will combine all the data.

So potentially I could have 89 rows x 52 columns of data for each spreadsheet. Luckily I will only have about 10 spreadsheets to loop through so shouldn't get anywhere near the max no of rows.

To be honest I have never attempted anything of this magnitude in Excel and am a bit of a loss of where to start.

Can anyone help ?

Nick

 

[GlennUK]

Sorry but it's not clear what you're aiming for here. What do you mean when you say "The next row will have the same data but will have moved on a week"?

Can you explain how you get to this: "So potentially I could have 89 rows x 52 columns of data for each spreadsheet"? 52 columns? How? Something to do with week numbers at a guess!?!?

 

[NickSmith]

Ok perhaps I need to explain further.

Each spreadsheet is a resourcing spreadsheet for a project.
Starting from cell C10 and running down to cell C99 are all the resources that could potentially be working on the project.
Starting from column AD and going through to column EZ are the percentage figures required for that resource on a week by week basis. Unfortunately the way the spreadsheet have been designed at each month end there is a total column. This means it is not possible just to keep working through column by column to grab the resource percentage because some of the cells will be totals. Another problem is that although each column represents a date it has been written in a way that the date is not readable as a date (see below)
<TABLE style="WIDTH: 159pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=211><COLGROUP><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" span=4 width=41><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><TBODY><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 159pt; HEIGHT: 19.5pt; BORDER-TOP: #666699 0.5pt solid; BORDER-RIGHT: #666699 0.5pt solid" class=xl73 height=26 width=211 colSpan=5>April-11</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl71 height=26>Rqd</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl71>Rqd</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl71>Rqd</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl71>Rqd</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl71>Rqd</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 31pt; HEIGHT: 19.5pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl72 height=26 width=41>1</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl72 width=41>8</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl72 width=41>15</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl72 width=41>22</TD><TD style="BORDER-BOTTOM: #666699 0.5pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 0.5pt solid" class=xl72 width=47>29</TD></TR></TBODY></TABLE>


So this is where the lookuptable comes in. It holds the correct date and the column associated with that date. So in the above example weekending 1st April 2011 is referenced in the lookup table as column CP.

So when I have established what the next friday is after today, I look this value up in the lookup table to get my start column in the resource spreadsheet. I can then get the resource percentage for that date and person. Then I need to move forward to the next friday and get the next percentage and so on until the end of the year. So potentially if I'm doing this at the start of the year I will get 52 weeks of resourcing data for each resource and there could be 89 resources (very unlikely though).

And for each resource I need to write out a row for each date. So if I only had 1 resource and we were at the start of the year then I would write out 52 rows of data.

Is that any clearer?

 

[GlennUK]

That's much clearer thanks. I'll have a think about this over lunch, but first thoughts are that it's quite simple in terms of processing.

 

[GlennUK]

A question ... are you not going to want to have the resource ( from C10:C99 ) be in the output list?

 

[NickSmith]

Thanks Glenn.

Oops yes of course. I need the resource name otherwise the whole thing would be pretty pointless.

 

[GlennUK]

Hi, this code will do the processing for a single sheet:

Sub singlesheetexample()
    Dim lngFindNextFriday As Long, strColumnToDo As String
    Dim intDateOffset As Integer, lngCurrentDate As Long
     
    enddate = #12/31/2011#
    
    For iresource = 10 To 99
        lngCurrentDate = Date ' start at today
        lngCurrentDate = lngCurrentDate + 7 - Weekday(lngCurrentDate, vbSaturday) ' point to next Friday
        For iweek = 1 To 52
            If lngCurrentDate > enddate Then Exit For
            colfindpos = Application.Match(lngCurrentDate, Sheet4.Range("A2:A999"), 0)
            If IsError(colfindpos) Then
                MsgBox "Lookup failed for date " & lngCurrentDate & " in dates table"
                End
            End If
            strColumnToDo = Sheet4.Range("B2:B999").Cells(colfindpos)
            If Sheet3.Range(strColumnToDo & iresource) <> "" Then
                nextrow = Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
                Sheet5.Range("A" & nextrow).Value = Sheet3.Range("C1")
                Sheet5.Range("B" & nextrow).Value = Sheet3.Range("C3")
                Sheet5.Range("C" & nextrow).Value = Sheet3.Range("E4")
                Sheet5.Range("D" & nextrow).Value = Sheet3.Range("C" & iresource)
                Sheet5.Range("E" & nextrow).Value = Sheet3.Range(strColumnToDo & iresource)
            End If
            
            lngCurrentDate = lngCurrentDate + 7
            
        Next
    Next
End Sub

currently using Sheet3 for the data sheet, Sheet4 for the lookup table, and Sheet5 for the output sheet.

 

[NickSmith]

Thanks Glenn, I'll give this a whirl.

 

[NickSmith]

Glenn ,just though I'd let you know that it worked a treat. I have adapted it somewhat to cope with multiple files (which the user selects) and put a few displays in just to provide an update on progress.

Just one little bug I can't stop, is when the code opens up one of the resourcing sheets the focus switches to the sheet being opened rather than sticking with the original workbook thus negating my nice little progress box. Any idea how I stop it doing that ?

I have copied the code below so you can see what I have done.

Private Sub CommandButton1_Click()
Dim lngFindNextFriday As Long, strColumnToDo As String
Dim intDateOffset As Integer, lngCurrentDate As Long
Dim wbkResourceMaster As Workbook
Dim wbkRSS As Workbook
Dim lCount As Long
Dim lcountdone As Long

Dim strExtension As String
Dim wkbname As Variant
Dim xlsFiles As Variant

Application.DisplayAlerts = False
Application.ScreenUpdating = True
Set wbkResourceMaster = ThisWorkbook
wbkResourceMaster.Sheets("Sheet2").Range("i5").Value = 0
wbkResourceMaster.Sheets("Sheet2").Range("i6").Value = 0
wbkResourceMaster.Sheets("Sheet2").Range("i7").Value = " "
ChDir "C:\Documents and Settings\Nick\My Documents\Test\"

'Get the workbooks to open
xlsFiles = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
If VarType(xlsFiles) = vbBoolean Then Exit Sub
For Each wkbname In xlsFiles
lCount = lCount + 1
Next wkbname
'wbkResourceMaster.Activate
wbkResourceMaster.Sheets("Sheet2").Range("i5").Value = lCount
For Each wkbname In xlsFiles
wbkResourceMaster.Sheets("Sheet2").Range("i7").Value = wkbname
Set wbkRSS = Workbooks.Open(Filename:=wkbname, ReadOnly:=True)
wbkRSS.Sheets("FC Form").Select
Set wbkRSS = ActiveWorkbook
enddate = #12/31/2011#

For iresource = 10 To 99
wbkResourceMaster.Activate
lngCurrentDate = Date ' start at today
lngCurrentDate = lngCurrentDate + 7 - Weekday(lngCurrentDate, vbSaturday) ' point to next Friday
For iweek = 1 To 16
If lngCurrentDate > enddate Then Exit For
colfindpos = Application.Match(lngCurrentDate, Sheet2.Range("A3:A54"), 0)
If IsError(colfindpos) Then
MsgBox "Lookup failed for date " & lngCurrentDate & " in dates table"
End
End If
strColumnToDo = Sheet2.Range("B3:B54").Cells(colfindpos)

If IsError(wbkRSS.Sheets("FC Form").Range("B" & iresource)) Then Exit For
If wbkRSS.Sheets("FC Form").Range(strColumnToDo & iresource) <> "" And wbkRSS.Sheets("FC Form").Range(strColumnToDo & iresource) <> 0 Then
nextrow = wbkResourceMaster.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wbkResourceMaster.Sheets("Sheet1").Range("A" & nextrow).Value = wbkRSS.Sheets("FC Form").Range("C1")
wbkResourceMaster.Sheets("Sheet1").Range("B" & nextrow).Value = wbkRSS.Sheets("FC Form").Range("C3")
wbkResourceMaster.Sheets("Sheet1").Range("C" & nextrow).Value = wbkRSS.Sheets("FC Form").Range("E4")
wbkResourceMaster.Sheets("Sheet1").Range("D" & nextrow).Value = wbkRSS.Sheets("FC Form").Range("C" & iresource)
wbkResourceMaster.Sheets("Sheet1").Range("E" & nextrow).Value = wbkRSS.Sheets("FC Form").Range(strColumnToDo & iresource)
wbkResourceMaster.Sheets("Sheet1").Range("f" & nextrow).Value = lngCurrentDate
End If

lngCurrentDate = lngCurrentDate + 7

Next
Next

wbkRSS.Close SaveChanges:=False
lcountdone = lCount - 1
wbkResourceMaster.Sheets("Sheet2").Range("i6").Value = lcountdone
Next wkbname
wbkResourceMaster.Sheets("Sheet2").Range("i6").Value = lCount
MsgBox "Resource data load completed"
End Sub