Extract rows of data meeting criteria

Option Explicit
Sub GetDataFromClosedWB()
 
    'This code is primarily based on this thread:
    'http://stackoverflow.com/questions/27908561/use-excel-vba-to-change-the-value-of-a-cell-in-a-closed-workbook
 
    '//Declare variables//
    Dim objConn       As Object
    Dim objRec        As Object
    Dim strMySQL      As String
    Dim strDataSource As String
    Dim strConnString As String
    Dim lngPasteRow   As Long
   
    Set objConn = CreateObject("ADODB.Connection")
    Set objRec = CreateObject("ADODB.Recordset")
   
    'Full path and file name - change to suit. Note workbook can be closed or opened.
    strDataSource = "C:\My Data\InvoiceList.xlsx"
   
    strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & strDataSource & ";" & _
                "Extended Properties=""Excel 12.0;HDR=YES"";"
   
    objConn.Open strConnString
    
    'Tab name and field (column) header to retrieve the data. Here the data will be pulled from 'Sheet1' from a column titled 'Created By' where the entries in that column equal 'John Bergen'.
    'Change to suit - but note the sheet name MUST have trailing '$'.
    strMySQL = "SELECT * FROM [Sheet1$] WHERE [Created By] = ""John Bergen"";"
   
    With objRec
        'Return the last value in a field
        .CursorType = 2 'adOpenDynamic [URL]http://stackoverflow.com/questions/14122308/rowset-does-not-support-scrolling-backward[/URL] Need a CursorType of 2 i.e. 'adOpenDynamic' to enable MoveLast, MoveNext and MovePrevious
        .Open strMySQL, objConn
        'Find the last row across columns A to H (inclusive) on the active sheet and increment it by one. Assumes the active sheet is not blank (will error out if it is).
        lngPasteRow = Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Range("A" & lngPasteRow).CopyFromRecordset objRec
        .Close
        'Return the recordcount of the dataset
        .CursorType = 3 'adOpenStatic For a list of these literal values refer [URL]http://www.w3schools.com/asp/prop_rs_cursortype.asp[/URL]
        .Open strMySQL, objConn
        If .RecordCount = 0 Then
            MsgBox "No records were imported!!", vbCritical
        Else
            MsgBox .RecordCount & " records have now been imported.", vbInformation
        End If
    End With
   
    'Tidy up - remove objects from memory to free up space
    objConn.Close
    Set objRec = Nothing
    Set objConn = Nothing
   
End Sub