For Each Next Loops Nesting Problem

[andro02351]

I have created a series of workbook that each have several power query queries in them drawing data from other workbooks. The queries have been setup to use a parameter (a persons name) which is provided in a cell of a worksheet that will be hidden and then passport protected. I am trying to create a macro in a separate "creation workbook" (the Thisworkbook in the code). It uses a table like this.

[TABLE="width: 318"]
<tbody>[TR]
[TD]Budget Manager[/TD]
[TD]FileName[/TD]
[TD]Password[/TD]
[/TR]
[TR]
[TD]BM1[/TD]
[TD]BM110.xlsm[/TD]
[TD]M1_10_66[/TD]
[/TR]
[TR]
[TD]BM2[/TD]
[TD]BM220.xlsm[/TD]
[TD]M2_20_7[/TD]
[/TR]
[TR]
[TD]BM3[/TD]
[TD]BM330.xlsm[/TD]
[TD]M3_30_36[/TD]
[/TR]
[TR]
[TD]BM4[/TD]
[TD]BM440.xlsm[/TD]
[TD]M4_40_89[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to do these things:

1. Open a series of workbooks from a folder in turn which were already created and named (from a previous macro) per the table above. While each workbook is open.
2. Set the query parameter cell to the corresponding Budget Manager value from the table.
3. Hide the "Query Parameter worksheet and give it a password (the same password for every book)
4. Refresh the queries and pivots in the workbook.
5. Save and close the workbook (5a) assigning the corresponding password from the table to the workbook.

3 and 4 are not a problem, I have commented them out for now as it makes it easier for me to debug.
2 and 5a are the problem.

For the moment I am not attempting 5a as I think once I have 2. working then 5a can be made to work following the same method.

The problem is that I am able to open each workbook in turn (the outer loop) but the inner loop instead of running once for each iteration of the outer loop runs to the end. That is the first pass gives the first file the parameter BM1 but then the second file and every file after that is given parameter BM4, instead of the parameter corresponding to the filename that I need it to have (I suppose the first and last ones are right!).

Here is my stylized code (you will see that I have experimented with placing DIM statements and other elements in different places - with no luck so far)..

Public Sub Parameterise_Protect_MBBs ()
' Open each budget book created by Create_Budget_Books and then enters a Budget Manager as the query parameter, hides and password protects _
' the query parameter worksheet, refreshes all (which runs the queries and refreshes the pivots) and then saves and closes each book with a password

Const strSavePath As String = "\\Savepath"

Dim FNames As Range
Dim FN As Range
Dim wkbTemplate As ThisWorkbook
' Dim BMnames As Range
' Dim BM As Range

' Set BMnames = ThisWorkbook.Worksheets("BM's").Range("A2:A32").Cells
Set FNames = ThisWorkbook.Worksheets("BM's").Range("B2:B32").Cells

For Each FN In FNames
Workbooks.Open strSavePath & FN.Value
' ThisWorkbook.Activate
Dim BMnames As Range
Dim BM As Range
Set BMnames = ThisWorkbook.Worksheets("BM's").Range("A2:A32").Cells
For Each BM In BMnames
' Workbooks(FN.Value).Activate
Worksheets("Query Parameter").Range("B2") = BM.Value
' Exit For
Next BM
With ActiveWorkbook
.RefreshAll
' Sheets("Query Parameter").Visible = False
' Sheets("Query Parameter").Protect Password:="Password"
'.Password = "Password"
.Close Savechanges:=True
End With
Next FN
End Sub

 

[andro02351]

I have managed to solve my own problem. It turns out mainly that I was using the wrong type of for loop construct. Code that works and is I think also simpler to follow is,

Sub Parameterise_Protect_wbs()


Dim SD As Worksheet


r = 2


Const strSavePath As String = "savepath"


Set SD = Worksheets("Stylized Data")


Do Until SD.Range("A" & r) = ""


Workbooks.Open strSavePath & SD.Range("B" & r)
With ActiveWorkbook


.Worksheets("Query Parameter").Range("B2") = SD.Range("A" & r)
'.RefreshAll
.Sheets("Query Parameter").Visible = False
.Sheets("Query Parameter").Protect Password:="Password"
.Password = SD.Range("C" & r)
.Close Savechanges:=True
End With
r = r + 1
Loop
End Sub


RefreshAll is commented out as in my test workbooks there is nothing to refresh.

Would have been nice to get a pointer from someone and maybe I'd have got there quicker.