Automate Data Extraction Without Opening Destination Workbook Excel VBA 

Home More Excel (VBA) Training Videos
     Webhosting by Godaddy     MS Excel Books








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.


Automate Data Extraction Without Opening Destination Workbook Excel VBA
Watch the video on YouTube

Reference

     Webhosting by Godaddy     MS Excel Books
Home More Excel (VBA) Training Videos