Extract Named Ranges from multiple files using PowerShell

Starter for 10...

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

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

Select allOpen in new window

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

Select allOpen in new window

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.