Link to home
Create AccountLog in
Avatar of Jim Dandy
Jim Dandy

asked on

Extract Named Ranges from multiple files using PowerShell

I have a series of directories, each with a file with the name in the format "Underwriting (property).xlsx" where the property name is different each time. Each file has a series of named ranges that I'd like to query with PowerShell and collect the data in a single output file, one file data per line/row.

Named ranges are things like: Property Name, Purchase_Price, Units, etc.

If it helps, I can move a copy of all these files into a single directory.

I can work with PowerShell scripts, I'm just not great at creating one from scratch.
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Starter for 10...
Get-ChildItem c:\dir1, c:\dir2 -Filter 'Underwriting (*).xlsx' -Recurse |
    Group-Object { $_.BaseName -replace '^.+\(|\).*$' }

Open in new window

This gets files matching the pattern, then groups them based on whatever "(property)" the file has.
You want to get the content of all named ranges but the default ones?
Multiple ranges are not likely to have the same columns, so how should they get together?
I would like to extract about a dozen named ranges, all single cells.
Ok, I've gotten a bit farther along. Using ISE, I can show the output of a small set of variables that I'm looking for, but now I could use some assistance in getting the information into a file. I'd like the information from each file I open to be placed in columns and each file's data to its own line.

The code that I have to get to this point is:
# Opens Underwriting files, extracts  necessary data, closes files
#

[cmdletbinding()]

$FilePath = "C:\Users\JimD\Underwriting"

#Specify the Sheet name
$SheetName1 = "Property Summary"

# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false

$UWFiles = Get-ChildItem -Filter "Underwriting (*).xlsm" -path $FilePath
foreach ($UW in $UWFiles)
{

# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($UW.fullname)
# Load the WorkSheet 'BuildSpecs'
$WorkSheet = $WorkBook.sheets.item($SheetName1)

    [pscustomobject][ordered]@{
    	PropertyName = $WorkSheet.Range("Name").Text
    	Market = $WorkSheet.Range("D6").Text
    	Units = $WorkSheet.Range("Unit_Count").Text
    	Price = $WorkSheet.Range("Purchase_Price").Text
    	OCC_Rate = $WorkSheet.Range("D9").Text
    	Year_Built = $WorkSheet.Range("D8").Text
    }

$workBook.Save()
$workbook.Close()
}

while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)){'released one count'}
Remove-Variable objExcel

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks for that, I copied the use of 'ordered' from another example. I did finally get things working and found that I needed to test for the existence of a second version of my underwriting where the sheet name was different. I'm not sure if it's the cleanest way to do it though but it works.

One question remaining....is there a way to close the Excel file without saving?  I was hoping to preserve the last file save time and not update it but I found no examples of closing without getting prompted to save and I certainly don't want to babysit the process.

Here is the final  result:
# Opens Underwriting files, extracts  necessary data, closes files
#

[cmdletbinding()]

$FilePath = "C:\Users\JimD\Underwriting"
$details  = ""
$details2  = ""

#Specify the Sheet name
$SheetName1 = "Property Summary"
$SheetName2 = "New Financing"

# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false

#$UWFiles = Get-ChildItem -Filter "Underwriting (*).xlsm" -Recurse -path $FilePath
$UWFiles = Get-ChildItem -Filter "Underwriting (*).xlsm" -path $FilePath
#write-output $FilePath

foreach ($UW in $UWFiles)
{
write-output $UW.fullname

#Check for worksheet named Property Summary
# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($UW.fullname)
$WorkSheets = $WorkBook.Worksheets |where {$_.name -eq "Property Summary"}

    if ($worksheets){
        $WorkSheet = $WorkBook.sheets.item($SheetName1)
        $details = [pscustomobject][ordered]@{
        	PropertyName = $WorkSheet.Range("Name").Text
        	Market = $WorkSheet.Range("D6").Text
        	Units = $WorkSheet.Range("Unit_Count").Text
           	Price = $WorkSheet.Range("Purchase_Price").Text
           	OCC_Rate = $WorkSheet.Range("D9").Text
          	Year_Built = $WorkSheet.Range("D8").Text}
            $details | Export-Csv -path "c:\temp\test.csv.txt" -Append
       }
    Else {
        $SheetName2 = "New Financing"
        $WorkSheet = $WorkBook.sheets.item($SheetName2)
        $details = [pscustomobject][ordered]@{
        	PropertyName = $WorkSheet.Range("C8").Text
         	Market = $WorkSheet.Range("C8").Text
    	    Units = $WorkSheet.Range("D9").Text
    	    Price = $WorkSheet.Range("D10").Text
    	    OCC_Rate = $WorkSheet.Range("I20").Text
    	    Year_Built = $WorkSheet.Range("C8").Text}
            $details | Export-Csv -path "c:\temp\test.csv.txt" -Append
        }


$workBook.Save()
$workbook.Close()
}


while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)){'released one count'}
#$objExcel.Quit()
Remove-Variable objExcel
Remove-Variable SheetName1
Remove-Variable SheetName2
Remove-Variable Details

Stop-Process -processname EXCEL

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you, I've searched for this and was unable to find it. I think that, in the words of Dr. Alfred Lanning from iRobot, I needed to ask the right question.

Many thanks to you both....

I would mark this as solved...but I don't see the option.