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]

Trying to understand everything at play here.

First, sorry to sound pedantic, not meaning to, but this you wrote...
"copy the last worksheet from a file and copy as the last sheet in another file"
...can be interpreted two ways:
(a) You want to copy the last worksheet in the source workbook and replace whatever the last worksheet is in the destination workbook with that copied worksheet.
or
(b) You want to copy the last worksheet in the source workbook and copy it (thereby adding a new worksheet) to the destination workbook.

Second, the name of the last worksheet (which changes daily) is not a big deal. But what I don't understand is how you determine which workbook is the source and which is the destination, other than opening up both and you knowing which is which. In other words, because those workbooks' names often change, from a programming standpoint, there needs to be a reliable procedure to identify the workbooks' status as being the source or destination. And, with cases like this that I've seen over the years, chances are pretty good that the folder path(s) of the workbook(s) will eventually change too.

All is not lost, there are ways around these scenarios but each way has its own programming approach, and hence amount of code.

So can you give more info, thinking of worst case scenario, of how complicated this whole thing can be. Then a solution can be posted that fits how complicated (or not) the situation is today, and might end up being in the future.

 

[TGarrett]

Tom,

Thanks for your reply. I'll try to explain things a little better. I am using Excel 2007 and am writing a macro that will copy the last worksheet from a file (source) and add it as the last sheet in another file (target), as in scenario b of your post. “Source” is an .xls file that contains the daily schedule and a new sheet is added each day and named as that date. The name of the file is something like “Schedule October 2015”. A new file is created at the beginning of each month, thus the month and year will change in the file name, and keeps a running tally of sheets for the month. I need to add the daily schedule to “target”, which is an .xlsm, each day and then run several other macros to reformat it and prepare it for printing. “Target” has a similar naming convention, with rolling month and year, and is the file from which the macro is running. I have the folder architecture set up so that each day, “source” is downloaded into a dedicated temp folder. Part of the macro is to clear out the temp folder after the day’s schedule is copied to “target”. Also, “target” is kept in its own dedicated current schedule folder. At the end of each month, the current “target” is archived in a different folder and the new month’s “target” is created. With the code below, I have gotten as far as being able to call up “source” using wildcards and select and copy the needed worksheet, but that is where there is a glitch. When I run the macro, I get Run-time Error 1004: Copy method of Worksheet class failed with “Sheets(Sheets.Count).Copy After:=workbooks(target.Name).Sheets.Count” highlighted.

If I omit “After:=workbooks(target.Name).Sheets.Count”, the macro runs fine with the exception that the sheet is copied into a new workbook. I need it to copy into “target”. I know I must be close, just can’t figure out what I’m missing.


Sub GetData()

Dim sFound As String
Dim target As Workbook
Dim source As Workbook

Set target = ThisWorkbook
ChDir “C:\documents\Temp Schedule”
sfound = Dir(“C:\documents\Temp Schedule\Schedule*.xls”)
If sFound <> “” Then
Workbooks.Open “C:\documents” & “\” & sFound
End If
Set source = ActiveWorkbook
Sheets(Sheets.Count).Copy After:=workbooks(target.Name).Sheets.Count
Windows(source.name).Close
Kill “C:\documents\Temp Schedule\Schedule*.xls”

End Sub

Thanks for the help.

Tom

 

[TPMan]

This question came up earlier today. This macro would go into the first workbook, which allow you to find out the name of, and activate, the "other workbook".

Sub Test1()
Dim wb As Workbook, x As String
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then x = wb.Name
Next wb
MsgBox "The other open workbook is named " & x & "." & vbCrLf & _
"Click OK to activate it."
Workbooks(x).Activate
End Sub

Because of you Tom, I have just joined this forum. I have been seeking to find the workbook name prior to running a selection of macro's stored in the "personal" workbook. I have to say I have searched for this solution for many hours. I removed the message box prompt and now it is perfect for my needs. Thank you ever so much. You have made my day

 

[Tom Urtis]

Because of you Tom, I have just joined this forum. I have been seeking to find the workbook name prior to running a selection of macro's stored in the "personal" workbook. I have to say I have searched for this solution for many hours. I removed the message box prompt and now it is perfect for my needs. Thank you ever so much. You have made my day

Wow, what a nice message. Thanks so much!

 

[sarahb76]

What do I think, you ask. I think I should have tested that more carefully.

Please substitute this macro, just take it out back and shoot it:

Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, i As Integer
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For i = 1 To Workbooks.Count
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbActivayte"
End With
Next i
End Sub

and put this one in its place:

Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, wb As Workbook
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For Each wb In Workbooks
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbActivayte"
End With
Next wb
End Sub

Then save the workbook and open a few other workbooks, go back to that subject workbook, right click on any worksheet cell, and it should be OK. Definitley let me know if you run into problems.

Tom - I'm jumping on the 10 year old bandwagon as I've found this thread incredibly helpful - and I too registered as a user SPECIFICALLY because of your shared wisdom in this thread! Thank you, thank you, thank you!

I'm curious though - would it be possible to have the right-click menu only work on a specific worksheet or a specific range of cells (instead of the entire workbook)?

 

[Tom Urtis]

Thank you so much for your kind words. How nice that this thread has benefited yourself and others.

To answer your question, yes, you can limit the custom right-click menu to a specific sheet and, if desired, a specific range within which the active cell resides.

For example, suppose you want the custom right-click menu to kick in when Sheet2 is active. And further suppose your range of interest is C5:D10. The following events all go into the workbook module, in place of the event code that was posted previously in this thread. The other 3 original macros (RightKlickMenuReeset, RightKlickMenuMayker, wbActivayte) all stay the same, no changes with them.

Notice the 2 lines that are bolded which manage the sheet and range specifications.

When I develop projects for others, I almost never refer to sheets as their tab name, as I show here, but rather as their codename that you see in the Project window of the VBE. This is because worksheet tab names can change but a worksheet codename will not be changeable unless someone with access to the VBE changes the codename, or deletes the sheet of interest and re-creates the sheet with the same look and feel but with a different codename. So for example this line that depends on the tab name...
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
...would look like this if it depended on the worksheet object's codename and not the tab name.
If ActiveSheet.CodeName <> "Sheet2" Then Exit Sub


Private Sub Workbook_Open()
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
If Intersect(ActiveCell, Range("C5:D10")) Is Nothing Then Exit Sub
Run "RightKlickMenuMayker"
MsgBox "Right click any worksheet cell for a list of open workbooks" & vbCrLf & _
"that you can click to select and immediately navigate to.", 64, "Navigation tip"
End Sub

Private Sub Workbook_Activate()
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
If Intersect(ActiveCell, Range("C5:D10")) Is Nothing Then Exit Sub
Run "RightKlickMenuMayker"
End Sub

Private Sub Workbook_Deactivate()
Run "RightKlickMenuReeset"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "RightKlickMenuReeset"
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
If Intersect(ActiveCell, Range("C5:D10")) Is Nothing Then Exit Sub
If Workbooks.Count = 1 Then Exit Sub
Cancel = True
Application.CommandBars("wbNavigator").ShowPopup
End Sub

 

[sarahb76]

Tom - Thank you, thank you, thank you!!! This is EXACTLY what I needed!