Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 6th, 2013, 07:56 AM
Registered User
 
Join Date: Feb 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Macro - Run ping every hour

Excel 2010
VB for Apps 7.0

Dear all,

Im hoping someone could help me!

I have a Excel spreadhseat named - CommsMASTER.xlsm thats runs the following macro;



Sub ping08()

For Each tmpCell In Range("D2:D1027").SpecialCells(2, 2)

If InStr(CreateObject("WScript.Shell").Exec("%comspec % /c Ping -4 -n1 -w 750 " & tmpCell).StdOut.ReadAll, "TTL=") Then

tmpCell.Offset(0, 3) = "OK"

Else

tmpCell.Offset(0, 3) = "Failed"


End If

Next

End Sub


This VB script basically pings a number of IP address and records the results as follows;

C D E F G H I J K
Site No. IntIP ExtIP Name 08:00 09:00 10:00 11:00
91 1.1.1.1 1.1.1.1 ABBEY OK
53 2.2.2.2 2.2.2.2 MEADS Failed


I would like to automate the process so the script runs automatically every hour and then records the results

in each consequent/relevant cell. So next result at 9:00 in H2 and 10:00 in I2.

I have been looking at the Application.OnTime TimeValue("10:57:00"), "ping08" function but just cant anything

to work as I would like.

As shown above, I have over 1000 rows to go through. I will have a dedicated server that this could run on.

Any help would be VERY MUCH APPRECIATED!
 
Old March 3rd, 2013, 10:28 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Application.OnTime is a good method

http://vbadud.blogspot.in/2007/08/au...excel-vba.html

Use Now + TimeValue(time) in the procedure to set this recurrently.

On the other hand I would advise to create an Executable (using VB6 / VB2010 etc) and schedule the executable using Windows scheduler

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Schedule a VBA macro to run at a specific time marshall04b Excel VBA 8 October 31st, 2015 08:41 AM
Lesson 12 - Ping Pong Game won't run threads Scott.Ballenger BOOK: Java Programming 24-Hour Trainer by Yakov Fain 2 April 27th, 2013 10:21 PM
Application to run excel vba macro dyung2 Excel VBA 9 October 28th, 2009 10:57 AM
Schedule a VBA macro to run at a specific time peterlihh Other Programming Languages 0 October 24th, 2008 07:15 PM
VBA Macros not showing up in the run macro menu d.lee84 Access VBA 9 August 21st, 2007 09:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.