VB Script to get the Tab names of the excel workbook

Have you ever wanted to get all the tab names in an excel workbook. I had an experience, i wanted to confirm i have included checklist for all the functionality in the current build, i had 34 tabs in the workbook, and i couldn't find any excel functions for that so created this script

'Create excel object
Set objExcel = CreateObject("Excel.Application")
' Open the excel sheet with the object, providing the file path
Set objWorkbook = objExcel.Workbooks.Open ("C:\MyFolder\Summary of Test Scripts.xlsx")
'Find the count of excelsheets and iterate through the worksheet and find the name 'and store ina variable

for i=1 to objExcel.activeworkbook.sheets.count
mystr=objExcel.activeworkbook.sheets(i).name
mystr1= mystr1 + trim(mystr) + vbcrlf
'msgbox i & " " & mystr
Next

objExcel.Quit
Set objExcel =NOTHING
Set objWorkbook =NOTHING
'create filesystem object to write to a text file
Set myFSO = CreateObject("Scripting.FileSystemObject")
'open textfile, provide the name
Set WriteStr = myFSO.OpenTextFile("Tabname.txt", 8, True)
'write the info to the file
WriteStr.WriteLine mystr1
WriteStr.Close
'close all the objects created
SET WriteStuff = NOTHING
SET myFSO = NOTHING

wscript.echo "Done"

This article can be found at the blog section of my personal site www.dileepk.info

Delete backup files n days older

Delete backup files n days older

Its the continuation of the last post, if you don't want to keep all the backup files, you want to store only "n" days back up and delete the older directories/ backup files. The vb script given below can do it.




' if you are going to run the script from a specific location, you can give as the value for Folderspec eg:- Folderspec="D:\BackUp"

Folderspec=""

Dim fs, f, f1, fc, s, sCurPath ,strDirectory, curDt,DelCount

'Current Date
CurDt=date()

DelCount=0

'Check any path is given with the call else finds the current directory
if len(folderspec)=0 then
'Find the current Directory
sCurPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
else
sCurPath=folderspec
end if

'Create file system object
Set fs = CreateObject("Scripting.FileSystemObject")

'selects the parent folder
Set f = fs.GetFolder(sCurPath)

'select the subfolders
Set fc = f.SubFolders

' looping through the directory contents

For Each f1 in fc

s = f1.name

' replace _ with / to convert to valid date

strDirectory = replace(s,"_","/")

'Check the date diffenrnce if it is greater than 3 (more than 3 days older) delete the folder

if DateDiff("d",strDirectory,CurDt) >3 then
DelCount=Delcount+1
fs.DeleteFolder(sCurPath & "\" & s)

end If

Next

wscript.echo "Total " & DelCount & " folders deleted from "& sCurPath


This article can be found at the blog section of my personal site www.dileepk.info

VB Script to backUp

Hi,

Do you come across any scenario to do regular backup of certain files which are updating regularly and need to put it in a folder with the current date. Herez the simple vb script for that. Copy and paste the below code to any editor and save it as vbs file and run it.



'Declare variables
Dim sCurPath,Fname,strDirectory,objFolder

' Finds the current directory

sCurPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")

'Replace the / with _ in the date for the valid directory name and

strDirectory = sCurPath & "\" & replace(date,"/","_")

'Create a file system object

Set fso = CreateObject("Scripting.FileSystemObject")

' check whether the directory exists, if not create the directory

if not fso.FolderExists(strDirectory) then

' Crete the directory on the current date

Set objFolder = fso.CreateFolder(strDirectory)
end if

' This copies the files and overwrites them if they exist. You can edit to copy any file type.


fso.CopyFile sCurPath & "\*.txt", strDirectory & "\", OverwriteExisting

wscript.echo "Done"


This article can be found at the blog section of my personal site www.dileepk.info