Excel VBA working with ranges gets a Type Mismatch error

I'm working on a macro that is cycling through all of the worksheets in a workbook. I need to grab data that starts on row 11 and goes to column 30. The last row on any given worksheet is different.

I'm trying to use SQL and Ranges to get the data I need on each worksheet. Once I get the data, I want to paste it on the "Data" worksheet. Once I go through all of the worksheets, the macro will do some other things.

I don't use SQL very often and the few times that I have my data has always started on row 1 / column 1. This is the first time I'm trying to grab data that does not start on row 1 / column 1.

When I run the macro, I'm getting a type mismatch error (indicated in the code below). All variables have been declared as public variables.

MyFileName = ""
MyFileName = ActiveWorkbook.Name

WrkShtCnt = 0
WrkShtCnt = Worksheets.Count
    
ws = 1
    
Do Until ws > WrkShtCnt
    
     DoEvents
        
     WrkShtName = ""
     WrkShtName = Trim(Workbooks(MyFileName).Worksheets(ws).Name)
        
     If UCase(WrkShtName) = "NOTES" Or UCase(WrkShtName) = "DATA" Then
          'skip as the macro will NOT be grabbing data from these 2 worksheets
     Else
          vStartDate = ""
          vStartDate = Format(Workbooks(MyFileName).Worksheets(ws).Cells(2, 1), "mm/dd/yyyy")
            
          vEndDate = ""
          vEndDate = Format(Workbooks(MyFileName).Worksheets(ws).Cells(3, 1), "mm/dd/yyyy")
            
          vExtra = ""
          vExtra = UCase(Trim(Workbooks(MyFileName).Worksheets(ws).Cells(4, 1)))
            
          vStr = ""
            
          If Len(vExtra) = 0 Then
               vStr = vStartDate & " - " & vEndDate
          Else
               vStr = vStartDate & " - " & vEndDate & " " & vExtra
          End If
                    
          vLastRow = 0
          vLastRow = Workbooks(MyFileName).Worksheets(ws).Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
            
          MySheet = ""
          Set MySheet = Workbooks(MyFileName).Worksheets(ws)
            
          Set MyData = MySheet.Range(Workbooks(MyFileName).Worksheets(ws).Cells(11, 13), Worksheets(ws).Cells(vLastRow, 30))
            
          Dim dBcn As New ADODB.Connection
          Dim dbRS As New ADODB.Recordset

          dBConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyData & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
            
          dBcn.Open dBConn     
            
          vsql = ""
          vsql = "SELECT * FROM MyData"

          dbRS.Open vsql, dBcn   ' type mismatch error occurs on this line of code
            
            If FirstTime = True Then
               Workbooks(MyFileName).Worksheets("Data").Cells(2, 1).CopyFromRecordset dbRS
               FirstTime = False
            Else
                vLastRow = vLastRow + 1
                Workbooks(MyFileName).Worksheets("Data").Cells(vLastRow, 1).CopyFromRecordset dbRS
            End If
                       
            Set MyData = Nothing
            dbRS.Close
            dBcn.Close
        
        End If
        
        ws = ws + 1
        
   Loop

Some of the code is what I found while researching and I'm trying to adapt it to my situation.

Thanks in advance for any help or suggestions you provide that will can be past this roadblock. My endusers will thank you as well.....Thanks again.

Use the address of the range in the SQL.

Option Explicit

Sub GetData()

    Dim wb As Workbook, ws As Worksheet, wsData As Worksheet
    Dim rngData As Range, LastRow As Long, r As Long
    
    Dim dbConn  As ADODB.Connection, sConn As String, rs As ADODB.Recordset
    Dim SQL As String
    Set dbConn = New ADODB.Connection
    
    ' config
    Set wb = ActiveWorkbook
    Set wsData = wb.Sheets("Data")
    wsData.UsedRange.Offset(1).ClearContents ' leave header
            
    ' connect to workbook
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & wb.FullName & _
            ";Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1"";"
    'Debug.Print sConn
    dbConn.Open sConn
   
    ' scan each sheets
    For Each ws In wb.Sheets
        If UCase(ws.Name) = "NOTES" Or ws.Name = wsData.Name Then
              'skip as the macro will NOT be grabbing data from these 2 worksheets
        Else
                                    
            ' entent of data
            LastRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
            If LastRow < 11 Then
                 MsgBox "Last row < 11 = " & LastRow, vbCritical
                 Exit Sub
            Else
                 Set rngData = ws.Range("M11:AD" & LastRow)
            End If
            
            SQL = "SELECT * FROM [" & ws.Name & "$" & rngData.Address(0, 0) & "]"
            'Debug.Print SQL
            
            ' copy records
            Set rs = New ADODB.Recordset
            rs.Open SQL, dbConn, adOpenForwardOnly
            With wsData
               If r < 2 Then r = 2
               .Range("A" & r).CopyFromRecordset rs
               r = 1 + .Cells(.Rows.Count, "A").End(xlUp).Row
            End With
            
        End If
    Next
    dbConn.Close
    Set dbConn = Nothing
    MsgBox r - 2 & " records copied to " & wsData.Name, vbInformation
End Sub

</details>