I have a directory of .xls workbooks with the following naming convention:
001.WIP Monthly Report 002.WIP Joes Custom Report ... 129.PUR Supplier Spend
The number of worksheets in each workbook varies but each worksheet is formatted the same way.
Row 5 holds column headers and rows 6 and beyond hold data:
A B C D 4| | | | ... 5| Org | Project | Task | ... 6| 023 | XYZ | 01304 | ... 7| 010 | ABC | 26453 | ... 8| ... | ... | ... | ...
My goal was to write a script that loops through every workbook in the directory, then loops through their respective worksheets and documents the column headers that they contain.
A sample output of this script would be something like:
A B C D E F 1| | | Org | Project | Task | ... 2| 001 | Sheet1 | X | | X | ... 3| 001 | Sheet2 | X | X | | ... 4| 002 | Sheet1 | | | X | ... 5| ... | ... | ... | ... | ... | ... 6| 129 | Sheet8 | X | X | X | ..
where column A is the first 3 digits of the workbook name, B is the worksheet name, and C and beyond contain the column headers and an X documenting whether or not that worksheet contains that column header. Also, if a worksheet started with "SQL" or "---" I wanted it to be ignored.
Here is the script:
#a function I found online for practicing good hygiene
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# -----------------------------------------------------
#open up a new instance of excel
$xl = new-object -comobject excel.application
$xl.Visible = $True
$xl.DisplayAlerts = $False
#open up a blank workbook that already exists on the desktop
#I refer to this as the $master workbook
$master = $xl.Workbooks.Open("c:\Users\me\desktop\master.xlsx")
$mws = $master.Worksheets.Item(1)
#initialize the row and column counters for the $master workbook
$c = 2
$r = 1
#specify the directory of workbooks to be analyzed
$files = dir("c:\Users\me\desktop\exports\*.xls")
#loop through the workbooks in the directory
foreach ($f In $files)
{
$wb = $xl.Workbooks.Open($f.FullName)
#loop through the worksheets in the current workbook
for ($i = 1; $i -le $wb.Sheets.count; $i++)
{
#if the first three characters of the worksheet are "SQL" or "---" then continue
#else record the first three digits of the workbook name and the worksheet name
$ws = $wb.Worksheets.Item($i)
$wsns = $ws.Name.Substring(0,3)
if ( $wsns -eq "SQL" )
{
continue
}
elseif ( $wsns -eq "---" )
{
continue
}
else
{
$r++
$mws.Cells.Item($r,1) = $wb.Name.Substring(0,3)
$mws.Cells.Item($r,2) = $ws.Name
}
#for each column in the worksheet, get the cell value in row 5.
#if the cell value is blank, then go to the next cell.
#if 4 cells in a row are blank then break and go to the next worksheet.
#we need to let 4 blank cells go by due to the formatting of some of the workbooks to be analyzed
#I picked 200 iterations because none of the workbooks being analyzed should have more columns than that.
$blnk = 0
for ($z = 1; $z -le 200; $z++)
{
$v = $ws.Cells.Item(5,$z).Value()
if ( $v -eq $Null )
{
$blnk++
if ($blnk -eq 4)
{
break
}
continue
}
#compare the current value of $v to all the previous values of $v that have already been stored in row 1 of the $master workbook
#I picked 5000 iterations arbitrarily. There should definitely not be this many columns by the end of the script execution.
for ($x = 3; $x -le 5000; $x++)
{
$mwsv = $mws.Cells.Item(1,$x).Value()
#if the value of $v matches the value of a cell in row 1 of the $master workbook then put an x in that column instead of creating a new column
if ( $mwsv -eq $v )
{
$mws.Cells.Item($r,$x) = "x"
break
}
#elseif $mwsv is blank then we have hit the end of list without finding a matching column
#we should create a new column and mark an "x" in the row for the current sheet.
elseif ( $mwsv -eq $Null )
{
$c++
$mws.Cells.Item(1,$c) = $v
$mws.Cells.Item($r,$c) = "x"
}
}
}
}
#close the current workbook
$wb.Close($False)
Release-Ref $wb
}
#save the $master workbook and quit excel
$master.Save()
Release-Ref $mws
Release-Ref $master
$xl.Quit()
Release-Ref $xl
The script did exactly what I wanted it to but it took forever. It found 1518 unique column headers for 330 worksheets in 129 workbooks but it took a day and a half to do so. Is there any optimization I can make to this script? Are there alternatives to Powershell that would be much faster?
I ran this script on a Dell Latitude with Windows 7 Pro, Intel Core i5-2540M 2.6GHz, 4GB RAM. CPU usage while the script was running was about 40%-70%.
