VBA code to read data in a closed workbook"Book2"

irfananeeza

Active Member
Joined
Feb 15, 2008
Messages
322
Office Version
  1. 2010
Hi excel experts, below is a macro which runs good with Workbooks.Open "Book2"code . Instead, Workbooks.Open "Book2", I need a simple code to read/access data within Workbooks"Book2" without opening it to be inserted right above With Workbooks("Book2").Worksheets("Sheet2"). Let's say I do not want to open "Book2" and then close it.

Sub basic_Vlookup()

Dim myLookupValue As String
Dim myFirstRow As Long
Dim myFirstColumn As Long
Dim myLastRow As Long
Dim myLastColumn As Long
Dim myColumnIndex As Long
Dim myVlookupResult As Long

Dim myTableArray As Range

myLookupValue = "Raymond Allen"
myFirstRow = 2
myFirstColumn = 1
myLastRow = 10
myLastColumn = 3
myColumnIndex = 3

'Workbooks.Open Filename:="C:\Users\User\Desktop\Book2.xlsx"
'Workbooks.Open "Book2"

With Workbooks("Book2").Worksheets("Sheet2")
Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), _
.Cells(myLastRow, myLastColumn))
End With

myVlookupResult = WorksheetFunction.VLookup(myLookupValue, _
myTableArray, myColumnIndex, False)

MsgBox "Sales by " & myLookupValue & " are " & Format(myVlookupResult, "#,##0")
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
why would you not want to open it?

you can open the book and then shut it again without updating the screen, then the person using the sheet wouldnt have even known!!

tip for you, go check out the BB codes page so that you can post the codes properly.
 

irfananeeza

Active Member
Joined
Feb 15, 2008
Messages
322
Office Version
  1. 2010
Thx for your reply. Actually, I am self studying VBA, I understood the open/close code. I wondered if there exists another technique to do it differently. Is there a technique to do it?
I will have a look into BB codes pages as well. Thx.
 

irfananeeza

Active Member
Joined
Feb 15, 2008
Messages
322
Office Version
  1. 2010
Thank you for taking the time to provide these useful links/resources.
The video technique seems relevant to my situation. I will let you know if I got it worked. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,993
Messages
5,639,423
Members
417,089
Latest member
jonstr101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top