Varible help?

[Skeggjold]

I know this is possible just do not know how to make the variable or formula.

I have a server list. Thousands of servers. So I have a C collumn with the list of servers. B collum would be the IP's etc. etc.

What I would like to is put a button , Macro or hyper link in collumn a. That once someone clicks on it a webpage with that servers info comes up from a internal webpage. The VB script I made for it works.

My Question is how do I automate that script for each server on the list. Without having to write the vb code for each server?


This is the VB script.


Sub RFC()
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600
objIE.AddressBar = 0
objIE.StatusBar = 0
objIE.Toolbar = 0
objIE.Visible = True '


objIE.Navigate ("Htt.... code ")
Do
DoEvents
Loop Until objIE.ReadyState = 4


objIE.document.getelementbyID("RFCCriteria").Value = "KSCPNSRV01"( this value would change per row)
objIE.document.getelementbyID("submit").Click

Do
Loop Until objIE.ReadyState = 4




End Sub


Sub X()
Dim RetVal
RetVal = Shell("c:\reports\6152.auto", 1)
End Sub


The value which is KSCPNSRV01 would change for each server.

[Bob Phillips]

Sub RFC()
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600
objIE.AddressBar = 0
objIE.StatusBar = 0
objIE.Toolbar = 0
objIE.Visible = True '


objIE.Navigate ("Htt.... code ")
Do
DoEvents
Loop Until objIE.ReadyState = 4


objIE.document.getelementbyID("RFCCriteria").Value = Activesheet.Cells(Activecell.Row, "C").Value
objIE.document.getelementbyID("submit").Click

Do
Loop Until objIE.ReadyState = 4
End Sub

[Skeggjold]

Thank you

That works but

objIE.document.getelementbyID("RFCCriteria").Value = Activesheet.Cells(Activecell.Row, "C").Value

Is there anyway to do it with button clicked is the active row?

Right now if I just click a button in row A I have to have row c active

[Skeggjold]

an example can not post the screen shot but here is the link for screen shot

//farm3.staticflickr.com/2805/12753087114_9347111366_o.png ( put the http in front of it )

[Bob Phillips]

You don't want to have a button against evry row. Just add one button, assign that code to the button, and then select the server you want to query and click.

[Paul_Hossler]

I'd hook the Worksheet Before Right Click event and determine the server from that cell.



Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rFirstCell As Range
Set rFirstCell = Target.Cells(1, 1)
If Intersect(Target.Cells(1, 1), Target.Parent.Columns(1)) Is Nothing Then Exit Sub
If Intersect(Target.Cells(1, 1), Target.Parent.UsedRange) Is Nothing Then Exit Sub
If Target.Cells(1, 1).Row = 1 Then Exit Sub
Cancel = True
Call GoToTheServer(Target.Cells(1, 1).Value)
End Sub






Option Explicit
Sub GoToTheServer(sServer As String)
MsgBox "Going to " & sServer
End Sub


Paul