Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If function not working correctly | Excel Discussion (Misc queries) | |||
Offset function not working correctly | Excel Discussion (Misc queries) | |||
Subtotal function is not working correctly | Excel Discussion (Misc queries) | |||
Lookup function still not working correctly | Excel Worksheet Functions | |||
Sum function not working correctly in Excel (Skips Cell) | Excel Worksheet Functions |