CODEWITHSUNDEEP
vbaexcelloopsdirectory
Hans van Tigchelt
Hans van Tigchelt

Reputation: 35

Loop through workbooks combining path and name

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;

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.

Upvotes: 2

Views: 128

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 2

Related Questions