How can we automate data extraction without opening destination workbook using Excel VBA. This idea can be used to get data from multiple workbooks and also populate user-forms. By adding a simple IF function during extraction we can extract specific data as shown in the training video and accompanying Excel VBA code below:

Option Explicit

Sub findData()
‘Let’s define the variables
Dim GCell As Range
Dim Txt$, MyPath$, MyWB$, MySheet$
Dim myValue As Integer

‘Search what
Txt = InputBox(“What do you want to search for?”)

‘The path to the workbook to be searched
MyPath = “C:\find-data\”
‘The name of the workbook to be searched
MyWB = “data.xlsx”

‘Use the current sheet to store the found data
MySheet = ActiveSheet.Name

‘use error handling routine in case of errors
On Error GoTo ErrorHandler

‘Turn off screen updating to run macro faster
Application.ScreenUpdating = False
Workbooks.Open Filename:=MyPath & MyWB

‘Search for the specified data
Set GCell = ActiveSheet.Cells.Find(Txt)

‘Record values in current workbook
With ThisWorkbook.ActiveSheet.Range(“A1”)
.Value = “Item”
.Offset(0, 1).Value = “Qty”
.Offset(1, 0).Value = GCell.Value
myValue = GCell.Offset(0, 1).Value
If myValue >= 6 Then
.Offset(1, 1).Value = GCell.Offset(0, 1).Value
End If
.Columns.AutoFit
.Offset(1, 1).Columns.AutoFit
End With

‘Close data workbook; don’t save it; turn screen updating back on
ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
Exit Sub

‘Error Handling
ErrorHandler:
Select Case Err.Number
‘Common error #1: file path or workbook name is wrong.
Case 1004
Range(“A1:B2”).ClearContents
Application.ScreenUpdating = True
MsgBox “The workbook ” & MyWB & ” could not be found in the path” & vbCrLf & MyPath & “.”
Exit Sub

‘Common error #2: the specified data wasn’t in the target workbook.
Case 9, 91
ThisWorkbook.Sheets(MySheet).Range(“A1:B2”).ClearContents
Workbooks(MyWB).Close False
Application.ScreenUpdating = True
MsgBox “The value ” & Txt & ” was not found.”
Exit Sub

‘General case: turn screenupdating back on, and exit.
Case Else
Application.ScreenUpdating = True
Exit Sub
End Select

End Sub

As you can observe from the VBA code we have described error-handling in good detail. Disabling ScreenUpdating during macro execution helps the macro to run faster.
Watch the video:

Further reading:
Application.ScreenUpdating Property (Excel)