Retrieve Data from Protected and Closed workbook

Hi,

I am using following code in a workbook to get data from other closed workbooks. I am able to get data from most of the workbook, however sometimes I get an error 2042 for certain cells in a workbook and it displays #NA. Can someone please help me to resolve this issue.

Code is as below:
------------------------------------

Option Explicit
Sub ExtractData()
    Dim FSO, Fld, Fil
    Dim NewSht As Worksheet
    Dim I As Integer, V As Integer
    Dim Myrange As Range, C As Range
    Dim MainFolderName As String
    Dim fName As String, sName As String
   

    Set FSO = CreateObject("Scripting.FileSystemObject")
    MainFolderName = ThisWorkbook.path
    Set Fld = FSO.GetFolder(MainFolderName)
    Set NewSht = ThisWorkbook.Sheets.Add
    I = 1

    Cells(1, 1) = Now()
    For Each Fil In Fld.Files
        V = 0
        'Skip this workbook
        If Fil.Name <> ThisWorkbook.Name And Fil.Type = "Microsoft Office Excel Worksheet" Then
            I = I + 1
            fName = Fil.Name
            ' Change this sheet name
            sName = "Sheet123"
            ' change these cell refs to grab the cells you want
            Set Myrange = Range("C9,F9,I9,C11,F11,I11,C13,F13,I13")

            Cells(I, 1) = fName
            For Each C In Myrange
                V = V + 1
                Cells(I, 1 + V) = GetValue(MainFolderName, fName, sName, C.Address)
            Next
        Else
        End If
    Next

    Columns("A:A").AutoFit
    Set FSO = Nothing
End Sub
----------------------------------------------------
Private Function GetValue(path, file, sheet, ref)
'   From www.j-walk.com/ss/excel/tips/tip82.htm
'   Retrieves a value from a closed workbook
    Dim arg As String

    '   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If

    '   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
          Range(ref).Range("A1").Address(, , xlR1C1)

    '   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function
-----------------------------------------------------------

Thanks

ADD

ADD

You have previoulsy been asked to read forum rules
& to also wrap your VBA code

You have not Wrapped your Code

Closing all your threads

You may PM me that you will abey this forum rules & I will re-open this thread

Thread re-opened after PM received from ADD