Switch between workbooks

Question

I have a code that searches for a certain information in another workbook, but that workbook changes names and formats. I am currently using workbook.open to open it, and when I specify the file location it has a dynamic part (you will understand on the sample code). The problem is that in the middle of the code I need to go back to the first workbook (the one running the code), do some stuff, then go back again to the workbook it searched the info on. I tried setting it as a variable using dim secondfile as string/workbook (tried both) and then tried to set it as the active workbook when it was "active", so I could use "secondfile.activate" later on when I needed to go back to it, but had no luck. Here is the code:

dim originalworkbook as workbook
set originalworkbook as this workbook
dim wb as workbook
set wb as Workbooks.Open("C:\Users\abc\Documents\bla bla bla " + Range("D6").Offset(iLoop).Value)
'code here...
originalworkbook.activate
'code here...
'[insert code here to go back to the wb workbook]

I cannot tell it to open the wb workbook again because it would not make sense. Right now I have list with 1k+ data that is searched in another more than 30 files. The name of the file is on the first workbook, at least part of it, as you can see in the code. I use the "Range("D6").offset" part to get it. The thing is, it would take too long to run the code if I closed and opened the second workbook everytime, so what I am trying to do is to search for all things that are in a specific file and after searching it all, saving and closing it. That is why I need to get back to this file...

PS: not all files are in the same format, thats why I didn't use "workbooks.activate" or something like it and then use the same "Range("D6").offset" thing to set the file to activate, because some are in .xls and others are in xlsx...

Say you dimmed wb1 as workbook and wb2 as workbook. Are you using the following syntax? wb1.sheets("Sheet1").Range("A1") = "aaa" , or wb2.Sheets("Sheet3").Range("B12") = MyString. What I'm saying is you don't have to activate it to change the cells or read from them. — David G, Aug 25 '15 at 16:03
http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Tim Williams, Aug 25 '15 at 16:08
Thanks @TimWilliams, but I think there is no other way then to use active sheet or something like it... You already helped me in this macro before, the sheet and workbook names changes everytime, see http://stackoverflow.com/questions/32127050/vba-find-not-working — Ricardo Milhomem, Aug 25 '15 at 16:20
@DavidG maybe I got the idea. You are saying that I don't need to go back to the first workbook to change a cell on it, right? I will try it now and return to you — Ricardo Milhomem, Aug 25 '15 at 16:22
@DavidG tried changing my string, didnt work. I tried defining two variables, one for workbooks (wb) and one for worksheets (ws), and tried to define them as thisworkbook/activeworkbook and activesheet/sheet ("name"), then tried using wb.ws.range.offset.value = "xxxx", also tried wb. sheets (ws) and then the rest, no luck. got type mismatch on this string — Ricardo Milhomem, Aug 25 '15 at 17:21

score 1 · Answer 1 · answered Dec 15 '16 at 10:55

Public w as workbook    
For Each w In Workbooks
        If w.Name = originalworkbook Then
            Workbooks(w.Name).Activate
            'Do Stuff
        Exit For
        End If
Next w

The beauty of the above is that you can use this for infinite number of excel files open at the same time. just change If w.Name = original workbook then to if w.Name like "*charcters from a file name*" then

The wild cards allow you to search efficiently

Hope this helps!

David G · Accepted Answer · 2015-08-25T18:31:32.263

Since your code is really not very descriptive I'm giving you a piece of mine and I'll try to explain what's going on. I am writing in another workbook with this code, putting information from the first. It's a long sub and I will remove unnecessary parts.

I tried to cover all situations you would encounter.

sub TemplateFiller(AHNSnumber as String)
    Dim wbk As Workbook
    Dim wbpath As String
    Dim lastline as long
    Dim wbName as string

    wbpath = "O:\08_Lean_Eng\10_On_going\David\Soldier's Pond\MDR\Templates\TemplateCustom.xls"

    'This is to check if it's open first. Only open it if it is closed!
    'IsWorkBookOpen is one of my own functions, not included here
    If IsWorkBookOpen(wbpath) = False Then
        Set wbk = Workbooks.Open(wbpath) 'Notice: set wbk =, not Set wbk as
    ElseIf IsWorkBookOpen(wbpath) = True Then
        wbName = "somethingworkbook.xlsb"
        Set wbk = Workbooks(wbName)
    End If

    'Finding the last line in the other wbk (I'm using info from the other wbk for variables here)
    lastline = wbk.Sheets("DL001").Range("BG65000").End(xlUp).row + 1

    wbk.Sheets("Dl001").Range("F" & lastline) = AHNSnumber 'Using a passed variable
    wbk.Sheets("Dl001").Range("G" & lastline) = "OK" 'Writing some stuff
    wbk.Sheets("Dl001").Range("H" & lastline) = ThisWorkbook.Sheets("a").Range("A1") 'Value from one to the other and vice versa
    Thisworkbook.sheets("b").Range("A2") = wbk.Sheets("Dl001").Range("A" & lastline)


    'Changing a variable in this workbook
    ThisWorkbook.Sheets("Data").Range("Revision") = ThisWorkbook.Sheets("Data").Range("Revision") + 1

End sub

'I am not the original writer of this function.
Function IsWorkBookOpen(filename As String) As Boolean
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open filename For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

As you can see I don't even make use of ActiveWorkbook or Workbook activate. — David G, Aug 25 '15 at 17:45
Thats almost the same thing I am doing, and if you may send the isworkbookopen part I would appreciate a lot. I just didn't understand one thing: wbname is a variable? What did you define it to? also, the difference in my code is that you are using a string to change the range in the last line, I am using a value, it is actually "Yes". Tried deleting the .value part in the end of it but still get subscript out of range... — Ricardo Milhomem, Aug 25 '15 at 18:04
wbpath is supposed to be wbname. I will fix it. Also, this code works, so if you have errors, they are not because of your workbook references. I can see in your code [set wb as Workbooks.Open], so are you sure you're doing the same thing? I'll add more variations so you get an idea as well. — David G, Aug 25 '15 at 18:28
I'm sorry. When setting the workbook, and it is open in the same application, it has to be the name only (not the path). I added that in. In removing some things i confused myself and oversimplified it! — David G, Aug 25 '15 at 18:33
David, tried to run the code again. Still have problems. The thing is: the "wbpath" variable in my case varies inside a loop, and I can tell what the name of the file is but I cannot tell what is it extension, I have XLS and XLSX. I can solve this by using a list or something, easy, but I am still having problems in another part of the code. The string "this workbook.sheets ("sheet name").range ("k6") .offset (loop) = "no"" returns a subscript out of range, error 9. — Ricardo Milhomem, Aug 26 '15 at 01:12
That means your loop is going too far. You should debug with F8 instead of running the whole thing in 1 shot and see where it exits. As for the extension, I was never confronted with this problem. Maybe you could try both xls and xlsx one after the other? Using On error resume next so it doesnt crash if it fails? I'm not sure what to do. — David G, Aug 26 '15 at 12:43

Switch between workbooks

2 Answers2