Category Archives: MS Office

Schedule VBA Macros to run periodically via VBscript

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Some time ago I posted an article on how to run an Excel VBA Macro using VBscript and cmd to save time or to be able to run multiple Macros across a number of Excel files without actually needing to open them. Well, I decided to extend this example to allow periodic execution of Excel Macros e.g. in case your macros have to carry out some periodic tasks like load data into a database etc.

How to schedule VBA Macros to run automatically?

The solution is quite similar to the one posted on my previous article with one minor modification…

What we need to do is execute our VBA Macro (can be Excel, Access etc.) from a VBScript script file. To make sure it runs automatically we need to schedule it to run from startup by placing it in the Windows Startup folder. Follow the steps below to setup the script.

Create VBScript to run Excel periodically

First create an empty *.vbs file and input the following code snippet:

RunMacro
Sub RunMacro() 
  dim xl,path,xlBook
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Book1.xlsm", 0, True)      
  xl.Application.Visible = False
  xl.DisplayAlerts = False     
  xl.Application.run "Book1.xlsm!Module.MyMacro"
  xl.ActiveWindow.close
  Set xlBook = Nothing
  xl.Quit 
  Set xl = Nothing
  WScript.Sleep(5000)'New line: Sleep for 5 seconds
  RunMacro'New line: Run the Macro again
End Sub 

Replace the highlighted rows with your Workbook name and VBA Macro name.

What does the VBScript do?
So basically what it does is:

  1. Executes the Module.MyMacro VBA Macro in Workbook Book1.xlsm which is situated in the same folder
  2. Sleeps for 5 seconds
  3. Repeat

Add the VBScript to Startup

Schedule VBA: Add VBScript to Startup folder
Add VBScript to Startup folder
To make sure the Macro will run periodically and not require any manual touch, you may want to add it to your Startup folder. This will ensure that the script will start running as soon as you turn on your workstation and will stop as soon as you close.

Turn Off the periodic VBA macro

Schedule VBA: Turn off VBA MacroWhat if you want to turn the script at any moment in time? You might as well turn on the Windows Task Manager. There is an easier way. Just create the following *.bat file and run it when you want to close the periodic script:

taskkill /F /IM wscript.exe

Now you can create Excel Macros and use them to execute certain tasks at intervals automatically!

Automatic Updating of Excel Worksheet

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)
Loading...

Introducing the new extension to the Scrape HTML Add-In: Automatic Updating of Excel Worksheets. When configuring your Excel file to scrape data of HTML websites there is often the issue of how to refresh this data periodically. For example – you want to scrape stock price data and refresh it every 2 minutes. You can either do this manually, write some clever VBA using the Application.OnTime function or… simply easily configure the periodical refresh in the Automatic Update tool as part of the Add-In.

The Automatic Update tool

Automatic Update tool

So how does it work?

Configure the refresh rate for each worksheet

Automatic Update tool
Automatic Update tool

Click on the Automatic Updating button to see the list of worksheets and refresh rates. You can add any worksheet and configure the interval at which it should be updated. Then click “Off” to turn the updating on. And that is it! The tool will then refresh on the Get* functions (e.g. GetElementByRegex) on the interval.

No VBA required!

How to use this feature?

This will certainly be useful for users who:

  • Need fresh data – whether you are a stock broker, analyst in any other role. This will certainly make life easier
  • Are waiting for an important website update – maybe you are waiting for the new iPhone or want to sign up to a marathon with limited participation. This will also prove useful.
  • Want to make automatic reports – why not make a dashboard of reports based on web provided data? Make charts based on data all in one place w/o needing to constantly switch between websites to extract the information you need

See the video tutorial here:

Save and Get Excel VBA settings

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Ever had an issue of sharing data (VBA settings) between your workbooks? Not many Excel VBA users know that VBA allows you to easily save and certain variables to and from the Windows Registry, just as if setting permanent VBA settings within your VBA Project. What is more this is already built into VBA that is why you do not have to reference any external libraries or functions. When is this useful:

  • Saving VBA settings of Excel Add-Ins – when using some VBA variables that you would like to save permanently for the user, and you would like to be able to get them at any time. Usually this applies only to saving configurations.
  • Sharing VBA data between workbooks – say you have multiple Excel workbooks which share some simple data fields e.g. certain variables, settings etc. Saving them into the registry is sometimes more convenient especially if the location of all workbooks can change over time.
  • Sharing VBA data between Excel and other applications (MS Office but not only) – technically it should be possible to get these settings from any other MS Office application e.g. MS Access or MS Word.

Source code – Saving VBA Settings to registry

Global myVariable as Integer
Sub GetMyVariable
  myVariable = GetSetting("MyApplication", "MySettings", "myVariable", "0")
End Sub
Sub SaveMyVariable(newValue as Integer)
  call SaveSetting("MyApplication", "MySettings", "myVariable", newValue)
End Sub

Simple right?

See more information on Getting and Saving variables in the Windows Registry at http://msdn.microsoft.com/en-us/library/kb0c3wb9(v=vs.90).aspx

Excel VBA OnTime Function – Make a VBA Alarm Clock

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)
Loading...

One of the many useful features of Excel and other MS Office applications, of which little users know of is the VBA OnTime function. It allows you to execute any Excel VBA procedure after the specified amount of time (delay). Using it recursively allows you to create a recurring / repeating event which lets you schedule periodic VBA Macros to run when working on Excel Workbooks.

Application OnTime Syntax

The syntax of the VBA OnTime function:

EarliestTime
The time (Date variable) when the Procedure is to be executed

Procedure
A string with the name of the Procedure which is to be executed

LatestTime
Optional. This is the latest time when the Procedure should be executed. This allows Excel for some flexibility when prioritizing Procedure execution together with other Excel (or other MS Office applications) Events such as Cut, Copy or Find. It makes sense to use this variable when your user is working on the file, so as not to interrupt any activities

Schedule
Optional. Defaults to True. If True schedules a new OnTime procedure. If False clears a previously set procedure. Useful when you want to cancel a previously scheduled Procedure

Excel VBA Alarm Clock

The code below will schedule the HelloWorld Sub to run in 1 seconds.

From above the following are equivalent:

Wait 1 second and expect a Message Box to open with the message “Hello World!”.

Cancelling Application OnTime

You can also use the Application OnTime function to Cancel Scheduled Procedures.

Example 1: Automatic worksheet recalculate using OnTime

The Timer can come in handy when we need to refresh our worksheet(s) periodically and do not want to do this manually. See the example below:

Let us add this procedure in any VBA module within the workbook:

Next add this to the Workbook in the “Microsoft Excel Objects” section of the VBA project:

That is it! Once you open the workbook the whole workbook will be recalculated every 10 seconds. In order to stop it simply close the workbook or debug the Refresh procedure.

Example 2: Automatic workbook versioning using OnTime

Working on a large Excel solution and afraid that you workbook will crash? Want to have a separate backup copy of your file every now and then. Why not use automatic file versioning then?

Add this code to any VBA module:

Next replace the Refresh function with this:

Voila! Your workbook will be saved as a new version every 10 minutes. Great isn’t? There are probably a ton of ways you can further tweak this to work better e.g. add a pop-up asking to confirm the creation of the next version etc.

Hope that this will prove useful to you! Let me know!

Excel Scrape HTML Add-In now with HTML caching

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

I am constantly extending the Scrape HTML Add-In when I have some spare time. Today’s update adds Caching to the Get* functions.

See the latest version of the Excel Scrape HTML Add-In here:
Excel Scrape HTML Add-In

Why caching?

The primary concept of the Add-In is to reduce any need for writing VBA code. However, the problem with the Get* functions may be that if you specify multiple functions with the same URL there might be some overhead due to having to download the same HTML content just as many times – which might heavily impact performance. In order to solve this issue I have introduced the Cache functionality. If you specify the same URL in more than 1 Get* function then provided that the cache timeout has not passed the HTML content will not be refreshed from the Web but from the Cache. E.g. if you have 2 Get* functions with the same URL and a 60 sec timeout, then only the first call to the Get* function will download the content from the Web, whereas the second one will not – provided that the second call happens before the 60 sec timeout ends. You can change the setting at any time in the “Cache settings”.

Cache Settings
Cache Settings