Using VBA to Create Copy/Paste PuTTY Commands

[pssrm]

***I am having trouble attaching the Images as used in the following question, you can find them here: Google Drive MrExcel Folder including the Excel File

Please forgive me as I am brand new to VBA, I only started researching and learning 2 weeks ago using Excel 2010 when I found myself in a position in which I could make the processes of the project that my team at work is doing. We are setting the time and date on over 30,000 ATS Switches manually, as they are not connected to the network.

In PuTTY, via Network and Local Ports we are running the Commands:

admin
password
d
SetTime HH:MM:SS
SetDate MM/DD/YYYY
Time

*Current Time in Military Format, Current Date, after "Time" Command we must hit Enter to check that it accepted the new time/date

We are able to enter commands into Notepad and Copy them, pasting each time we run the commands, but the frustration is that the current time has to be manually set, therefore we must update our Notepad constantly, using the new time.

What I have come up with is:

Figure 1: https://drive.google.com/file/d/0B5xOYg-vP9WWeGozQnN5UVBMVzg/edit?usp=sharing

VBA:

************************************************

Dim SchedRecalc As Date

Sub Recalc()
With Sheet1.Range("C5")
.Value = Format(Time, "HH:MM:SS")
End With
Call SetTime
End Sub

Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

**********************************************

Dim TimerActive As Boolean
Sub StartTimer()
Start_Timer
End Sub
Private Sub Start_Timer()
TimerActive = True
Application.OnTime Now() + TimeValue("00:00:01"), "Timer"
End Sub
Sub Stop_Timer()
TimerActive = False
End Sub
Private Sub Timer()
If TimerActive Then
Range("B2:C7").Select
Selection.Copy
Application.OnTime Now() + TimeValue("00:00:01"), "Timer"

End If
End Sub

**************************************************

The Start Clock Button runs the SetTime Macro, the Stop Clock Button runs the Disable Macro, the Start AutoCopy Button runs the StartTimer Macro and the Stop AutoCopy runs the Stop_Timer Macro. Everything is working as is supposed to, however the problem is that the Copied Output result is:
Figure 2 https://drive.google.com/file/d/0B5xOYg-vP9WWQXdUc1VHQ2tZeWM/edit?usp=sharing

Which VERY close to what I need with the exception of the extra "spaces" that are a result of copying the Blank Cells: C2,C3,C4,C7. The result is an error that prevents the Commands from working as they are not recognized by PuTTY. What I need is to "Trim" the fat and have the result be:

Figure 3 https://drive.google.com/file/d/0B5xOYg-vP9WWb3ZBUXNPZHhNalU/edit?usp=sharing

I have tried to apply the "TRIM" function with no success. The alternative would be having these commands be in a single column, but I do not know how to format a single cell to print text and the time ("SetTime =(now)")

ANY help would be greatly appreciated and even compensated if necessary.

I am EAGER to see what responses I get from this and will be actively checking for your generous input.

 

[John_w]

Welcome to MrExcel, pssrm.

The simplest solution, with minimal changes to your code and existing procedure is to put everything in the B column.

Change:

With Sheet1.Range("C5")
.Value = Format(Time, "HH:MM:SS")
End With

to:

    With Sheet1
        .Range("B5").Value = "SetTime " & Format(Time, "HH:MM:SS")
        .Range("B6").Value = "SetDate " & Format(Time, "MM/DD/YYYY")
    End With

and change:

Range("B2:C7").Select

to:

    Range("B2:B7").Select

PS - please use CODE tags when posting VBA code, like this:

[CODE]
your VBA code here
[/CODE]

 

[pssrm]

First I must say you have MADE MY DAY!!! THANK YOU SO ABSOLUTELY MUCH!!!

PS - please use CODE tags when posting <acronym title="visual basic for applications">VBA</acronym> code, like this:

your <acronym title="visual basic for applications">VBA</acronym> code here

I knew I was supposed to format the code when posting, I remember seeing it on a different forum post I had looked at but couldn't find the proper etiquette instructions anywhere. Sorry for being sloppy, I tried to fix the formatting now but I don't seem to be able to edit that first post, I certainly WILL format correctly in all future instances.

Again! Thank you so much!!

 

[pssrm]

I forgot to ask, how do I get the date to be properly formatted? As of now it is coming up as "12/30/1899" This isn't really a big deal, as we could certainly just update the date on a daily basis. Thank you again!

 

[John_w]

I was being sloppy there. For the date:

        .Range("B6").Value = "SetDate " & Format(Date, "MM/DD/YYYY")

 

[pssrm]

Perfect!!