Need some help run my first ADO code

Question

I am gettin an error on the "cn.ConnectionString" line

Sub getDataFromMultipleFiles()

Dim MyFilesPath As String

Dim cn As ADODB.Connection

MyFilesPath = "C:\Users\ayo\Documents\FI with P8 Actuals"

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

                  "Data Source=" & MyFilesPath & "Forecast\_026FI.xlsm;" & \_ 

                  "Extended Properties='Excel 12.0 Macro;HDR=YES';" 

cn.Open

cn.Close

End Sub

Answer 1

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & MyFilesPath & "Forecast_026FI.xlsm;" & _

                  "Extended Properties='Excel 12.0;HDR=YES';" 

if running in source data workbook,codes like below
  Set Conn = CreateObject("ADODB.Connection")
  Set rs = CreateObject("ADODB.Recordset")
  
  Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName

Answer 2

I just "Unprotected" all the sheets in the Workbook now I get the same error on the "cn.Open" line.

Answer 3

You'll have to open the workbook in Excel before opening the ADODB connection. There is no way to specify the password when you open the connection.

Answer 4

Yes it is.

Answer 5

Is Forecast_026FI.xlsm a password-protected workbook?