Category: Excel VBA

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 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

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)