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
.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
Select Case Err.Number
‘Common error #1: file path or workbook name is wrong.
Case 1004
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
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)

2 thoughts on “Automate Data Extraction Without Opening Destination Workbook Excel VBA

  1. Daniel Turgel

    I keep getting a Compile Error: Variable Not Defined screen when I try to run this – any idea what’s going on?

  2. yonten

    Sir, Thanks for the information provided on Excel sheets.
    Still I got one problem related to Transferring data from one worksheet to another sheet in different workbook. I have subject marks in sheet named “PHY” and I wanted to transfer only selected range of the data in this sheet to another workbook named “ResultSheet”.

    I anticipate for your good support

    Thank you


Leave a Reply

Your email address will not be published. Required fields are marked *