Update linked cells

[charlesa920]

I marked earlier thread "Solved" but it ain't.

I have an application that PINGS IP addresses. I can supply these by range or have it point to an external file.

My file with the IP addresses is Radio_SpreadSheet.xls
which contains a sheet "all IPs". column A in this sheet has a list of 500 IPs of which 250 are assigned.

So when I run the PINGscanner it tries to ping all of the IPs and I only need it to ping the ones currently assigned. Successful pings are green, time_outs are red... with all the IPs being pinged the results are meaningless. I need to be able to quickly identify any IPs that are off the network. Somehow it needs to skip unassigned IPs.

When an IP gets assigned the user name goes into column B. I tried for a solution by adding another sheet in the Radio_SpreadSheet.xls named "IP". Here is the formula I used in IP column A
=IF('[Radio_SpreadSheet.xls]all IPs'!B1<>"",'[Radio_SpreadSheet.xls]all IPs'!A1,"")

OK so far as the sheet IP only shows IPs that are assigned....but when I run my PINGscanner it defaults to scan the "all IPs" sheet. So I created another workbook "activeIPs.xls" with a sheet "activeIPs" which includes the same formula as above.

So I tell PINGscanner to scan based upon this "activeIPs.xls" and it appears to work...until:
1) I handout a new IP and
2)the workbook "activeIPs.xls" which has not been updated to reflect the newly assigned IPs

Do I need a bit of code that everytime I save "Radio_SpreadSheet.xls" it will also update the linked values in "activeIPs.xls"?

Perhaps I am going about this all wrong, I just don't know?
Any suggestion, solutions, even finger pointing will be appreciated!
Charles

[Bob Phillips]

Seems unnecessarily complex.

Why not just get the PINGscanner to scan the IP sheet? Or even get the PINGScanner to check column B before pinging the IP address.

[charlesa920]

Hi again xld,
I cannot figure out how to tell the PINGscanner which range of IPs to scan. It appears to find the most complete list and default to it.

the only thing I can tell it to do is use a particular file.

It will do a text file or excel and probably others too, I have not checked other file types.

[Bob Phillips]

what is the code you are using?

[charlesa920]

I have this code in a module in Radio_SpreadSheet.xls ...
[vba]
Sub open_update_save()
'
' open_update_save Macro
' Macro recorded 12/22/2007 by charles
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Workbooks.Open Filename:= _
"C:\FOLDERS\Excel Files\activeIPs.xls" _
, UpdateLinks:=3
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
[/vba]
I may have a jurry rigged way to get it done.
If I can get the above code to run everytime the Radio_SpreadSheet.xls is saved it should do the trick.

I see there is Workbook routine option "BeforeSave" but I need it AfterSave so the changes will be reflected.


Everytime the radiospreadsheet is saved it needs to also update and save the "activeIPs.xls" with the latest information.

What if this program gets shuffled to another computer and the directory to the "activeIPs.xls" changes... maybe a "where is the file" msgbox

How about if I put the "activeIPs.xls" in the xlstart so it is always loaded?

thanks
Charles

EDIT: perhaps I can have it run the open_update_save on the WorkbookDeactivate event. If my user is going to run IPScanner will this entail deactivating the workbook?