Reference workbook by part of path

[dcoledc]

I am having some trouble figuring out the best way to do something. I have 2 workbooks. Wb 1 has a macro to open Wb 2 and a macro to return to Wb 2 if one has already been open. Once in Wb 2, the user can save the file as a different name. They can also return to Wb 1.

My problem occurs when the user hits the return button in Wb 1 when there is no open Wb 2. My initial solution to this was to count the workbooks with:

If Workbooks.Count = 1 Then
    Workbooks.Open FileName:="C:\program files\reports to go\Forms.xls", UpdateLinks:=xlUpdateLinksAlways

Else

    Application.ScreenUpdating = False
    Range("d6").Select
    ReportsToGo.Save
    Windows("reports to go.xls").Visible = False

End If
End If
End Sub

This works fine until I already have another completely unrelated workbook open. Then the code counts that workbook as one and simply reveals it when I hide Wb 1. I hide Wb 1 to avoid referencing workbooks by name as the name may very.

First, I am open to any possible solution.
Second, my first thoughts were to either test other open workbooks for a sheetname (b/c that will be in all the workbooks I want open). This tells me that hiding Wb 1 will reveal the appropriate workbook.

I have changed the name of the workbook in the properties window from thisworkbook to Forms. This is unchanging regardless of the Filename. Is there someway to reference this from another workbook?

Or, is there someway to utilize part of a path to test. For example, can I test to see if other open workbooks are stored in the "patient files" folder and if so, then hide wb 1 to reveal wb 2?

Hope that made sense.

Any thoughts?

 

[dcoledc]

Thank you again for your continued help. How can I reference the new variable from Wb 1? Isn't that what I need to do to be able to activate it if it exists. My thoughts were in the return macro I would say something like:

If wb <> "" Then 
[INDENT]wb.Activate
[/INDENT]Else
[INDENT]MsgBox "There are no open reports"
[/INDENT]End If

If you can't, I have found a bit of a workaround. It is not perfect but it gets the job done.

 

[Andrew Poulsom]

More like:

If Not wb Is Nothing Then
    wb.Activate
Else

 

[dcoledc]

I still don't understand how you can reference a variable from a another workbook. When I set the variable I intentionally made it Wb so when I use it, excel will change wb to Wb. When I do this it does not change wb to Wb. This tells me that Wb 1 does not recognize the variable wb. Am I wrong about this?

 

[Andrew Poulsom]

You can't reference a variable from another workbook. If wb1 opens wb2 assign wb2 to an object variable when it's opened. To activate wb2 from a macro in wb1 use that object variable. If you are still stuck please post the code from both workbooks.

 

[dcoledc]

I finally get it and it works. Yippee! Man that was tough.

Thanks for sticking with me.

 

[dcoledc]

I spoke too soon. There does seem to be one gliche. First I have set the variable wb this way: Global wb as Workbook. Below are the two subs that open Wb 2 and return to Wb 2:

Sub NewReport()
On Error Resume Next
Set wb = Workbooks("Forms.xls")
On Error GoTo 0
If wb Is Nothing Then
    Set wb = Workbooks.Open("C:\program files\reports to go\Forms.xls", UpdateLinks:=xlUpdateLinksAlways)
    Windows("reports to go.xls").Visible = False
Else
    ActiveWindow.Visible = False
End If
 Application.ScreenUpdating = True
End Sub

and

Sub ReturnToReport()
If Not wb Is Nothing Then
    wb.Activate
Else
    MsgBox "No open report"
End If
End Sub

This work until I close Wb 2 from Wb2. Doing this reveal Wb 1. Then when I hit my NewReport sub it does it does not open a new workbook but instead does this line of code:

ActiveWindow.Visible = False

making Wb 1 hidden leaving nothing. Wb 2 does not open. I am guessing the variable is still active even though Wb 2 has been closed. Any idea on how to overcome this? Maybe a second variable somewhere?

 

[Andrew Poulsom]

You should be closing wb2 with code in wb1 and setting wb to Nothing when you do so. Try this:

Sub NewReport()
    Dim x As Variant
    On Error Resume Next
    x = wb.Name
    If Err <> 0 Then
        Err.Clear
        Set wb = Workbooks.Open("C:\program files\reports to go\Forms.xls", UpdateLinks:=xlUpdateLinksAlways)
        Windows("reports to go.xls").Visible = False
    Else
        ActiveWindow.Visible = False
    End If
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[dcoledc]

I have been playing with this and did something similar to what you have and on your solution and mine I get an error. Not at first but when I go back and forth. Let me explain, using your code for the NewReport and my posted code for the ReturnToReport, it all works fine to a point. If I open a Wb 2 using NewReport and then close it from within Wb 2, when I use my ReturnToReport sub I get an error. Instead of it displaying the msgbox "No report open", it tries to run the first line of code wb.activate and I get an error.

This coding did solve the last posts problem but seems to have created a new one.

I think somwhere I need to

Set wb= nothing

I am just not sure where.

Thoughts?

 

[Andrew Poulsom]

You need to avoid closing wb2 manually. Close it in code from wb1, setting wb to Nothing when you do so.

 

[dcoledc]

Could I set workbook.count to a variable when opening and then when it changes set wb=nothing?

Based on the setup of the program I don't think it would work to close from Wb1.