Active the workbook with an identifiable string in the file name

Brian F · Apr 21, 2013

Windows 8, Excel 2010;

The short description; Among several open workbooks, I want to activate the one with the string "Attention Item Report" in the file name.

The long description; I am trying to paste values from one workbook to another. I know how to identify the information I want to copy and I know how to tell the vba routine where to paste it in the destination workbook. The problem is identifying which of several open workbooks I want paste the information to. There will be several open workbooks (let’s say 10). Nine of them will be a source of the information and one of them is the destination. The destination workbook always has a name similar to FANnc12StraB, Buena Village Attention Item Report 2013-4(Apr)-21. The only thing that always appears in the name of the destination workbook is the string "Attention Item Report" (and that string never occurs in any of the names of the source works). So, the string "Attention Item Report" makes that name different from the rest. I will manually activate the macro in each source workbook one at a time that will identify the destination workbook and perform the cut and paste operation (i.e. I don't need to cycle through the source workbooks). I need to know how to say "From all of these open workbooks, activate the one with the string "Attention Item Report" in the file name".

Can anyone help me with this?

mikerickson · Apr 21, 2013

A loop like this will find the workbook you seek

Code:

Dim ReportWorkbook As Workbook
'preceding code

    For Each ReportWorkbook in Application.Workbooks
        If ReportWorkbook.Name Like "*Attention Item Report*" Then Goto foundWB
    Next ReportWorkbook
    Set ReportWorkbook = Nothing
foundWB:
' rest of code

Brian F · Apr 21, 2013

I opened several documents, only one of which has the phrase "Attention Item Report" in the file name. I inserted a module in one of the source documents with your code and I added the line "Application.Goto Range("AA11"), True" at the end of the code. When I step through the code, it never leaves the source document. It ends up going to AA11 in the source doc rather than in the destination doc. Any ideas?

Brian F · Apr 21, 2013

Oh, I added a couple of lines to tell it to do something on destination workbook and it did it. I suppose I just didn't understand what it should have looked like when the destination source was chosen.

Sub Go_To_AIR()
Dim ReportWorkbook As Workbook
'preceding code
For Each ReportWorkbook In Application.Workbooks
If ReportWorkbook.Name Like "*Attention Item Report*" Then GoTo foundWB
Next ReportWorkbook
ActiveWorkbook.Sheets("Checklist").Activate
Set ReportWorkbook = Nothing
foundWB:
Application.Goto Range("N5"), True
End Sub

Thank you so much for your help!

mikerickson · Apr 21, 2013

My code finds the desired workbook and assigns it to the variable ReportWorkbook.
Depending on which sheet of the found workbook you want, you might add

Code:

If ReportWorkbook Is Nothing Then
    MsgBox "Action Item workbook not present"
    End
Else
    Application.Goto ReportWorkbook.Sheets(1).Range("AA11")
End If

Brian F · Apr 22, 2013

Thank you again.

I found that I needed to put the phrase "ReportWorkbook.Activate" immediately after your code to be able to manipulate the workbook. Without that code, I was still in the original workbook.

Your response anticipated my next question, "suppose the desired workbook is not there?" Now I don't have to ask. Thank you.

Brian F · Apr 22, 2013

After playing with this new code you gave me, I've determined that what I actually want to do is have the message say "open the missing Workbook" (which I've done) but then it needs to go back and look for the workbook I've just opened so it can find it and associate it with the name "ReportWorkbook" which I can't seem to manage.

mikerickson · Apr 22, 2013

Open returns a workbook object

Code:

If ReportWorkbook Is Nothing Then
    MsgBox "will open the book"
    Set ReportWorkbook = Workbooks.Open(path to workbook)
Else
    Application.Goto ReportWorkbook.Sheets(1).Range("AA11")
End If

BTW, one rarely needs to Activate a workbook in order to work with it.

Brian F · Apr 22, 2013

I understand what it is to specify the path to the document but in this case the path to the workbook is too difficult to specify. It changes all the time depending on what I'm working with. I've decided I need to open the proper document using my wetware. So, after the prompt, I will manually open the workbook (which is fine). Having opened the workbook, is there a way to have the routine go through a fresh search to identify the proper workbook among the various open workbooks. If it is too difficult I will have to train my self to remember to have it open before firing the macro.

I don't know why, but in this case the workbook wasn't active until I told it to activate. I'm not knowledgeable enough to venture an opinion as to why.

Brian F · Apr 23, 2013

It occurred to me that if before your code of;

For Each ReportWorkbook in Application.Workbooks If ReportWorkbook.Name Like "*Attention Item Report*" Then Goto foundWB Next ReportWorkbook Set ReportWorkbook = Nothing foundWB:

I included code that said;

For Each ReportWorkbook in Application.Workbook If ReportWorkbook.Name Like "*Attention Item Report*" Then (Go on with the routine, skip to after the message) Next ReportWorkbook Elseif ReportWorkbook Is Nothing Then MsgBox "open the book"

I would have an opertunity to open the workbook before everything shuts down. Could you help me make that statement work? </pre>

Active the workbook with an identifiable string in the file name

Brian F

Active Member

Excel Facts

mikerickson

MrExcel MVP

Brian F

Active Member

Brian F

Active Member

mikerickson

MrExcel MVP

Brian F

Active Member

Brian F

Active Member

mikerickson

MrExcel MVP

Brian F

Active Member

Brian F

Active Member

Similar threads

Forum statistics

Share this page

Active the workbook with an identifiable string in the file name

Active Member

Excel Facts

MrExcel MVP

Active Member

Active Member

MrExcel MVP

Active Member

Active Member

MrExcel MVP

Active Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock