Excel VBA: Delete all worksheets except for specific ones you want to retain

Excel VBA code example: How to delete all worksheets except for specific ones ones you want to retain.

This came from real world need today so I pounded this code out.

  • I have a list of worksheets that I want to keep. 
  • I run a process that adds analyzed data from another file to a new “temp” worksheet (tab), and additional worksheets per vendor from parsing out the imported data in the temp worksheet.
  • I can end up with 10 new tabs of analyzed data including the temp sheet.
  • The code below deletes all worksheets not on a “keep sheet list” . Basically, resetting the spreadsheet back to the state it was in before I ran macro to import and process that data that added all the new sheets.

Excel VBA Example: ResetWorkbook Function

'Reset Workbook
'Author Rick Cable
'Date: 11/28/2018
'Title: ResetWorkbook
'Purpose:   Deletes any workbooks not on the keep sheet list,
'           resetting it to previous state if you've added them
'           from some other process and want to undo
'Version 1.0
Public Sub ResetWorkbook()
    Dim sht As Worksheet
    Dim arrKeepSheetList As Variant
    Dim strKeepSheetList As Variant
    Dim isOnList As Boolean
        
    Application.DisplayAlerts = False
    isOnList = False
    strKeepSheetList = "Instructions,Template,Sample CSV File,Data Elements,Config"
    arrKeepSheetList = Split(strKeepSheetList, ",")
    
    'Loop through each worksheet in ActiveWorkbook
     For Each sht In ActiveWorkbook.Worksheets
        isOnList = False
        'Look thru list of sheets to keep
        'if sheet is not on list, delete it
        For Each Item In arrKeepSheetList
            If sht.Name = Item Then
                isOnList = True
                'If isOnList Then
                '    Exit For
                'End If
            End If
        Next Item
            
        If isOnList Then
             'MsgBox (sht.Name & " is on the list")
        End If            
        
        If isOnList = False Then
            'MsgBox ("I would be deleting " & sht.Name & " right now!")
            Worksheets(sht.Name).Delete
        End If
        
    Next sht

    Application.DisplayAlerts = True

End Sub

VBScript – How to Sort Folders using VB’s FileSystemObject and an ADODB Recordset

This is an example of how to sort a list of folders using VBScript.

The FileSystemObject in Visual Basic does not have a built in sorting method. #FrownyFace

I wanted to have a function that could do this a painlessly as possible. I searched around and found a good example but it needed some tweaking as it was originally written for running in an Classic ASP page.

I started with sample code from the site link below. Posted by “Thing”

https://www.sitepoint.com/forums/showthread.php?330636-Sorting-Files-using-FileSystemObject

It uses the FileSystemObject but also uses an adhoc ADODB Recordset to store the folder names then we use the recordset to sort then output the list on the order we’ve chosen.

Simple and effective code.

I’ve converted it to run on any Standard Windows PC.

Enjoy!

VBScript Code Example

Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorLocation = 3 ' adUseClient
objRS.Fields.Append "Name", 200, 50 ' adVarChar

objRS.Fields.Append "created", 7 ' adDate
objRS.Open

imagespath = "C:\scripts"
Dim FSObj, FOlderObj, FileCollObj

Set FSObj = CreateObject("Scripting.FileSystemObject")
Set FolderObj = FSObj.GetFolder(imagespath)
Set FolderCollObj = FolderObj.SubFolders

For Each item in FolderCollObj
    Set SubFolderObj = FSObj.GetFolder(FolderObj & "/" & item.name)
    objRS.AddNew
    objRS("Name") = item.name
    objRS("created") = SubFolderObj.DateCreated
Next

objRS.Sort = "created ASC"
objRS.MoveFirst

Do UNTIL objRS.EOF  
    msgbox "Folder Name=" & objRS(0)    
    objRS.movenext
Loop

For Loops for Beginners – ASP, C#, PHP, JavaScript & Python Examples

A “For Loop” executes a block of code a specific number of times or while a specified condition is true.

PHP For Loop

for (init; condition; increment)
  {
  code to be executed;
  }

php fOR lOOP PARAMETERS

  • init: Mostly used to set a counter (but can be any code to be executed once at the beginning of the loop)
  • condition: Evaluated for each loop iteration. If it evaluates to TRUE, the loop continues. If it evaluates to FALSE, the loop ends.
  • increment: Mostly used to increment a counter (but can be any code to be executed at the end of the iteration)

Note: The init and increment parameters above can be empty or have multiple expressions (separated by commas). Example The example below defines a loop that starts with i=1. The loop will continue to run as long as the variable i is less than, or equal to 5. The variable i will increase by 1 each time the loop runs:

PHP For Loop Example Code

<?php
for ($i=1; $i<=5; $i++)
  {
  echo("The number is " . $i . "<br>");
  }
?>

Classic ASP For Loop Example Code

<%
For i = 1 to 5
 Response.Write("The number is " & i & "<br>")
Next
%>

JavaScript For Loop Example

<%
For i = 1 to 5
 Response.Write("The number is " & i & "<br>")
Next
%>

C# For Loop Example

for (int i = 0; i < 5; i++) 
      {
        Console.WriteLine(i);
      }    

Python For Loop Example

states = ["Alaska", "Alabama", "Arkansas"]
for x in states:
  print(x) 
  if x == "Alabama":
    break

VBScript – Adding Days and Months to a Date Using the DateAdd Function

VBScript has a built in function called “DateAdd” that specifically deals with adding or subtracting when dealing with a date/time variable.

To use it you will need to know the syntax and parameters that it needs to make the magic happen.

For my example I’m going to get the current date and time of the system by calling the “Now()” function which grabs the date and time from the PC the script is being run on.

To add month to today’s date:

DateAdd("m",3, Now())

Since I wrote this code in March it should return a numeric “6” for June.  To spell out the month you would have to due some more work and maybe I’ll cover that in another post later.  The important thing to remember when working with dates and times using DateAdd, all your output will be numeric.

Calling the DateAdd Function in VBScript

The VBScript function, DateAdd, needs a couple of parameters separated by commas to make it work.

1. DateAdd needs to know the “Interval” parameter which is the part of the date your are wanting to work with.  In the sample above I used “m” to tell it that I want to add to the month. 

HINT: Don’t forget to wrap interval parameter text in double quotes.

2. DateAdd needs the number you want to add to the date and in my sample I used “3”. If you want to get a previous month then simply give DateAdd a negative number.

3. DateAdd needs to know what date you are working with. In my sample I used Now() to the current system date/time. You could have passed the date via a variable.

Where I had used the string value “m” to add to the month, you only have to change to interval parameter to change the year, month, day, hour, minute or second.  Here is a list of all the interval parameters you can pass to the DateAdd function:

  • yyyy – Year
  • q – Quarter
  • m – Month
  • y – Day of year
  • d – Day
  • w – Weekday
  • ww – Week of year
  • h – Hour
  • n – Minute
  • s – Second

I know for some people the DateAdd function is like preschool but for people who are new to VBScript I know it helps a lot if someone stops to explain the how and why of how this stuff works.

Good luck and happy coding!
Rick

VBScript: Adding Leading Zeros to a Date

I was recently developing a script to loop through an excel file and write the contents in to a text file.  One of the specifications was that the output dates had to have leading zeros like 01/01/2021 but the excel file had them as 1/1/2021.

I thought there must a VBScript function like the built in CDate for or FormatDateTime but neither of these seemed to return dates with leading zeros so I wrote my own function to do it.

As in all programing, there are many ways to write this to get the same output but this way worked for me on the project I used it for.  

VBScript FixDate Function to fix leading zeros in a date value

Function FixDate(strDate)
Dim iTemp, arrDate, item, strTemp

 ' my custom date fix function
 ' split the date in to an array by the "/" character
 ' check each date item and check to see if it is less than 1000
 arrDate = Split(strDate,"/")
 for each item in arrDate
  if len(item) < 2 then
   item = "0" & item
  end if
  
  ' Next section makes sure there is no / at the end of the rebuilt date when I put it back together.
  if item < 100 then
   strTemp = strTemp & item & "/"
  else
   strTemp = strTemp & item
  end if
 Next
 ' Put the date back together
 FixDate = strTemp
 
End Function

How to call this function

FixDate("1/1/2011") 
 or 
FixDate(YourStringDate) 


List all USB Devices on a Window PC using VBScript

I’m going through some old scripts I’ve written or stolen from my years as a solutions engineer / developer and have come across one I thought was worthy of sharing.

The intent of this script was to loop through a list of USB devices found on a given PC.

I might store the results in a database as part of an device tracking inventory system or use the data to do something else in the code logic if I had found a specific USB device attached.

Example: VBScript to List all USB Devices

Get the code here on GitHub

'http://blogs.technet.com/b/heyscriptingguy/archive/2005/03/15/how-can-i-determine-which-usb-devices-are-connected-to-a-computer.aspx
'Win32_PnPEntity info here: http://msdn.microsoft.com/en-us/library/aa394353(v=vs.85).aspx 
strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colDevices = objWMIService.ExecQuery _
    ("Select * From Win32_USBControllerDevice")

For Each objDevice in colDevices
    strDeviceName = objDevice.Dependent
    strQuotes = Chr(34)
    strDeviceName = Replace(strDeviceName, strQuotes, "")
    arrDeviceNames = Split(strDeviceName, "=")
    strDeviceName = arrDeviceNames(1)
    Set colUSBDevices = objWMIService.ExecQuery _
        ("Select * From Win32_PnPEntity Where DeviceID = '" & strDeviceName & "'")
    For Each objUSBDevice in colUSBDevices
        Wscript.Echo objUSBDevice.Description & ": " & objUSBDevice.Manufacturer
		
    Next    
Next

If you run this script on a remote computer on a network domain instead of your own, you will probably have to run this script with Administrator rights depending on the who the network is configured.

You can check out my article on how to run VBScript as an administrator if you need help with that.

Hope this helps somebody! 🙂

~Cyber Abyss

Subversion (SVN) error: E120106: ra_serf: The server sent a truncated HTTP response body – Automate the command line workaround with VBScript

SVN error: ra_serf: The server sent a truncated HTTP response body.

While working on a large enterprise project, I’m getting a Subversion (SVN) error “SVN error: ra_serf: The server sent a truncated HTTP response body.” when updating a repository to a network drive.

I found part of the answer on a Stack Overflow post about SVN error: E120106: ra_serf: The server sent a truncated HTTP response body.

This work around assumes the SVN checkout folder is already present provided was a two SVN command line commands.

$ svn cleanup
$ svn up

I ran the SVN command line commands manually for days but still the huge project updates would hang after about 5-10 minutes.

I decided I would try to automate this process a little bit to see if I could run the commands via a script and just repeat the cleanup and update commands every time it hung or stopped until the Subversion (SVN) update process has successfully completed.

Automating the SVN Cleanup and Update Commands in a VBScript.

Below is the script I just wrote today to try and automate the SVN processes.

  • Create Windows Shell
  • Use Shell and Run method calling taskkill command to kill any SVN processes that might be hung.
  • Use Shell and Run SVN with the “cleanup” w/ path argument to identify the SVN Repository folder we are targeting.
  • Use Shell and Run SVN with the update command, “up”, with path to identify the SVN Repository folder we are targeting.

The VBScript Code

Dim oShell : Set Oshell = CreateObject("WScript.Shell")

For i = 1 to 3
    OShell.Run "taskkill /im svn.exe /F", 5, True
    OShell.Run """C:\Program Files\VisualSVN Server\bin\svn.exe"" cleanup H:\Repo\", 1, True
    OShell.Run """C:\Program Files\VisualSVN Server\bin\svn.exe"" up H:\Repo\", 1, True
Next
Set OShell = Nothing

SVN Error: E120106 in a Nutshell

In summary, the SVN error E120106 for truncated HTTP response body is coming from the SVN server.

Everyone on this team has this issue and checkout code repository to their local C drives to checkout and commit work.

I suspect our network is just slow enough to cause a hiccup in communications between HTTP on SVN repository side and Windows file system.

I hope this work around and simple explanation help somebody out there with the same issue. 🙂

~Cyber Abyss

How to Run Visual Basic Scripts as Admin from the Command Line. VBScript Tips and Tricks!

I’ve been using Visual Basic, VBScript, for many years and have used VBScript for a great many things.

I’ve found a lot of value in VBScript over the course of my long IT career. I’ve even saved my employer well over $1,000,000 with a fairly simple VBScript in one instance. Got employee of the month for that one!

If you’re looking for ways to run vbs from cmd, here are 3 ways you can run VBScript files on a Windows PC including running VBScript files as an Administrator via the command line.

3 Methods of Running VBScripts

  1. Run VBSCript in Windows Explorer
  2. Run VBScript from Windows Command Line
  3. Run VBScript as a Windows Administrator

For more VBScript info, try these excellent VBScript resources:

1. Run VBScript in Windows Explorer

  • Locate the VBScript file using Windows Explorer or place a copy on your Desktop
  • Identify VBScript files by the .vbs file extension
VBScript files in Windows Explorer
  • Double click the VBScript file.
  • You may or may not see anything depending on the program’s design.
  • To check to see if VBScript is running.
  • Run Windows task Keys Ctrl+Shift+Esc then sort by Image Name. Look for “wscript.exe”.
  • To kill a VBScript process, right click over “wscript.exe” and select “end process”.
  • You may see more than one “wscript.exe” on the task manager list as it can be run multiple times and new instances will execute.

2. Run VBScript from Windows Command Line

  • Click Windows Start
  • Open Command Window by Typing “CMD” and hit Enter
CMD exe from Windows Start Menu
  • Enter the full file path at the Command Line and hit Enter

3. Run vbs from cmd: Run VBScript as a Windows Administrator

Create a Windows Batch File that uses WScript.exe to open the .vbs file (See example below)

Note this for reference. There is another script host file called, CScript.exe, that can also execute VBScript files.

CScript.exe is in the same directory as WScript.exe so if you need to reference it, just replace by name in the examples below.

If I recall correctly, use CScript.exe if something the VBScript has to do something that is 64 bit OS related with the Windows Meta (WMI).

Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. 

Right click and select option to Run Windows Batch file as Admin and Admin Rights will pass through to the VBScript

Example Windows Batch File to RUN VBS from the COMMAND Line

Save the file below with a .cmd file extension. Then right click on it and select “Run as Admin”. Admin rights will be passed on to the VBScript

You can download an example from my GitHub repo.

@echo off
pushd %~dp0
C:\Windows\System32\WScript.exe "My_VBScript.vbs"

How to Concatenate a String using VBScript

How to Concatenate a String Value in VBScript

To concatenate string values using VBScript we use the ampersand (&) character to piece together our composite string value.

Code Example:

Srting1 = "Word 1"
String2 = "Word 2"
String3 = String1 & String2
or 
String3 = "Word 1" & "Word 2"
msgbox(String3)

The output value would be “Word 1 Word 2”.

If you’re more familiar with JavaScript, you might be used to concatenating strings using the plus character (+).

In VBScript you can only use the + operator on numeric values.

Classic ASP Sleep Function or How to Delay a HTTP Response in Classic ASP – Explanation & Alternative Solutions

Is There a Sleep Method in Classic ASP?

First, and sorry, there is no built in sleep method in classic ASP. Probably for good reason though. Keep reading for background on why and I’ll offer some possible alternatives.

VBScript Sleep Function Code Example

Normally, when I think of a sleep function, I think of the built-in VBScript Sleep Function where we can set a delay in seconds to pause some code in a function.

WScript.Sleep(5000)
WScript.Echo("5 seconds have passed.")

What is Classic ASP?

Classic ASP is VBScript interpreted rather than compiled on the IIS web server then converted into 100% HTML before it is delivered to the client / web browser in a HTTP call.

I’ll emphasize, with Classic ASP, the client never sees the underlying VBScript, only rendered HTML.

The Refresh Meta Tag / Wait vs. Sleep

An alterative to “Sleep” might be delay or wait. We can use the Meta Tag, “Refresh”, if you just need a web page to wait for a number of seconds before refreshing which includes redirecting the page on refresh to another URL.

You would include the meta refresh tag inside the web page’s head tags as shown in the code example below.

Meta Refresh Tag Example Code

<html>
    <head>
        <title>Meta Tag Refresh Example</title>
        <meta http-equiv="refresh" content="5"; url="Test.html" />  
    </head>
    <body>
    </body>
<html>

Custom ASP Sleep Function Alternative

As a prerequisite, I can’t imagine why you would want to delay a Classic ASP page from being served to a user’s web browser for 10 seconds. That’s a long time to make a user wait but you can do anything with code.

I will warn you, if you cause the HTTP Response to delay for a User Agent like Google bot, Google will probably exclude your website from their search indexes so I typically would not do this in practice on a website that needed any kind of Search Engine Optimization (SEO) friendliness.

DIY Classic ASP Sleep Function?

With Classic ASP, since we don’t have a native sleep or delay method, we can just build our own. By default, I’m going to stay with a delay of specific number of seconds as our end goal.

I’m sure we can come up with a few ways to solve for this but this solution is mine.

Building the Sleep Function from Scratch

  • We will set some variables for a start time and a current time.
  • Then start a While Loop that watches for # of seconds we’ve chosen.
  • We update the current time at each iteration of the loop and check it at start of each loop iteration.
  • Once current time increments by 10 seconds, loop completes giving you a delay of specified seconds.

Classic ASP Sleep Function Code

<%
Sub Delay(intSeconds)
	StartTime = Now()
	CurrentTime = Now()
	While DateDiff("s",StartTime,CurrentTime) < intSeconds
		CurrentTime = Now()
	Wend
End Sub

Response.Write("Something<br>")
call Delay(10)
Response.Write("Something 10 seconds later")
%>

Another reason I would not recommend this approach is that we are tying up the CPU while running this loop waiting for the time to change. Making this more of a weapon than a tool.

From a bad guy perspective, if you could get this code loaded and running on multiple pages with lots of traffic you could really degrade the performance of the server.

I hope this helps you if you were looking for a simple Classic ASP HTTP Response delay function but be careful how you use it.

Hope this helps you in your search for a VBScript Sleep Function.

~Cyber Abyss