Error retrieving data from closed workbook

A

Add

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
 
J

Jim Cone

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
 
A

Add

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 said:
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
Click to expand...
 
J

Jim Cone

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)


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
 
J

Jim Cone

More...
Ron de Bruin show how to use ADO to extract data from
closed workbooks here... http://www.rondebruin.nl/ado.htm
--
Jim Cone



"Jim Cone"
wrote in message
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)



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
 
A

Add

Data is entered in these cells, there are no formulas in the cells from where
I am exctracting data.

I tried ADO to extract data too, howevere it gives an error too. One thing I
noticed that some of the excel workbook are protected (i.e. protect workbook
structure and windows).

So it is giving error for the files where this workbook protection is on,
however issue is, it is still able to read data from couple of cells from
these workbooks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adapt this Directory Listing working code to insert a file namepattern prompt. 1
ExecuteExcel4Macro 4
Closed workbook 1
ExecuteExcel4Macro Error 2
Retrieving cell formatting from closed workbook 1
importing data from closed workbook 1
Getting value from closed workbook 1
Trouble with types... 1

Top