Loop through workbooks combining path and name

Question

I have a workbook for each week of the year. And I have these workbooks for multiple years. I want to gather data from each workbook and each worksheet. Gathering the data from each sheet is already working fine.

Structure is like this;

Year - different folder
Week - different workbook
Day - different worksheet

I'm struggling with looping through the workbooks. The sheets is already solved. I want to combine the year and the week and I used the workbook path to do so.

Workbooks.Open ("L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2017\Aanvragen ME week 01.xlsx")

I already tried using an integer to loop through multiple files, which doesn't seem to work.I also tried setting up an array but at that point its seem I'm unable to combine the workbook path - (I used the path to distinguish between the years).

Dim i As Integer

For i = 1 To 9
Application.ScreenUpdating = False

    Workbooks.Open ("L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2017\Aanvragen ME Week 0i.xlsx")
    Windows("Aanvragen ME Week 0i.xlsx").Activate
    Sheets("Vrijdag 1").Select
    Range("A66:I77").Select
    Selection.Copy
    Windows("Book5").Activate

Code in between, finding the additional data

 Sheets("PLANT 1 & 2").Select
    Range("C3").Select
    Selection.End(xlDown).Offset(0, -1).Select
    Selection = "Week0i"
    Range("C3").Select
   Selection.End(xlDown).Offset(0, -1).Select
   Range(Selection, Selection.End(xlUp)).Select
    Selection = "Week0i"

    Windows("Aanvragen ME Week 0i.xlsx").Activate
    ActiveWorkbook.Application.CutCopyMode = False
    ActiveWorkbook.Close SaveChanges:=False

Next i

Any idea how to solve this?

Thanks in advance.

It sounds like you already played with some code. Please show what you tried - it's much easier to go from there. Aside from that: Have you researched string concatenation in VBA? It seems to me that you might be able to find your answer there. — Inarion, Jul 31 '18 at 09:22
Hey @Inarion I just added the code where I used the integer approach in VBA. — Hans van Tigchelt, Jul 31 '18 at 10:09

score 2 · Accepted Answer · answered Jul 31 '18 at 10:11

Here is an example how to loop through years and weeks to generate the path:

Sub Test()
    Dim iYear As Long
    Dim iWeek As Long

    For iYear = 2015 To 2017
        For iWeek = 1 To 52
            Debug.Print "L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME " & iYear & "\Aanvragen ME week " & Format(iWeek, "00") & ".xlsx"
            'add your code here
        Next iWeek
    Next iYear
End Sub

You also might want to read: How to avoid using Select in Excel VBA.

Note that I used Format(iWeek, "00") to get the week number in a two digit format.

The result would be something like this:

L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 01.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 02.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 03.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 04.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 05.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 06.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 07.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 08.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 09.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 10.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 11.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2015\Aanvragen ME week 12.xlsx
…
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2017\Aanvragen ME week 50.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2017\Aanvragen ME week 51.xlsx
L:\Chemical_Mfg\Aanvragen CA\Aanvragen ME 2017\Aanvragen ME week 52.xlsx

Peh, thanks very much. It's working. I applied the same approach to the remaining code after finding the correct .xlsx file. Great learning here. — Hans van Tigchelt, Jul 31 '18 at 10:29

Loop through workbooks combining path and name

1 Answers1