Function not working correctly
 

I cant get the below code that calls the function (below) to work. Can
anyone tell me whats wrong with it?

'Loops through B100 to find a match
Counter = 1
Do
Counter = Counter + 1
'Calls the Closed workbook reference code to be used in
the match loop
p = "Q:\CS Management Reports\Reports Setup"
'p = "\\scgvlfs05\sesfa\CS Management Reports\Reports
Setup"
f = "Authorized List.xls"
s = "Reports Setup"
a = ("B" & Counter)
'If 5 and 3 MATCHES an Authorized ID, perform found action
RemoteValue = GetValue(p, f, s, a)
If NN = RemoteValue Then
MsgBox ("Profile " & NN & " is authorized. Click OK to
continue")
MatchFlag = 1
Exit Do

Below is the function the code above calls.

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


It will not work if the workbook is closed like it is
supposed to do. Why does it only work when the workbook
is open?

Function not working correctly
 

Actually the problem was that it wasnt working, the
problem was that it needed to have at least 1 worksheet
active in any Excel Window. Thats strange but once I
created a worksheet, it worked.


-----Original Message-----
I cant get the below code that calls the function (below)
to work. Can
anyone tell me whats wrong with it?

'Loops through B100 to find a match
Counter = 1
Do
Counter = Counter + 1
'Calls the Closed workbook reference code to be used in
the match loop
p = "Q:\CS Management Reports\Reports Setup"
'p = "\\scgvlfs05\sesfa\CS Management Reports\Reports
Setup"
f = "Authorized List.xls"
s = "Reports Setup"
a = ("B" & Counter)
'If 5 and 3 MATCHES an Authorized ID, perform found action
RemoteValue = GetValue(p, f, s, a)
If NN = RemoteValue Then
MsgBox ("Profile " & NN & " is authorized. Click OK to
continue")
MatchFlag = 1
Exit Do

Below is the function the code above calls.

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


It will not work if the workbook is closed like it is
supposed to do. Why does it only work when the workbook
is open?


.