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?

 

[Andrew Poulsom]

Use the WorkBookIsOpen function from here:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

You shouldn't change rename ThisWorkbook.

 

[dcoledc]

Hello and thank you for your response. I have looked at your suggestion and am not quite sure how to implement. My knowledge of functions is little to none however I assume I have to crate a Sub procedure that references the variables created in the function. If this is correct, I am not sure how to do this effectively.

Please keep in mind, I won't necessarily know the name of Wb 2. I want to test if any OTHER open workbook could be the Wb 2 I am looking for and if so open. All I will know for certain about Wb 2 is that it was made from Wb 2 and simple has a different name so all the macros, sheets, etc will be the same.

Any thoughts?

 

[Andrew Poulsom]

It would be:

If Not WorkbookIsOpen("Forms.xls") Then
    Workbooks.Open FileName:="C:\program files\reports to go\Forms.xls", UpdateLinks:=xlUpdateLinksAlways

 

[dcoledc]

Hello and thanks again, that works to a point. If the user does not save "Forms.xls"(Wb 2) and goes back to Wb 1 and then returns to Wb2 ("Forms.xls") it works perfectly. However, if the user saves Wb 2 prior to going to Wb 1 then it doesn't work. The reason is that when they save, VBA automatically changes the name to something else (ex. "Cole, Dan.xls") to prevent the original "Forms.xls" from being written over. So when the code searches for "Forms.xls" it doesn't find it and opens a new one. Upon returning to Wb 2 position, the file the user was working on,"Cole, Dan.xls", is now hidden by a new "Forms.xls" file.

Any thoughts?

 

[Andrew Poulsom]

In that case use an object variable to keep track of the workbook. Example:

Dim wb As Workbook
If wb Is Nothing Then
    Set wb = Workbooks.Open(FileName:="C:\program files\reports to go\Forms.xls", UpdateLinks:=xlUpdateLinksAlways)
Else
'Whatever
End If

 

[dcoledc]

I am not sure I understand in that I didn't think I could reference a variable from a different workbook. If I understand your suggestion, I should set Wb 2, whatever it may be called, to a variable, wb, perhaps when it deactivates. When I try to return to wb from Wb 1, if wb exists then hide Wb1 else open "Forms.xls". Is my understanding correct?

The code I have in Wb 1, not including the function is as follows:

Sub SaveOfcInfo()
Dim wb As Workbook
If wb is Nothing Then
[INDENT]Set wb = Workbooks.Open FileName:="C:\program files\reports to go\Forms.xls", UpdateLinks:=xlUpdateLinksAlways[/INDENT]Else
    Application.ScreenUpdating = False
    Range("d6").Select
    ReportsToGo.Save
    Windows("reports to go.xls").Visible = False
      
    Application.ScreenUpdating = True
End If
End Sub

Is that what you meant? How do I set Wb 2 to the variable so that this code will look for it?

Help.

 

[Andrew Poulsom]

Isn't Forms.xls Wb2?

 

[dcoledc]

It starts as "Forms.xls" but within Wb 2, if the user hits a macro it will save it under a different name. Once saved as a different name they can still go back to Wb 1. Now trying to return to Wb2 doesn't work right b/c it is not called "Forms.xls" anymore.

 

[Andrew Poulsom]

Once you have the workbook in an object variable it will remain there until the workbook is closed. It doesn't matter that the workbook has been saved with a different name. Declare the variable as public at the top of a module and assign it when the workbook is opened. Then you can use:

wb.Activate

to return to it.