Find name of external open workbook using VBA

[9tanstaafl9]

I need to copy data from an open workbook for which I do not know the name, into my workbook that contains the macro that finds this information. I need to know how to get the name of the workbook with the data. Is this possible? I've searched the Internet for two days now and can't find it. I'm afraid it is just such an easy question that no one has felt the need to post it. If it is simply impossible, I would like to know that too.


I could, for example, ask the user to click somewhere on the target workbook if that would help.I tried doing something similar, but only managed to get the so address where the user clicks. Not the workbook name or path.
I can't just use index numbers, because it is possible the user might have other workbooks open, and also I don't know what order they would open them in. Also I cannot edit or make changes to the unknown workbook..


Any help or ideas would be appreciated

 

[jasonb75]

I can't just use index numbers, because it is possible the user might have other workbooks open, and also I don't know what order they would open them in.

Taking your comments into consideration, is there any way to identify the correct 'unknown' workbook without prompting the user to click?

 

[9tanstaafl9]

Taking your comments into consideration, is there any way to identify the correct 'unknown' workbook without prompting the user to click?

That is what I'm asking. I am self taught, and there are quite a few gaps in my knowledge. Clicking was just the only way I thought of that might help. I have never had to reference an external workbook while creating a macro. They have always been self-contained.Obviously, the user can see the name of the workbook. However, Their workbook names are very long and include the date. I think it would be extremely unlikely that they would be able to enter the name correctly if I ask them for it. I am looking for something relatively idiot-proof. It doesn't have to be automatic, just simple.

 

[jasonb75]

See if this does what you need,

Sub test()
Dim wb As Workbook
For Each wb In Application.Workbooks
Select Case MsgBox(wb.Name, vbYesNoCancel, "Use this Workbook?")
    Case vbYes
        Exit For
    Case vbCancel
        Exit Sub
    End Select
Next

With wb
    ' do some stuff
End With
End Sub

It might fail if the user has multiple instances of the excel application open, but that doesn't seem possible to do in windows 8 so I can't test the theory.

 

[9tanstaafl9]

Brilliant idea! I'm pretty sure that will do exactly what I want. I will try that as soon as I get home.

 

[9tanstaafl9]

See if this does what you need,

Sub test()
Dim wb As Workbook
For Each wb In Application.Workbooks
Select Case MsgBox(wb.Name, vbYesNoCancel, "Use this Workbook?")
    Case vbYes
        Exit For
    Case vbCancel
        Exit Sub
    End Select
Next

With wb
    ' do some stuff
End With
End Sub

.

I'm not sure if it did what it was supposed to do or not. It DID pop up the window and let me say YES on the correct workbook, then when stepping through the code it went right to the 'do some stuff. section. But I'm not sure if it didn't work correctly or if I'm just doing something wrong because I can't seem to copy data from the workbook.


I tried this:

With wb.Worksheets("Budget")
Range(Range("B14"), Range("B14").End(xlDown)).Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + 11).Copy

I didn't get an error, but it made the selections on my Macro workbook, not the wb we just said yes to on the popup menu. And yes I know I'm supposed to get rid of the selects, but I have to do that after I know it's working correctly, that way I can see things happening. I don't know how to do this particular thing avoiding all selects.

Copying this data is the only thing I need to with the unknown name workbook. I just need to copy it and paste it onto the "WorkingSheet" tab of my workbook with the macro.

 

[jasonb75]

The code doesn't Select the workbook, it just grabs the name of it, you need to prefix Range with a dot / period so that the code knows to work 'With' wb.Worksheets("Budget") otherwise the code defaults to the Selected sheet in the workbook with the code.

It should be

With wb.Worksheets("Budget")
    .Range(.Range("B14"), .Range("B14").End(xlDown)).Select

As you only need to copy the data you should be able to condense this to one line and do away with all of the With / End With / Select code.

Try this code in a new blank workbook, I've set it to paste the copied data to Sheet1 A1, if it works then it's simply a matter of changing the paste destination range.

Sub test()
Dim wb As Workbook
For Each wb In Application.Workbooks
Select Case MsgBox(wb.Name, vbYesNoCancel, "Use this Workbook?")
    Case vbYes
        Exit For
    Case vbCancel
        Exit Sub
    End Select
Next
    wb.Sheets("Budget").Range("B14:L14").End(xlDown).Copy Sheets("Sheet1").Range("A1")
End Sub

FWIW I'm self taught as well, there may be better ways.

 

[9tanstaafl9]

THANK YOU. It worked! I had to tweak the last line so that it selected all my data and not just the last line, but otherwise it was perfect.

My new last line in case anyone needs to know is now:

wb.Sheets("Budget").Range(wb.Sheets("Budget").Range("B14:M14"), wb.Sheets("Budget").Range("B14:M14").End(xlDown)).Copy Sheets("WorkingSheet").Range("B1")

And yes I know I should be able to shorten this using WITH but I'm not very good at that yet and it's Sunday and this works so I'm leaving it alone.

Thank you again for your help!!!!!!

 

[jasonb75]

...and it's Sunday and this works so I'm leaving it alone.

I like that theory, but it's Monday now, at least for me anyway, so

I just looked back at my last post, I think that the version I used there would just copy the last row, not the whole table.

Anyway, with With

With wb.Sheets("Budget")
    .Range(.Range("B14:M14"), .Range("B14:M14").End(xlDown)).Copy Sheets("WorkingSheet").Range("B1")
End With

without With (slightly different version, which I haven't tested)

wb.Sheets("Budget").Range("B14", wb.Sheets("Budget").Range("M14").End(xlDown)).Copy Sheets("WorkingSheet").Range("B1")

I wonder how long t will take before somebody breaks the idiot proofing.

 

[9tanstaafl9]

Thank you! If I can bother you one last time, I get an error message if the user screws up and doesn't make any selection at all. How do I correct that? I tried testing for wb.name = "" but I just got an error message about object variables not being set.