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

[irfananeeza]

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

 

[orsm6]

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]

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.

 

[orsm6]

does this thread help you??

Using VBA to copy data from a closed workbook to the current, open workbook

 

[orsm6]

faq page..... also look at the XLBB plugin page, very helpful for when posting questions

https://www.mrexcel.com/board/help/mrexcel-message-board-faq/

 

[orsm6]

try this.... long video, but i think it does what you need....

https://www.youtube.com/watch?v=wGdwP_hvsrc

 

[irfananeeza]

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.