10 Replies - 815 Views - Last Post: 12 March 2012 - 10:37 AM Rate Topic: -----

#1 Saldash  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 20-December 08

Excel 2000 & VB script being glitchy

Posted 10 March 2012 - 12:16 PM

Hi,

I'm trying to automate a telnet script into a button on an excel spreadsheet.
The user puts info into two cells, and the script uses those values in the script.

The problem I'm finding is that this is only working some of the time - more often than not,
the enter key will be pressed despite using the same sendkeys directly above it --
this is causing whole lines and in some cases partial lines ( :blink: ) not to appear in the command line.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Sub PerformSendKey(ByVal line As String)
    SendKeys String:=line, wait:=True
    SendKeys String:="{enter}", wait:=True
    Sleep 1500
End Sub

Private Sub CommandButton1_Click()
    Application.EnableCancelKey = xlErrorHandler
        On Error GoTo ErrHandler
    
    i = Shell("C:\Windows\System32\cmd.exe", vbNormalFocus)
    Sleep 2000
    
    PerformSendKey ("telnet")
    PerformSendKey ("open " + MachineID)
    PerformSendKey ("user")
    PerformSendKey ("password")
    PerformSendKey ("db2cmd -i -w")
    PerformSendKey ("db2 connect to host")

ErrHandler:
    Exit Sub

End Sub


One thing always works though -- the password, when the script glitches out it misses the top couple of lines and presents the password in plain text, when the script doesn't glitch out, the password also always works.
I can't figure out why only the password line is reliable.

How can I make sure, that every time the button is pressed -- it always works?

Thanks :)

This post has been edited by Saldash: 10 March 2012 - 12:17 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Excel 2000 & VB script being glitchy

#2 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,771
  • Joined: 26-March 09

Re: Excel 2000 & VB script being glitchy

Posted 11 March 2012 - 12:57 PM

I've had a similar issue in the past using send keys and if I remember rightly, I had to send things one character at a time?

Might be worth giving it a try. Just loop around your string and call send keys for each character.

Keep us updated how you get on.
Was This Post Helpful? 0
  • +
  • -

#3 Saldash  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 20-December 08

Re: Excel 2000 & VB script being glitchy

Posted 11 March 2012 - 04:10 PM

I'm actually in half a mind to go about this a different way -
I can batch script this without a problem - a problem arises from that though that I need to be able to send the same command set to any of the 800 machines on my network, but the folks the script is intended for can't run batch scripts
as a security precaution.

Creating a simple GUI is also out of the question as most of the Telnet dlls out there are "products" and must be bought or are hefty code packs that just weigh down the process and there is insufficient info out there to write one from scratch.

What I might do is deploy a console script to all machines with generic code and get the script to simply ask for input of the necessary letter.

Basically I want to insert the current date and a single letter into a record in the database.
If I do this fully remotely, I have to have the script open up a telnet session which means an extra login step.
Running the script from the machine through a telnet session means the script can just prompt for the letter when run as a command - I didn't want to have to go down that route as it means rebuilding all my images (and there are quite a few), but as a last resort I just may have to.
Was This Post Helpful? 0
  • +
  • -

#4 Toadill  Icon User is offline

  • D.I.C Regular

Reputation: 45
  • View blog
  • Posts: 401
  • Joined: 08-January 12

Re: Excel 2000 & VB script being glitchy

Posted 11 March 2012 - 04:12 PM

Sleep is bugged get rid of it.

Maybe replace it with a timer or something...

This post has been edited by Toadill: 11 March 2012 - 04:13 PM

Was This Post Helpful? 0
  • +
  • -

#5 Saldash  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 20-December 08

Re: Excel 2000 & VB script being glitchy

Posted 11 March 2012 - 04:14 PM

View PostToadill, on 12 March 2012 - 12:12 AM, said:

Sleep is bugged get rid of it.

Maybe replace it with a timer or something...

I've already tried waiting until the current time + X number of seconds...
It's just as buggy, if not more so.

What also can happen, is because the target machines are not base in the same building, they are actually spread out country wide - so network latency can have an effect on this as well - where the machine might be online, just not respond within the time allocated within even a section of the script and things can start to go funny.

I've run this the full script on my machine dozens of times without fail - but when my guys use it - that when it starts showing its demons.

This post has been edited by Saldash: 11 March 2012 - 04:19 PM

Was This Post Helpful? 0
  • +
  • -

#6 Toadill  Icon User is offline

  • D.I.C Regular

Reputation: 45
  • View blog
  • Posts: 401
  • Joined: 08-January 12

Re: Excel 2000 & VB script being glitchy

Posted 11 March 2012 - 04:33 PM

View PostSaldash, on 10 March 2012 - 02:16 PM, said:

Hi,

I'm trying to automate a telnet script into a button on an excel spreadsheet.
The user puts info into two cells, and the script uses those values in the script.

The problem I'm finding is that this is only working some of the time - more often than not,
the enter key will be pressed despite using the same sendkeys directly above it --
this is causing whole lines and in some cases partial lines ( :blink: ) not to appear in the command line.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Sub PerformSendKey(ByVal line As String)
    SendKeys String:=line, wait:=True
    SendKeys String:="{enter}", wait:=True
    Sleep 1500
End Sub

Private Sub CommandButton1_Click()
    Application.EnableCancelKey = xlErrorHandler
        On Error GoTo ErrHandler
    
    i = Shell("C:\Windows\System32\cmd.exe", vbNormalFocus)
    Sleep 2000
    
    PerformSendKey ("telnet")
    PerformSendKey ("open " + MachineID)
    PerformSendKey ("user")
    PerformSendKey ("password")
    PerformSendKey ("db2cmd -i -w")
    PerformSendKey ("db2 connect to host")

ErrHandler:
    Exit Sub

End Sub


One thing always works though -- the password, when the script glitches out it misses the top couple of lines and presents the password in plain text, when the script doesn't glitch out, the password also always works.
I can't figure out why only the password line is reliable.

How can I make sure, that every time the button is pressed -- it always works?

Thanks :)


Just a thought but since your amount of commands is constant maybe you could count through them to ensure that all are executed in order


'global variable
Dim intCount as Integer

Private Sub PerformSendKey(ByVal line As String)
    SendKeys String:=line, wait:=True
    SendKeys String:="{enter}", wait:=True
    intCount = intCount + 1
End Sub

Private Sub CommandButton1_Click()
    Application.EnableCancelKey = xlErrorHandler
        On Error GoTo ErrHandler
    
    i = Shell("C:\Windows\System32\cmd.exe", vbNormalFocus)

intCount = 0

Do Until intCount >= 6
    If intCount = 0 Then
      PerformSendKey ("telnet")
    ElseIf intCount = 1 Then
      PerformSendKey ("open " + MachineID)
    ElseIf intCount = 2 Then
      PerformSendKey ("user")
    ElseIf intCount = 3 Then
      PerformSendKey ("password")
    ElseIf intCount = 4 Then
      PerformSendKey ("db2cmd -i -w")
    ElseIf intCount = 5 Then
      PerformSendKey ("db2 connect to host")
    End If
Loop
ErrHandler:
    Exit Sub
    
End Sub


Then add a loop of course to ensure it keeps going.
The loop would end when Count > 5

Could use IF or Select Case.

This post has been edited by Toadill: 11 March 2012 - 04:55 PM

Was This Post Helpful? 0
  • +
  • -

#7 Saldash  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 20-December 08

Re: Excel 2000 & VB script being glitchy

Posted 11 March 2012 - 11:14 PM

The script always enters to a new line, so counting the lines would always show positively even if the text being entered doesn't show. I've tweaked timings and all sorts and added a small sleep inbetween the sendkeys string:=line and {enter}.

I may just go with the deploying of the script, at the risk of sounding lazy, it's far less hassle than it's worth.
Was This Post Helpful? 0
  • +
  • -

#8 Toadill  Icon User is offline

  • D.I.C Regular

Reputation: 45
  • View blog
  • Posts: 401
  • Joined: 08-January 12

Re: Excel 2000 & VB script being glitchy

Posted 12 March 2012 - 12:01 AM

Did you try the code I posted LMAO I think you misunderstand what I did.

Look at the code it adds one to the intCount each time PerformSendKey is called. The if statements will only allow one call each loop iteration.

This should do exactly what you asked...

Unfortunately I do not have VB6.0 installed on this laptop therefore I can not test the code to see everything. Please try the code and let me know if it does what it is suppose to.

This post has been edited by Toadill: 12 March 2012 - 12:07 AM

Was This Post Helpful? 0
  • +
  • -

#9 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,771
  • Joined: 26-March 09

Re: Excel 2000 & VB script being glitchy

Posted 12 March 2012 - 01:03 AM

Like Toadill also asked, did you actually try any of the suggestions given by Toadill or myself, before dismissing them?
Was This Post Helpful? 0
  • +
  • -

#10 Saldash  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 98
  • Joined: 20-December 08

Re: Excel 2000 & VB script being glitchy

Posted 12 March 2012 - 09:36 AM

Yes, I have tried them - the script functions but the output sent to the command line is the same -
I know each function is being called as intended, when intended, the problem I face is that the actual text it's supposed to be sending doesn't appear.

PerformSendKeys () works every time, but
SendKeys String:=line, wait:=True
isn't sending the line all of the time (see below)

Posted Image

Never mind that host isn't reachable - it's a deliberate invalid host.

This post has been edited by Saldash: 12 March 2012 - 09:44 AM

Was This Post Helpful? 0
  • +
  • -

#11 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,771
  • Joined: 26-March 09

Re: Excel 2000 & VB script being glitchy

Posted 12 March 2012 - 10:37 AM

And did you try breaking it down by sending individual characters, as what you showing there is exactly the same as I had (not a telnet program, but to a command prompt) and the only way I could get it to consistently work was to send individual chars to make up the string.

I should also add, that this was purely in VB6, so unless it behaves differently in VBA, it would be worth a try for the sake of a couple of lines of code to make the change.

This post has been edited by maj3091: 12 March 2012 - 10:53 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1