Find an Open Excel And Call Subs

[AnnAnn]

Hello,
I have searched but haven't found what I am looking for; appreciate any help given.
I need to create a macro in a workbook that will find an Excel workbook that is already open and where cell A1 contains the word 'Network' on the 2nde visible sheet. Once it finds that Excel, it needs to run other Subs, which I know how to create. I am stuck on how to find the open Excel workbook with the criteria mentioned.

 

[Micron]

Not sure what you mean by second visible sheet. It could be 2nd tab from the left with one or more hidden to the left of that. It might also mean the sheet that has an index value of 2. Anyway, this worked for me in my testing, but I did not worry if the sheet is "second" or not. I would think it doesn't matter if it is the only sheet with the word "Network" in a cell:

Sub getWBname()
Dim wb As Workbook
Dim sht As Worksheet

For Each wb In Application.Workbooks
    For Each sht In wb.Worksheets
        If sht.Visible Then
            If sht.Range("B1") = "Network" Then MsgBox wb.Name
        End If
    Next
Next

End Sub

I used B1 rather than alter my data.

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA to find the open Excel and Run Subs Once Found

I have searched but haven't found what I am looking for; appreciate any help given. I need to create a macro in a workbook that will find an Excel workbook that is already open and where cell A1 co...

stackoverflow.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[AnnAnn]

Not sure what you mean by second visible sheet. It could be 2nd tab from the left with one or more hidden to the left of that. It might also mean the sheet that has an index value of 2. Anyway, this worked for me in my testing, but I did not worry if the sheet is "second" or not. I would think it doesn't matter if it is the only sheet with the word "Network" in a cell:

Sub getWBname()
Dim wb As Workbook
Dim sht As Worksheet

For Each wb In Application.Workbooks
    For Each sht In wb.Worksheets
        If sht.Visible Then
            If sht.Range("B1") = "Network" Then MsgBox wb.Name
        End If
    Next
Next

End Sub

I used B1 rather than alter my data.

Thanks, Micron. I received the following error, Compile Error: Expected variable or procedure, not module.

 

[AnnAnn]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA to find the open Excel and Run Subs Once Found

I have searched but haven't found what I am looking for; appreciate any help given. I need to create a macro in a workbook that will find an Excel workbook that is already open and where cell A1 co...

stackoverflow.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Ahh, forgot, thank you for the reminder, RoryA.

 

[Micron]

Sorry, I'm out. I don't have time to follow 2 or more threads for the same issue, especially when neither is linked to.

 

[AnnAnn]

Sorry, I'm out. I don't have time to follow 2 or more threads for the same issue, especially when neither is linked to.

Ok. Not sure what you mean by 'especially when neither is linked to'. To what?

 

[Micron]

Ok. Not sure what you mean by 'especially when neither is linked to'. To what?

Cross posting without linking as RoryA said. Not done in either thread when I looked.