Getting data from a closed workbook

[muhleebbin]

Hi Everyone,

I have the following code however am running into an error...

Is it because the file name in fn is from a sharepoint site? When running through the code, it errors out on the rs.open step so it seems like the connection to the file is going fine.

Thanks in advance for your help!

Sub Getdata()

    Dim cn As ADODB.Connection
    Dim fn As String
    Dim rs As ADODB.Recordset
    
    fn = Sheets("Map").Range("BD14").Value
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=fn;Extended Properties='Excel 12.0 Xml;HDR=YES';"
    
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [Sheet31$]"
    rs.Open
    
    Sheet3.Range("A1").CopyFromRecordset rs
    
    rs.Close
    cn.Close

End Sub