9
\$\begingroup\$

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

\$\endgroup\$
  • 1
    \$\begingroup\$ Perhaps this would be easier with a db? Even in MSAccess - just link all the tables (a lot of work with so many, but once it's done, it's done). \$\endgroup\$ – Gaffi Apr 18 '12 at 20:02
8
\$\begingroup\$

I know this question is a bit old, but I ran into it while looking for other Excel/PowerShell help and had recently found one potential fix. A two line change may actually speed up the entire process significantly.

Change from:

#loop through the worksheets in the current workbook
for ($i = 1; $i -le $wb.Sheets.count; $i++)

To:

#loop through the worksheets in the current workbook
$sheetCount = $wb.Sheets.Count
for ($i = 1; $i -le $sheetCount; $i++)

The reason lies in how the COM object works internally, which I can't fully explain myself. This StackOverflow question briefly reviews why not to use 2 dots when referencing COM objects. Second, this blog post identifies this specific change as improving performance from 14 minutes to 10s. I haven't validated myself, but I would expect some improvement. Hope that helps... even if it is over a year late.

\$\endgroup\$
5
\$\begingroup\$

I find it easier to use the Interop vs. COM approach to Office automation. If nothing else it makes finding values to the enumerated types easier, everything else is basically the same.

Add-Type -ASSEMBLY "Microsoft.Office.Interop.Excel"

You should also look at $WorkSheet.UsedRange This will give you a range (Row and Columns) that excel identifies as having data.

$WorkSheet.UsedRange.Rows.Count
$WorkSheet.UsedRange.Columns.Count
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.