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)
I keep getting a Compile Error: Variable Not Defined screen when I try to run this – any idea what’s going on?
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