Category: VBS Scripts

Visual Basic Script that can be used to rename a file

Here I am trying to automatically load log files into a MS Access file. Log files are actually txt files which need their extension changed if like me you wish to automate their import into MS Access. The following Visual Basic Script takes a file called FS_LS.log and changes it to FS_LS.txt. (I have another function that then takes this file and imports it into an MS Access application.

For safety this script creates a copy of the FS_LS log and renames it with a txt extension combining hours and minutes into the title. Thus it needs to be run one minute apart otherwise it will throw an error.

For testing purposes I have the following line

Fso.Copyfile "C:\Users\mark\Documents\vbs\" & vardatefile, "C:\Users\mark\Documents\vbs\" & vardatefile3

If you don’t have a feed to some kind of constant stream of log files this will copy the txt back to a log file ready to be copied again.(something I found useful for testing). Now next you want to call this from within your MS Access application prior to your data import function using the previously posted function.

Dim Fso
Dim vardatefile
Dim vardatefile2
Dim vardatefile3
Dim varhoursleft
Dim varminsright
Dim varnow

varnow= FormatDateTime(now,4)
varhoursleft = left(varnow,2)
'msgbox varhoursleft

varminsright = right(varnow,2)
'msgbox varminsright
Set Fso = WScript.CreateObject("Scripting.FileSystemObject")
vardatefile2 = "FS_LS.txt"
vardatefile3 = "FS_LS.log"
vardatefile = "FSLS-" & varhoursleft & varminsright & ".txt"
Fso.MoveFile "C:\Users\Mark\Downloads\FS_LS.log", "C:\Users\Mark\Downloads\" & vardatefile
Fso.Copyfile "C:\Users\Mark\Downloads\" & vardatefile, "C:\Users\Mark\Downloads\" & vardatefile2
'Fso.Copyfile "C:\Users\Mark\Downloads\" & vardatefile, "C:\Users\Mark\Downloads\" & vardatefile3

Set FSO = nothing

Compact Database automatically using this MS Access Function and VB Script

If like me you sometimes need to run multiple SQL statements in MS Access on a regular basis maybe at a particular time some of which are deletes, you will need to find a way to automate regular compacts. Here’s some code scavenged from the interweb that will allow you to do this.

Firstly create the following function in your target MS Access database.

Public Function CompactDatabase()
      Dim vStatusBar As Variant
      DoCmd.SetWarnings False
            
          If FileLen(CurrentDb.Name) > 2000000 Then
              Application.SetOption ("Auto Compact"), 1
              Application.SetOption "Show Status Bar", True
              vStatusBar = SysCmd(acSysCmdSetStatus, "The application will be compacted on close during compaction please do not interrupt")
          Else
              Application.SetOption ("Auto Compact"), 0
          End If
                
End Function

Next open up notepad paste in the following code save it as a txt file and then in explorer edit the suffix to vbs. Then simply double click on the file within explorer to run it. You should see the access database you have identified in the VB script open and then immediately close at which point it carries out the compact. The eagle eyed will have spotted that the above function doesn’t actually perform a compact. All it does is set the compact on close option to True within Access settings. The VB Script then instructs the database to close and Access compacts the database on exit.

set oAccess = createobject("Access.Application")

oAccess.OpenCurrentDatabase "C:Path\TargetDatabaseyouwantCompacted.mdb"
oAccess.visible = true
wscript.sleep 1000
oAccess.Run "CompactDatabase"
oAccess.closecurrentdatabase

set oAccess = nothing

A VB script such as this could be used to remotely run any MS Access function or functions – simply substitute the “CompactDatabase” parameter (or add further run commands) with the name(s) of the function(s) in the database you wish to trigger and then run the VB Script. Be warned the speed at which functions and commands within functions run may vary when called from a vbscript so you may have to use some experimentation to input things like pauses and waits if you start to get very imaginative with the functions run.

VBS – Pieces of code

Shutdown computer

Option Explicit
Dim oShell
Set oShell = Wscript.CreateObject("Wscript.Shell")
oShell.Run "SHUTDOWN -T 60 -S" 'wait 60 seconds before shutting down

Trigger speech

Option Explicit 
Dim speechobject
set speechobject=createobject("sapi.spvoice")
speechobject.speak "Your system is setup and ready for your day"

Delay for Seconds (10)

Option Explicit
Dim dteWait
dteWait = DateAdd("s", 10, Now())
Do Until (Now() > dteWait)
Loop

Sleep Function for Delay – Count in Milliseconds (5 mins below)

WScript.Sleep 5*60*1000

Using Task Scheduler and Visual Basic Scripts to automate pretty much anything Win 7

This is hardly ground breaking but windows Task Scheduler can be used to run VBS scripts on a variety of events to automate repetitive and boring tasks. Backups for example or refreshing caches on web browsers before users come in.

Hit Window button

Type “Task Scheduler”

Press return you should get the following screen.

Schedule1

Make sure Task Schedule Library is selected in the tree on the left then within Actions sub window on the right click Create

Schedule2

Give the Task a name in the area above marked with a red arrow and then go to the actions Tab where you can use the New… button to navigate to the vbs file you wish to run.

Schedule3

Next use the browse button OR if you know the path and file name simply type in the path to your visual basic script you would like to run. Here I have put in an imaginary backup script. Next go to the Trigger tab and again hit the New… tab

The Begin Task selection list gives a good indication of when you want to trigger whatever tasks.

Schedule4

If we continue with the theme and run a vbs script to do a backup at 11:48 everyday

Schedule5

Start up VBS

OPTION EXPLICIT
 
Dim WshShell
Dim counter
Dim shell
Dim Outlook
Dim objWord
Dim objshell
 
'Flash keyboard to show code is being hit
 
Set WshShell = CreateObject( "WScript.Shell" )
counter = 0
While counter < 18
                WshShell.SendKeys "{NUMLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{NUMLOCK}"
                WshShell.SendKeys "{CAPSLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{CAPSLOCK}"
                WshShell.SendKeys "{SCROLLLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{SCROLLLOCK}"
                WshShell.SendKeys "{CAPSLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{CAPSLOCK}"
                WshShell.SendKeys "{CAPSLOCK}"
                counter = counter + 1
WEnd
 
'It is not possible to poll the state of the keyboard using VBS but strangely if you use vb through word therefore it is necessary to open word turn caps lock check on the status and if caps locks is on turn them off
 
Set objShell = CreateObject("WScript.Shell")
Set objWord = CreateObject( "Word.Application" )
 
if objWord.CapsLock <> 0 then
   ' turn capslock off
   objShell.SendKeys "{capslock}"
 
end if
 
if objWord.NumLock = 0 then
'turn numlock on
objShell.SendKeys "{numlock}"
 
end if
 
'This line required to delay Windows long enough to allow the computer to open word - if it trys to quite word before fully open you will get an error depending on your machine speed may be better to use the actual seconds
WScript.Sleep(100)
objWord.Quit
 
'Startup MS Outlook
 
CONST PATH_TO_OUTLOOK = """C:\Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE"""
CONST SHOW_NORMAL = 3
CONST MINIMIZE = 1
 
SET shell = WScript.CreateObject("WScript.Shell")
 
' Open Outlook
shell.Run PATH_TO_OUTLOOK, 2
 
ON ERROR RESUME NEXT
 
' Grab a handle to the Outlook Application and minimize
SET outlook = WScript.CreateObject("Outlook.Application")
WScript.Sleep(100)
outlook.ActiveExplorer.WindowState = SHOW_NORMAL
 
' Loop on error to account for slow startup in which case the
' process and/or the main Outlook window is not available
WHILE Err.Number <> 0
  Err.Clear
  WScript.Sleep(100)
  SET outlook = NOTHING
  SET outlook = WScript.CreateObject("Outlook.Application")
WEND
 
ON ERROR GOTO 0
 
SET outlook = NOTHING
SET shell = NOTHING

Rolling VBS Script

Want to backup files and don’t have a fancy Database Administrator or sysadmin on 40k that knows his way around SQL Server 08R2 and MS Access , windows server and fully complies with continuous backup?

This might prove useful.

With any of the standard bespoke internally created applications that I am responsible for I like to have three main copies or parts.

1.a Back End
2.a Development copy front end – (this is used to adjust and create runtimes )
3.a Front end run time

Of the above two I backup both the back-end and the development copy of the front end. I do not back up run times (No.3), personally if I have the others backed up I can re-create a run-time from whatever point I desire. If for some reason I can’t create a run-time from a development copy it is time to roll back on the development copy – this should exist because of this very post.

In terms of the two types of copies I consider both equally important. Back-ends are easy to create but very difficult to re-create increasing in value with time. Front ends have no value in the data but take an extra ordinary amount of time to figure out and likewise are very awkward to recreate increasing in value with time. Both are digital and can be copied multiple times often with little to no cost (size dependent for back ends). Many front ends are actually tiny but their value comes from the way in which things flow and the speed with which they execute.

So I backup both to date I’ve had some brushes with almost disaster but never actually lost an application. Yes there have been hiccups mainly due to my own error – getting confused with versioning and deleting the most up to date version rather than yesterdays version. Forgetting a password on an encrypted USB and having it wipe itself requiring recovery from backup – that kind of thing annoying but not really a problem. I now have a daily backup routine that at least means that I cannot really loose more than a days work. I use it all the time.

Option Explicit
Dim FSO
Dim vardatefile
Dim varmonthfile
Dim BDayFilePath
Dim BMonthFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "YourDatabaseBackEndCopy-Weekday-" & day(varnow) & ".accdb"
varmonthfile = "YourDatabaseEndCopy-Month-" & Month(varnow) & ".accdb"
BDayFilePath = "C:\" & vardatefile
BMonthFilePath = "C:\" & varmonthfile

FSO.CopyFile "C:\DatabaseTarget.accdb", BDayFilePath, "True"
FSO.CopyFile "C:\DatabaseTarget.accdb", BMonthFilePath, "True"
Set FSO = nothing

msgbox "Backup Complete" ,0, "Backup Script"

Save in simple word editor and change to vbs suffix , double click to run.
Note I have made this Option Explicit which is good practice although it is entirely possible to remove Option explicit and dynamically set the variables.