Automate Data Extraction Without Opening Destination Workbook Excel VBA

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)