Getting Validation cell value from closed workbook

[Walter349]

Can anyone give me some hint as to how to deal with getting a cell value from a cell that is a merged validation cell, populated from a named range.

getting the value from a standard formatted cell, no problem.
getting the value from a merged cell, no problem.

but cannot get the value for a cell formatted as a validation cell.(M6)

This is the code being used and works on everything except the validation cell(M6) (Not my code by the way)

========================
Sub ReadDataFromAllWorkbooksInFolder()

Dim FolderName As String, wbName As String, r As Long, cValue As Variant

Dim wbList() As String, wbCount As Integer, i As Integer

FolderName = "z:\test folder"
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
Workbooks.Add
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "M6")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
Next i
End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String

GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = " ' " & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)

End Function
=========================================================

Any ideas??

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Hi,

I ran a test.

Got and error when no value had yet been assigned to the merged data validation list cell.

However, if a value had been selected, there was no error and the value was returned.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

that was quick,

Strange result, as I get either a 0 in the controlled test or #REF error from the real data

Let me expand a little, the purpose of this, is to run through 1000 + plus files and record the file name and the value in the validation cell of each file, which is just the reporting name from a selection of staff names sourced from a named range on another sheet.

The validation cell itself is a group of merged cells, but for the data side of things, it should make no difference

The reporting form is protected for all non-entry cells,but the cell M6 in question is not locked.

All of the files have a value in the M6 cell

I am now wondering if the protection on the file may cause the fault

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Is the SHEET protected or is the FILE protected?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Just the sheet is protected, I tried it on a test sheet that was protected and it worked fine.

There is something about the original file it does not like or recognise and keeps coming up with a #REF! (invalid cell reference) yet the cell is present, populated and correctly identified.

May have to approach this differently than planned

'If at first you don't succeed, then your hammer is below specifications'

 

[Walter349]

I never managed to discover what the problem was, but approached it from another directon, which did mean I could not use the function above to do this from closed files, but had, instead to open each one in turn.

But it only took about ten minutes to run through 2000+ files.

The Macro above does work in all other instances though.

'If at first you don't succeed, then your hammer is below specifications'