Hi,

Please see the code below to read data from closed and protected workbooks
in a directory.

I am able to read data from most of the workbook, however from some workbook
it reads couple of cells correctly and for other cells it gives #NA (i.e.
error 2042). If I open the workbook in which it reads few cells correctly and
few cells gives error, then it gets all the cells data correctly of open
workbook.

This #NA error appears only when the workbook is closed and that too only
for couple of cells of that workbook and not for all other cells.

Can someone help me to rectify this error.


------------------------Code------------------------
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
Worksheet" Then
I = I + 1
fName = Fil.Name
' Change this sheet name
sName = "My Sheet"
' 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)
' 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

--------------------------------End of Code-----------------------

Thanks

Looks like you modified John Walkenbach's code...
The lines with my changes are noted with '<<< on the right.
'--
Sub ExtractData_R1()
Dim FSO, Fld, Fil
Dim NewSht As Worksheet
Dim I As Integer, V As Integer
Dim Myrange As Variant, C As Long '<<<<<
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 Excel Worksheet" Then '<<<<<<
I = I + 1
fName = Fil.Name
' Change this sheet name
sName = "My Sheet"
' change these cell refs to grab the cells you want
Myrange = Array("C9", "F9", "I9", "C11", "F11", "I11", "C13", "F13", "I13") '<<<<<<
NewSht.Cells(I, 1) = fName '<<<<<<
For C = 0 To UBound(Myrange) '<<<<<<
V = V + 1
NewSht.Cells(I, 1 + V) = GetValue(MainFolderName, fName, sName, Myrange(C)) '<<<<<
Next
Else
End If
Next
Columns("A:A").AutoFit
Set FSO = Nothing
Set NewSht = Nothing '<<<<
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming - check out "List Files")




"Add"
wrote in message
Hi,
Please see the code below to read data from closed and protected workbooks
in a directory.
I am able to read data from most of the workbook, however from some workbook
it reads couple of cells correctly and for other cells it gives #NA (i.e.
error 2042). If I open the workbook in which it reads few cells correctly and
few cells gives error, then it gets all the cells data correctly of open
workbook.
This #NA error appears only when the workbook is closed and that too only
for couple of cells of that workbook and not for all other cells.
Can someone help me to rectify this error.
------------------------Code------------------------
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
Worksheet" Then
I = I + 1
fName = Fil.Name
' Change this sheet name
sName = "My Sheet"
' 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)
' 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
--------------------------------End of Code-----------------------
Thanks

Hi Jim,

I applied the code that you have given, still I am getting same error 2042.
The workbook has data in cells C9, F9, I9 and so on. It gives error for cells
c9 and f9 and is returning correct value for cell I9.

If I open the wrokbook with this data then it is able to read cell c9 and f9
correctly. What else could be wrong?

Thanks

ADD
------------------------------

"Jim Cone" wrote:

Looks like you modified John Walkenbach's code...
The lines with my changes are noted with '<<< on the right.
'--
Sub ExtractData_R1()
Dim FSO, Fld, Fil
Dim NewSht As Worksheet
Dim I As Integer, V As Integer
Dim Myrange As Variant, C As Long '<<<<<
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 Excel Worksheet" Then '<<<<<<
I = I + 1
fName = Fil.Name
' Change this sheet name
sName = "My Sheet"
' change these cell refs to grab the cells you want
Myrange = Array("C9", "F9", "I9", "C11", "F11", "I11", "C13", "F13", "I13") '<<<<<<
NewSht.Cells(I, 1) = fName '<<<<<<
For C = 0 To UBound(Myrange) '<<<<<<
V = V + 1
NewSht.Cells(I, 1 + V) = GetValue(MainFolderName, fName, sName, Myrange(C)) '<<<<<
Next
Else
End If
Next
Columns("A:A").AutoFit
Set FSO = Nothing
Set NewSht = Nothing '<<<<
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming - check out "List Files")




"Add"
wrote in message
Hi,
Please see the code below to read data from closed and protected workbooks
in a directory.
I am able to read data from most of the workbook, however from some workbook
it reads couple of cells correctly and for other cells it gives #NA (i.e.
error 2042). If I open the workbook in which it reads few cells correctly and
few cells gives error, then it gets all the cells data correctly of open
workbook.
This #NA error appears only when the workbook is closed and that too only
for couple of cells of that workbook and not for all other cells.
Can someone help me to rectify this error.
------------------------Code------------------------
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
Worksheet" Then
I = I + 1
fName = Fil.Name
' Change this sheet name
sName = "My Sheet"
' 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)
' 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
--------------------------------End of Code-----------------------
Thanks

Do the questionable cells have formulas in them that refer to other workbooks?
If so, my guess is that the cell values are #N/A when the workbook is closed.
(kind of analogous to "is the refrigerator light on when the door is closed)<g

You could turn off Screen Updating and open - read - close each file.
Unless the files are very large, there shouldn't be much time difference.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Add" wrote in message
Hi Jim,
I applied the code that you have given, still I am getting same error 2042.
The workbook has data in cells C9, F9, I9 and so on. It gives error for cells
c9 and f9 and is returning correct value for cell I9.

If I open the wrokbook with this data then it is able to read cell c9 and f9
correctly. What else could be wrong?
Thanks
ADD