Sub Main()
    Dim arr As Variant
    arr = GetArr("C:\tmp\1.xlsx")
    Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Function GetArr(sFile As String) As Variant
    Dim wb As Workbook
    Dim arr As Variant
    On Error Resume Next
        Set wb = Workbooks.Open(sFile, False, True)
    On Error GoTo 0
    If wb Is Nothing Then
        ReDim arr(1 To 1, 1 To 2)
        arr(1, 1) = "Нет файла"
        arr(1, 2) = sFile
    Else
        ReDim arr(1 To wb.Sheets.Count, 1 To 2)
        Dim y As Long
        Dim sh As Worksheet
        For Each sh In wb.Sheets
            y = y + 1
            arr(y, 1) = sh.Name
            arr(y, 2) = sh.Range("A1").Value
        Next
        
        wb.Close False
    End If
    
    GetArr = arr
End Function