“VBA? You wrote VBA code for that?”

exclaimed a colleague.

“Is that like Visual Basic?”

Well kind of, VBA is Visual Basic for Applications.  Which is a scripting language for the Microsoft Office system.

I’d had a problem where an extract of an asset report only had the “Last user” and “Last software scan date” on the first record for each computer listed.
I needed each line to have entries, as I was going to sort the data later.

Excel screenshot showing blank cells. but I wanted this: Excel screenshot showing cells filled in CopyCell macro

There was over 4000 records in the extract, so I couldn’t “Fill Down”.

I spent some time looking at Excel spreadsheet functions, particularly the IF function, and kept on running into issues.

So I wrote a VBA Macro.

Dim RowNumber As Integer
Dim ComputerNameColumn As Integer
Dim LastUserColumn As Integer
Dim LastSoftwareScanDateColumn As Integer

ComputerNameColumn = 1
LastUserNameColumn = 2
LastSoftwareScanDateColumn = 4

For RowNumber = 2 To 2324
‘ compare Computer Names, if same the current computer name is same as the last
‘ copy the previous “Last User, Last Software Scan Date” columns.
If Cells(RowNumber, ComputerNameColumn) = Cells(RowNumber – 1, ComputerNameColumn) Then
Cells(RowNumber, LastUserNameColumn) = Cells(RowNumber – 1, LastUserNameColumn)
Cells(RowNumber, LastSoftwareScanDateColumn) = Cells(RowNumber – 1, LastSoftwareScanDateColumn)

End If
Next

Yes, it is simple, and you can see it was written for one time use*.  But it worked.

You can download the sample spreadsheet here.

* – for persistent use, you might want to detect things like the ActiveSheet, the number of rows; and whether the “Last user/Last software scan date” cells are actually blank before you overwrite them.

Bookmark and Share

Get the logged on user – code which works!

Some VBscript code which ACTUALLY works. I don’t understand some sites which share code. You go looking for VBscript code on the net to do something, and invariably IT DOES NOT WORK.

For one of two reasons:

  • it’s a code snippet, and you need to write code around it to get it to work
  • it’s a code snippet, which has been copied from somewhere else, where it did not work either.

The VBscript I’ll post in the CodeWhichWorks category will work because:

  • I use it in a production environment, so I know it works
  • I use it in a production environment, and I use this code as a memory jogger.

 

GetLoggedOnUser

Get the username, user domain & computer name of the user & computer which is running the VBscript.

Message box showing user and computer details '*********************************************************
' NAME: GetLoggedOnUser
'
' AUTHOR: Dale Robinson (WISEFAQ.COM)
' DATE  : September 2008
'
' COMMENT: Get the logged on user.
'
' Version: 1.0
'*********************************************************


Option Explicit
On Error GoTo 0

Dim bGotUserDetails
Dim sUserName, sUserDomain, sDisplay_ComputerName

Call fn_GetLoggedOnUser(sUserName,sUserDomain,sDisplay_ComputerName, bGotUserDetails)

If bGotUserDetails Then
MsgBox "Username: " + sUserName + VbCrLf + "Domain: " + sUserDomain + VbCrLf + "Computer Name: " + sDisplay_ComputerName,,"User details"
End If

WScript.Quit

Function fn_GetLoggedOnUser(fnsUserName,fnsUserDomain,fnsComputerName, boolresult)
' get some common computer / user details.
Dim objNet, objWSHShell, objEnv
On Error Resume Next
'get the username and domain
Set objNet = CreateObject("WScript.NetWork")

If Err.Number <> 0 Then
boolresult = False
Else
boolresult = True
End If

   fnsUserName = objNet.UserName
fnsUserDomain = objNet.UserDomain

'get the computer name
Set objWSHShell = WScript.CreateObject("WScript.Shell")

   If Err.Number <> 0 Then
boolresult = False
End If

   Set objEnv = objWSHShell.Environment("Process")

If Err.Number <> 0 Then
boolresult = False
End If

fnsComputerName = objEnv("COMPUTERNAME")
Set objNet = Nothing
Set objWSHShell = Nothing
Set objEnv = Nothing
On Error GoTo 0
End Function

You can download a zipped copy of GetLoggedOnUser.vbs here.

Bookmark and Share

Logon scripts should always be error trapped.

Post from Whirlpool:

when some users log on they get the following message but it is ok for other users
VBScript 807010B error.

PostLogon.vbs file contents are below
_____________
Set WshShell = CreateObject(“WScript.Shell”)
CMDFile = “cscript %logonserver%\something$\logonXP.vbs PostLogon”
WshShell.Run CMDFile, 0, True
wscript.quit

can you please tell me which area i need to look at for fixing and get rid of this error?

Your help is highly appreciated.

There are a couple problems with this script:

  • there is an un-trapped error which a end-user gets to see.
  • WshShell is not set to Nothing when the script exists.
    sure wscript.quit should call a garbage collection, but it’s not good programming.
  • not capturing the result of the WshShell.Run CMDFile, 0, True statement.

Here was my suggested answer, which apparently helped:

(the area to look at) The directory path to logonXP.vbs for starters…

This vbscript code might help:
Set WshShell = CreateObject(“WScript.Shell”)
CMDFile = “cscript %logonserver%\something$\logonXP.vbs PostLogon”
On Error Resume Next
cmdfileresult = WshShell.Run (CMDFile, 0, True)
If cmdfileresult <> 0 Then
MsgBox CMDFile,,”Please tell the IT Guys”
End If
On Error GoTo 0
wscript.quit

Which produces this error message when there is a problem:

logon-script-error
(“Please tell the IT Guys” – is the important part)
Bookmark and Share

Customers like to (re)use the methods of the support team …

Link to Officer and Sergeants Powerpoint Slidedeck I was doing some code cutting the other day for a customer.

Indirectly.

I had sent a DOS batch file to Customer A, in response for an urgent fix request.  The DOS batch file enables Customer A to use a particular web application.

The Help Desk send me an email, "Customer B have this VBscript file which doesn’t work."

Customer A shared the DOS batch file Customer B, who thought they’d have a stab at re-writing it into VBscript.

VBscript file received from Customer B:

Set objWSHShell = WScript.CreateObject("WScript.Shell")
objWSHShell.Run ("dosomething.exe /commandlineparameters", 3, True)
objWSHShell.Run ("dosomething2.exe /commandlineparameters", 3, True)

Now there’s a few problems with this file:

  • no checking to see if dosomething.exe exists.
  • no checking to see if dosomething.exe & dosomething2.exe return an error.

Here’s my ruggerised, ready for deployment version:

Option Explicit
On Error GoTo 0Dim objWSHShell, objFSO
Dim sDoSomethingParameters, sDoSomethingFilePathName, sDoSomethingCommandString
Dim boolFileExists, boolResult
Dim intDoSomethingResult
Const intWindowStyleHideAndActivate = 0
sDoSomethingFilePathName = "C:\doer\DoSomething.exe"
boolResult = False
intDoSomethingResult = 555
Set objWSHShell = WScript.CreateObject("WScript.Shell")
Call fn_CheckIfFileExists(sDoSomethingFilePathName, boolFileExists)

If Not boolFileExists Then
   ' exit with File Does Not Exist
   WScript.Quit (2318)
End If

sDoSomethingParameters = " -q -a qwerty"
sDoSomethingCommandString = sDoSomethingFilePathName + sDoSomethingParameters

intDoSomethingResult = objWSHShell.Run (sDoSomethingCommandString, intWindowStyleHideAndActivate, True)

If intDoSomethingResult <> 0 Then
   ' execution of DoSomething and parameters failed.
   WScript.Quit (1708)
End If

' if we're gotten here, it's worked, and we can exit the script!
Wscript.quit (0)

~~~~~~~

Function fn_CheckIfFileExists(fnFilePathName, boolResult)
On Error Resume Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(fnFilePathName)
On Error GoTo 0
If objFSO.FileExists(fnFilePathName) Then
   boolResult = True
Else
   boolResult = False
End If

Set objFSO = Nothing
Set objFile = Nothing

End Function

I’m reminded of the maxim:
Customers like to (re)use the methods of the support team, but they don’t really understand them.

If you click on the picture, you’ll get to view the powerpoint file, where I saw the original maxim.

Bookmark and Share