Reference workbook by part of path

dcoledc

Active Member
Joined
May 6, 2010
Messages
403
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:

Code:
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

dcoledc

Active Member
Joined
May 6, 2010
Messages
403
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It would be:

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

dcoledc

Active Member
Joined
May 6, 2010
Messages
403

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In that case use an object variable to keep track of the workbook. Example:

Code:
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

Active Member
Joined
May 6, 2010
Messages
403

ADVERTISEMENT

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:

Code:
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.
 

dcoledc

Active Member
Joined
May 6, 2010
Messages
403
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,271
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top