G
Guest
I have a spread sheet of Ip addresses and I'd like to be able to click the cell with an Ip in it and have telnet fire off with that cells data (Ip address). Can it be done
Thanks
Thanks
A
Arvi Laanemets
Hi
It's time to switch to Access for you, I think.
--
(Don't use my reply address - it's spam-trap)
Arvi Laanemets
address). Can it be done?
It's time to switch to Access for you, I think.
--
(Don't use my reply address - it's spam-trap)
Arvi Laanemets
cell with an Ip in it and have telnet fire off with that cells data (IpCmark said:
address). Can it be done?
H
Harlan Grove
Why? It's not obvious why Access, also neither primarily a scripting language
nor a network admin application, would be any better suited than Excel for such
an application.
It could be done using a SelectionChange event handler that would use VBA's
Shell statement to launch TELNET. It's not unlikely there's already an example
of this sort of thing in the Google Groups archives for the newsgroup
microsoft.public.excel.programming
F
Frank Kabel
Hi
some possible ideas:
1. use the Selection_change event and check for a specific cell:
- read the value of the cell
- execute your Telnet code
2. You may also use the right-mouseclick oder Doubleclick event for
this
some possible ideas:
1. use the Selection_change event and check for a specific cell:
- read the value of the cell
- execute your Telnet code
2. You may also use the right-mouseclick oder Doubleclick event for
this
A
Arvi Laanemets
Hi
Textbox's OnClick/OnDoubbleClick event is reason enough
Of-course you can do all this, or at least something equivalent, in Excel
too, but not so easily.
Harlan Grove said:
Textbox's OnClick/OnDoubbleClick event is reason enough
Of-course you can do all this, or at least something equivalent, in Excel
too, but not so easily.
H
Harlan Grove
...
...
Not all Excel users have Access.
What would this look like in Access that makes you believe it'd be lots easier
to do in Access using Access's Textbox's OnClick event? I may be naive, but
wouldn't there be a necessary intermediate step of loading IP addresses into
Textbox controls?
In Excel, if the IP addresses are already in cells, all it takes is using a
SelectionChange event handler that checks whether Target's contents are a valid
IP address, then calling an API function to detect whether TELNET is already
running, and if so switch to it else launch it, then use sendkeys to create the
connection. It's hard to see how the guts of this would be much (any?) different
in an Access OnClick event handler, but perhaps you could provide details.
...
...Textbox's OnClick/OnDoubbleClick event is reason enough
Of-course you can do all this, or at least something equivalent, in Excel
too, but not so easily.
Not all Excel users have Access.
What would this look like in Access that makes you believe it'd be lots easier
to do in Access using Access's Textbox's OnClick event? I may be naive, but
wouldn't there be a necessary intermediate step of loading IP addresses into
Textbox controls?
In Excel, if the IP addresses are already in cells, all it takes is using a
SelectionChange event handler that checks whether Target's contents are a valid
IP address, then calling an API function to detect whether TELNET is already
running, and if so switch to it else launch it, then use sendkeys to create the
connection. It's hard to see how the guts of this would be much (any?) different
in an Access OnClick event handler, but perhaps you could provide details.
A
Arvi Laanemets
Hi
I didn't find single Excel in catalogues here at moment, but I remember I
did see it in one a couple of years ago - it did cost almost as much as
whole Office. So by us (I'm leaving out those practicing piracy) or you have
both excel and access (of course maybe you didn't install the whole package,
but you can always add new components), or you are freeware fan and have
p.e. OpenOffice. I'm sure anyone can order a single Excel here too, but
probably they have to ship it from somewhere abroad.
I would import the Excel table into Access at start. And then create a form
based on it - you have an option to select between 4 layouts, but isually
it'll be or Columnar (data from one row are displayed at once) or Tabular
(it looks much like excel worksheet, but you are limited to fields present
in table). You haven't to worry about basic navigating, or adding/deleting
rows, or refreshing data when editing - it's cared of, at least so long you
keep things simple. You create a form, maybe you edit it to place or format
controls in a way you like or add some combo boxes to find a particular
record and navigate to it (I'm sure 15-30 min is enough for all t´his), and
then you can concentrate on what will happen when something is done (p.e.
when you click onto control with link)
OK. I did forget about this event. But I don't like it anyway. There are
maybe 10-100 cells, clicking on which the procedure has to be called, but
this bloody event is firing off for every 65536*256 cells, and I have always
to make a check. And it doesn't differ, have I tabbed in, or clicked on it,
or made a doubble-click. It feels for me somewhat sloppy. In Access, I'll
use the event for single control.
Here I give up - no experience with TELNET. But I think the code for it in
Access and Excel will not differ significally.
Don't misconceive me - I don't think Excel is bad. For some tasks it is much
better than Access. And vice versa) But everyone has his own taste, and
absolute truth doesn't exist anyway.
Harlan Grove said:
I didn't find single Excel in catalogues here at moment, but I remember I
did see it in one a couple of years ago - it did cost almost as much as
whole Office. So by us (I'm leaving out those practicing piracy) or you have
both excel and access (of course maybe you didn't install the whole package,
but you can always add new components), or you are freeware fan and have
p.e. OpenOffice. I'm sure anyone can order a single Excel here too, but
probably they have to ship it from somewhere abroad.
I would import the Excel table into Access at start. And then create a form
based on it - you have an option to select between 4 layouts, but isually
it'll be or Columnar (data from one row are displayed at once) or Tabular
(it looks much like excel worksheet, but you are limited to fields present
in table). You haven't to worry about basic navigating, or adding/deleting
rows, or refreshing data when editing - it's cared of, at least so long you
keep things simple. You create a form, maybe you edit it to place or format
controls in a way you like or add some combo boxes to find a particular
record and navigate to it (I'm sure 15-30 min is enough for all t´his), and
then you can concentrate on what will happen when something is done (p.e.
when you click onto control with link)
valid
OK. I did forget about this event. But I don't like it anyway. There are
maybe 10-100 cells, clicking on which the procedure has to be called, but
this bloody event is firing off for every 65536*256 cells, and I have always
to make a check. And it doesn't differ, have I tabbed in, or clicked on it,
or made a doubble-click. It feels for me somewhat sloppy. In Access, I'll
use the event for single control.
Here I give up - no experience with TELNET. But I think the code for it in
Access and Excel will not differ significally.
Don't misconceive me - I don't think Excel is bad. For some tasks it is much
better than Access. And vice versa
) But everyone has his own taste, andabsolute truth doesn't exist anyway.
H
Harlan Grove
Arvi Laanemets said:
Perhaps Microsoft markets its software differently in your country than in
the US, but in the US there are (oversimplifying considerably) two Office
variants: Standard and Professional, and aside from minor stuff, the
difference between the two is that Professional includes Access, Standard
doesn't. Both include Excel, Word, PowerPoint and Outlook. In that sense,
not everyone, at least in the US, who has either stand alone Excel or Office
Standard has Access. I'd bet that makes a rather large number of people who
don't have Access.
All this is 15 minutes more than just using the worksheet as-is in Excel.
....
The SelectionChange macro wouldn't be firing 65536*256 times, only once each
time the selection changes. Few people move through more than a tiny
fraction of this many cells in a worksheet. Even if it took 1/10 of a second
to move from cell to cell without any event handlers at all, it'd take over
19 days! to move through all +16 million cells in a worksheet.
But importing the worksheet into access and creating the form likely
involves more total time than firing the Excel event handler whenever the
cellpointer moves. At that point, as long as the first thing that happens is
checking whether the new active cell contains an IP address, which is fairly
quick and simple, the event handler won't take that much time unless you're
holding down cursor keys to move through a great many cells instead of using
[Ctrl]-shifted cursor keys or using the mouse and scroll bars.
As for the undue burden that attends use of the SelectionChange event
handler, given the following test harness.
'Sheet1 Class Module
'-------------------
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, v As String, ss As String
Dim n As Long, p As Long, q As Long
For Each c In Target
v = c.Value
If VarType(v) = 8 Then
q = 1
For n = 1 To 4
p = InStr(q, v, ".")
If p = 0 Then
v = ""
Exit For
ElseIf n > 1 And Not Mid(v, q, p - q) Like String(p - q, "#") Then
v = ""
Exit For
End If
Next n
'# following statement uncommented to check that IP addresses are
'# detected, then commented out when measuring event overhead
'If v <> "" Then Debug.Print "IP", c.Address(0, 0), v
End If
Next c
End Sub
'Module1
'-------
Option Explicit
Sub test()
Const MAXITER As Long = 200
Dim rng As Range, c As Range, dt As Date, n As Long
Set rng = Range("A1:H20")
Application.EnableEvents = True
dt = Now
For n = 1 To MAXITER
For Each c In rng
c.Activate
Next c
Next n
Debug.Print "With events", 86640# * CDbl(Now - dt)
Application.EnableEvents = False
dt = Now
For n = 1 To MAXITER
For Each c In rng
c.Activate
Next c
Next n
Debug.Print "Without events", 86640# * CDbl(Now - dt)
Application.EnableEvents = True
End Sub
Sheet1!A1:H20 containing 4 cells with IP addresses, along with a mixture of
blanks, text and numbers in the other cells.
On my wife's PC with a 1GHz Celeron running Excel 2000, the test macro
produces the result
With events 7.01944419415668
Without events 6.01666681235656
Since the test macro simulates moving the cell pointer 32,000 times, that
looks like an execution time drag of 0.000031 seconds per selection change.
Maybe that's too much for you, but maybe it'd be acceptable to the OP.
Don't misconceive me - I don't think Excel is bad. For some tasks it is much
better than Access. And vice versa
absolute truth doesn't exist anyway.
Absolute truth *DOES* exist. Mathematics and logic are both based on it. As
for the sciences, they may not provide absolute truth, but they do provide
contingently reliable truth. And they rely on MEASUREMENT and experimental
support for or against hypotheses. Given your estimate of 15 minutes to
program the needed Access infrastructure to support the OP's task, and the
apparent execution speed drag of 0.000031 seconds to move between cells on
the PC I'm using at the moment, I'd have to move the the cell pointer over
28 million times in order to realize any time savings using Access, at least
in the manner you propose.
Yes, there is such a thing as subjectivity. There's also such a thing as
objectivity. And there's also such a thing as obstinate refusal to admit
grossly mistaken design advice.