VBA - Switching between Workbooks

[jhol4]

Hi Guys,

I posted a question earlier about using a RefEdit to link to external workbooks, however it seems that this cannot be done.

So my new question is does anyone have a good way of alternating between workbooks using VBA where the name of the workbook is likely to change regularly.

I usually use Windows(filename.xls).activate, however if I don't know what the filename is, this becomes difficult.

Thanks in advance.

 

[Tom Urtis]

No problem...right now I'm out at the auto repair shop getting my car's oil changed (too much info there, but what the hell), and will send you the full workbook in a couple hours.

 

[Hallaa]

I got a chuckle out of that last post! Thanks, Tom!

 

[Tom Urtis]

OK, all the car details are done: oil changed, tires rotated and inflated, brakes checked, and gas tank filled. During all that, I deleted your email. I have the workbook done but first I ask that you send me an email again so I can reply with the finished workbook, which all works no problem.

 

[nitesh123]

Thanks Tom! This worked like a charm

 

[3t3rnal]

Hi all, I've modified a code by Tom given at the start to allow the user to determine which open unknown workbook to activate however I'm not sure what's wrong with this code. Can anyone help me take a look at it pls? i'm not sure why I am unable to put "next wb" within the while loop. What i'm trying to do here is to loop through all the open workbooks and displaying their file names to the user so that they can determine which workbook to activate. Very much appreciated guys!
Sub Test1()
Dim wb As Workbook, x As String
For Each wb In Workbooks
While wb.Name <> ThisWorkbook.Name
x = wb.Name
response = MsgBox("The other open workbook is named " & x & ".", vbYesNo)
If response = vbYes Then
Workbooks(x).Activate
End If
Next wb
Wend
End Sub

 

[Agent1]

Hi Tom. I see this is a very old thread. I have a related issue to this thread and I have no luck in finding the right answer. I have two open work books, I am currently working on the 2nd workbook, how do I insert into a code to activate the previous workbook? Here is my code:

Sub add_hyperlink2()
Workbooks("Chart1").Sheets("Sheet1").Activate
ActiveCell.Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="..\Desktop\2014-02-20 order-no.465-Product.xlsm", TextToDisplay:="2014-02-20 order-no.465-Product.xlsm"
End Sub

Thanks

 

[Tom Urtis]

Thanks for doing your search on this topic.

If you are only seeing one workbook name, and that name is the workbook you are using, could it be that you are opening other workbooks in other instances of Excel? That is my first thought. It is possible with API to identify workbooks open in other instances but the code is rather complicated and would not be necessary if you (or the project you are working with) have the capacity to only employ one single Excel instance. Please post back with what you are working with in terms of instance counts and we can take it from there.

 

[Agent1]

Thanks for doing your search on this topic.

Hi. I don't want to complicate the issue too much but here is the complete code and objective that i'm working with:

Dim strText As String
Dim var

strText = Mid(ActiveWorkbook.Name, 15)
strText = Left(strText, Len(strText) - 5)
var = Split(strText, " ")

With Workbooks("Chart1").Sheets("Sheet1").Activate
    ActiveCell.Activate
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
    Address:=ActiveWorkbook.FullName, TextToDisplay:=var(0) & " " & var(1)
End With

I have two workbooks open, one is always going to be called "Chart1" (since it's my main database where I keep track of my customers orders) and the second workbook is the customer order (which will always have a different filename with every order I process into "Chart1"). What the code above is supposed to do is to insert the customer order file into the "Chart1" workbook as a hyperlink into a preselected cell. The problem with the code is that it's hyperlinking "Chart1" into "Chart1"! (The code is doing at least one good thing, it's naming the hyperlink as the customer's file and not as "Chart1") I need the code to hyperlink the customer's file. If it helps here is an example of a customer file : "2104-02-21 No.653965 Product.xlsm" Thank you

 

[Tom Urtis]

Is it that you would only and always have two workbooks open, one called Chart1 (where I presume the macro resides) and another workbook of whatever name it might be.

Also, it is not clear to me how the sheet name and cel address of this other workbook is determined, in order to know where the hyperlink should lead to.

I'm not faulting you publicly or anything like that, but want to tell you that your With structure is meaningless in terms of no code lines that are connected to the With statement. All that's needed is the location (sheet name and cell address) of the link destination. Is it teh active cell at the time of macro execution, or maybe the next available cell in some column on some sheet, or is it always the same sheet and cell...hwo is this determined.

 

[Agent1]

Hi. I usually have more than two workbooks opened at the same time.
I hear and understand regarding the unnecessary use of the word 'with', thanks for pointing it out to me. Allow me to lay out every detail and maybe there could be a different route entirely that I can take to get to my goal. I will first introduce you to a system I created at my work that allows me to process orders efficiently with out mistakes. I have a file called 'customer database' where there are two tabs, one tab is a sales order template and the other tab is a list of all my customers with all their relevant information including there file path typed out. The sales order is linked to the customer information through Vlookup. I have a macro that saves the sales order as a copy into the customers respective folder and then returns my 'customer database' to its original blank sales order template. With so many orders I can lose track of orders and priorities therefore I have another chart that is always open and I manually hyperlink all the sales orders, and any other relevant files of the customer, to that chart. We will name this chart 'Chart1'. Now, my goal is to have a macro added to my 'customer database' (and not 'Chart1') that at the same time when it saves the sales order file into the customers file it also automatically hyperlinks the file into a pre-selected cell in 'charts1'. So for a recap: 1) I open workbooks 'Chart1' and 'customer database' 2) I then enter the customer's name into a cell that updates the 'sales order template' tab under 'customer database' with all the customers information i.e. shipping address, billing address etc. using the function Vlookup which looks up data in the next tab called 'customer information' 3) Next, I hit a macro that saves the template into the customer's file to my network using the customer's file path which is located in 'customer information' tab. *the file path is written out in the 'customer info' tab and is also updated to the template using Vlookup. 4) I would like to hyperlink the saved file to 'Chart1'. Please share with me any brainstorm you would have to get to my goal other than the way I mentioned Thank you for your time