Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open workbooks Repeat/Loop VBA code
Hello all,
Am running the following code which I found on the internet to see if a certain workbook was already open; Function IsWbOpen(wbName As String) As Boolean Dim i As Long For i = Workbooks.Count To 1 Step -1 If Workbooks(i).Name = wbName Then Exit For Next If i < 0 Then IsWbOpen = True End Function This works great, - to a point, I can identify a workbook and interrogate certain cells for information, however when I save the file, Excel inserts the whole workbook path into the formula for example; =IF(iswbopen("Test.xls")=TRUE,'[Test.xls]Summary'!$B$1,"No") Becomes; =IF(iswbopen("Test.xls")=TRUE,'C:\MyDocs\Excel\[Test.xls]Summary'!$B$1,"No") So if I then open a workbook with the same name from within a Lotus Notes email or from another directory the formula becomes useless. In addition the formula doesn't automatically update when I close the Test.xls workbook or re-open it. To summarise what I trying to do, I have a number of offices who need to send me their reports, I want to open a file and my summary sheet looks at cell B1 on the Summary tab in their Test.xls file, which contains the office name and then a separate lookup cell can then look at cell B2 for the value. Is there a way of firstly locking the formula so Excel cannot tamper with it and secondly keep repeating the code just like in a regular cell i.e. =B1+C1 etc. Thanks very much for any assistance; Colin. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open workbooks Repeat/Loop VBA code
How about:
=IF(iswbopen("Test.xls")=TRUE,INDIRECT("[test.xls]Summary!$B$1"),"No") =indirect() will return an error if the sending workbook is not open, but that's ok with you! ps. I'd watch out for differences in upper/lower case, too: Option Explicit Function IsWbOpen(wbName As String) As Boolean Dim i As Long For i = Workbooks.Count To 1 Step -1 If LCase(Workbooks(i).Name) = LCase(wbName) Then Exit For Next i If i < 0 Then IsWbOpen = True End Function pps. Another way to check to see if a workbook is open without looping through the workbooks collection. Option Explicit Function IsWbOpen(wbName As String) As Boolean On Error Resume Next IsWbOpen = CBool(Workbooks(wbName).Name < "") On Error GoTo 0 End Function Col wrote: Hello all, Am running the following code which I found on the internet to see if a certain workbook was already open; Function IsWbOpen(wbName As String) As Boolean Dim i As Long For i = Workbooks.Count To 1 Step -1 If Workbooks(i).Name = wbName Then Exit For Next If i < 0 Then IsWbOpen = True End Function This works great, - to a point, I can identify a workbook and interrogate certain cells for information, however when I save the file, Excel inserts the whole workbook path into the formula for example; =IF(iswbopen("Test.xls")=TRUE,'[Test.xls]Summary'!$B$1,"No") Becomes; =IF(iswbopen("Test.xls")=TRUE,'C:\MyDocs\Excel\[Test.xls]Summary'!$B$1,"No") So if I then open a workbook with the same name from within a Lotus Notes email or from another directory the formula becomes useless. In addition the formula doesn't automatically update when I close the Test.xls workbook or re-open it. To summarise what I trying to do, I have a number of offices who need to send me their reports, I want to open a file and my summary sheet looks at cell B1 on the Summary tab in their Test.xls file, which contains the office name and then a separate lookup cell can then look at cell B2 for the value. Is there a way of firstly locking the formula so Excel cannot tamper with it and secondly keep repeating the code just like in a regular cell i.e. =B1+C1 etc. Thanks very much for any assistance; Colin. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeat macro for all open workbooks | Excel Programming | |||
Loop through, open, and modify all workbooks in file | Excel Programming | |||
Loop Through All Open Workbooks | Excel Programming | |||
Loop through all Open workbooks | Excel Programming | |||
Loop through open workbooks | Excel Programming |