Error With ADODB RecordSet

[Ark68]

I am trying to use this code to access data in a closed workbook. It's based on a tutorial found here.

sub test
    Dim wstoa As Worksheet
    Dim tdyr As Long
    Dim fPath As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
  
  
    Set cn = New ADODB.Connection
    tdyr = Year(Now)
    fPath = "D:\WSOP 2020\SupportData\(" & tdyr & ") SOP Schedule.xlsm"
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=fPath;Extended Properties='Excel 12.0 Macro;HDR=YES';"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [ROSTER$]"
    rs.Open
    rs.Close
    cn.Close
End sub

Question 1:
I am getting an error (line in red) that suggests it cannot find the workbook "ROSTER" (error dialogue below). I checked the workbook (2022) SOP Schedule.xlsm and their is indeed a worksheet called "ROSTER", no preceeding or trailing spaces. Before I set the record set, the connection works without error. Any thoughts on why ROSTER isn't being recognized?
Question 2:
Will those code be problematic of users machines where they have not enabled the Microsoft ActiveX Data Objects 6.1 Library? Will users of this workbook need to enable it to use this code?


Error merssage:
The Microsoft Access database engine could not find the object 'ROSTER$'. Make sure the object exists and that you spell it's name and path name correctly. If 'ROSTER$' is not a local object, check your network connection or contact the server administrator."[/code]

 

[Ark68]

I suspect my issue may be more with the connectionstring

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=fPath;Extended Properties='Excel 12.0 Macro;HDR=YES';"

I'm fearing that fPath isn't being applied.

 

[Ark68]

I substituted the variable fPath with the whole path and it works.

cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=D:/WSOP 2020/SupportData/(" & tdyr & ") SOP Schedule.xlsm;" & _
        "Extended Properties='Excel 12.0 Macro;HDR=YES';"

 

[smozgur]

You can keep the variable which is much better for readability, however, you need to include it as a variable as shown below, not write it into the string as "text".

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fPath & ";Extended Properties='Excel 12.0 Macro;HDR=YES';"

 

[Ark68]

Ahhh ... ok. Yeah!
Thank you so much Suat.

 

[smozgur]

Ahhh ... ok. Yeah!
Thank you so much Suat.

You're welcome.