Using data from another workbook (other than the active one)

Question

Ignoring what my code actually does (it's not important to my question):

I want to be able to open my excel file, press a button, have the code use data in that workbook and another opened workbook (so I would have two workbooks opened at the same time, the macro runs in one of them but can take data from both of them).

The trick here is that I can't seem to find code to access the other workbook that I've opened up, so I can only take info from the active workbook.

For example,

Private Function GetLastRow() As Integer

Dim myLastRow As Integer

Set ws = ThisWorkbook.Sheets("Sheet1")

myLastRow = Range("C" & Rows.count).End(xlUp).Row

GetLastRow = myLastRow

End Function

This code lets me access the active workbook (the one running the code), using ThisWorkbook.

Is there another function capable of allowing me to access another opened workbook?

Yes - I suggest reading up on using workbooks as variables. [This page should help](https://excelmacromastery.com/excel-vba-workbook/), or any of [these results](https://www.google.com/search?q=vba+refer+to+open+workbook) should get you going. You're getting there, with the `Set ws = ...` line. However, when you call a naked `Range()`, that runs on whatever the ActiveSheet is. If you want to make sure that the `Range()` refers to a range on the `Sheet1` worksheet, then simply do `myLastRow = ws.Range("C" & rows.count).End(xlUp).Row` — BruceWayne, Feb 04 '19 at 18:58
@BruceWayne thanks for the link I was able to find a useful feature, 'ActiveWorkbook'. But I don't think this ever reads an active book other than the one I click the button in (so it would never access the other workbook I want to draw data from). — Gardenia, Feb 04 '19 at 19:14
Note that row counting variables **must** be of type `Long` because Excel has more rows than `Integer` can handle. So both, your function *and* your variable must be declared `As Long`. • I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. — Pᴇʜ, Feb 05 '19 at 07:09

score 1 · Answer 1 · answered Feb 05 '19 at 07:16

You could change your function to be more flexible.

Private Function GetLastRow(InWorksheet As Worksheet, InColumn As Variant) As Long
    GetLastRow = InWorksheet.Cells(InWorksheet.Rows.Count, InColumn).End(xlUp).Row
End Function

So you can call it …

Sub Test()
    Dim LastRow As Long
    LastRow = GetLastRow(ThisWorkbook.Worksheet("Sheet1"), "C") 'column as letter
    'or
    'LastRow = GetLastRow(ThisWorkbook.Worksheet("Sheet1"), 3)  'column as number

End Sub

So you can even run this on another workbook using:

LastRow = GetLastRow(Workbooks("OtherWorkbook.xlsx").Worksheet("Sheet1"), "C") 'column as letter

score 0 · Answer 2 · answered Feb 04 '19 at 18:57

0

There is a Workbook object built into VBA that you can use. This documentation should give you the information that you need https://learn.microsoft.com/en-us/office/vba/api/excel.workbook

You would simply put the name of your other workbook in quotes, in parentheses after using the Workbook object (see example on page I hyperlinked). Good luck!

answered Feb 04 '19 at 18:57

Tianna Wrona

342
2
14

Thanks for the link, that is definitely a useful feature but do you know if there's a way to access the other workbook if you don't know its name? – Gardenia Feb 04 '19 at 19:12
If you don't know the other workbook's name then you can loop over the open workbooks `For Each wb in Application.Workbooks` and select the first instance where `wb.Name <> ThisWorkbook.Name` – Tim Williams Feb 04 '19 at 19:58

score 0 · Answer 3 · answered Feb 04 '19 at 21:01

I guess this is what you looking for. When you have more then one Workbook active you can switch between then.

Sub GetLastRow()

Dim myLastRow As Integer

'Active Workbook
Set ws = ThisWorkbook.Sheets("Plan1")
myLastRow = ws.Range("C" & Rows.Count).End(xlUp).Row
MsgBox myLastRow


'Way when you know workbook name
Workbooks.Open Filename:=ActiveWorkbook.Path & "\Teste1.xlsx"
Set ws1 = Application.Workbooks("Teste1.xlsx").Sheets("Plan1")
myLastRow1 = ws1.Range("C" & Rows.Count).End(xlUp).Row
MsgBox myLastRow1

Dim myLastRow As Integer

'If you don't know the name but, opened after your main Workbook
Set ws3 = Application.Workbooks(2).Sheets("Plan1")
myLastRow3 = ws1.Range("C" & Rows.Count).End(xlUp).Row
MsgBox myLastRow3

End Sub

Note that row counting variables **must** be of type `Long` because Excel has more rows than `Integer` can handle. So both, your function *and* your variable must be declared `As Long`. • I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. — Pᴇʜ, Feb 05 '19 at 07:10

Using data from another workbook (other than the active one)

3 Answers3