I have been banging my head against this for about a week now and I may actually be dying inside trying to get this to work. I am a noob when it comes to scripting, but I'm learning!

We are migrating to a load-balanced TS farm from a single Terminal Server. Years ago (before my time) there was a brilliant idea to allow certain Terminal Services users the ability to access the C: drive of a Terminal Server to save Access and Excel files locally. This is wrong and it has been wrong for a very long time. Unfortunately, for me, there have been numerous Excel-to-Access data connections on the local disk that now have to be found and dealt with.

Here is the issue I'm trying to solve:

Step 1: Import, from a CSV file, values containing a file path e.g. C:\path\directory\file.xls (The directories also contain .xlsm and .xlsx files.)
Step 2: Open Excel programatically
Step 3: Open files from the vales enumerated in the CSV
Step 4: Count data connections (ideally, get the connection strings)
Step 5: Save the output of the count (or connection strings) to a txt file with the same file name as the original in the same directory from whence it was opened
Step 6: Close the files without saving and quit excel

I'm using this for step one:
$csvfile=import-csv -path "C:\Users\thisguy\Desktop\Modified XL\book2.csv"
The format of the CSV file has "Path" in cell A1 with the paths to the files listed from A2 on down.
I can then go:
$csvfile | FOREACH-OBJECT { $_ }
And I get a nice table output to the screen with:

Path
----
D:\Company Shares\Access\Shared\_Testing\Copy of Copy of OGa - Copy2.xlsm
D:\Company Shares\Access\Shared\_Testing\Copy of Copy of OGa - Copy2 - Copy (2).xlsm
D:\Company Shares\Access\Shared\_Testing\Copy of Copy of OGa - Copy2 - Copy.xlsm
D:\Company Shares\Access\Shared\_Testing\Copy of Copy of OGa - Copy2 - Copy.xlsx
D:\Company Shares\Access\Shared\_Testing\Copy of Copy of OGa - Copy2.xlsx
D:\Company Shares\Access\Shared\_Testing\Copy of OGa - Copy2.xlsx
D:\Company Shares\Access\Shared\Access\Audit\Items_NT.xlsx
D:\Company Shares\Access\Shared\Access\Audit\Cust_NonTax.xlsx
D:\Company Shares\Access\Shared\_Testing\OGa - Copy.xlsx
D:\Company Shares\Access\Shared\_Testing\OGa.xlsx
D:\Company Shares\Access\Shared\_Testing\OGa Rush_AddOn_PhysicalInventory.xlsx
D:\Company Shares\Access\Shared\Access\mas90\Lift Sent to MR sheets\LiftSentToMR_New_070813.xlsx
D:\Company Shares\Access\Shared\Access\mas90\Lift Sent to MR sheets\LiftSentToMR_New_080513.xlsx
D:\Company Shares\Access\Shared\Access\mas90\Lift Sent to MR sheets\LiftSentToMR_New_082113.xlsx
D:\Company Shares\Access\Shared\Access\SW\SW_WeedlyNetSales_Values.xlsx
D:\Company Shares\Access\Shared\Access\SW\SWY STORE UNIT SALES.xlsm

Sweet, right?

Now, to open single Excel files, I'm using

$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$doc = $Excel.WorkBooks.Open(C:\users\thisguy\file.xlsm)

To count the connections and output this to a text file (as I can't for the life of me figure out how to get the data connections strings):

$doc.Connections.Count | Out-File C:\savedir\excel.txt

This is how I'm closing without saving:

$Excel.Displayalerts = $false

And this is how I'm closing out Excel:

$Excel.Quit()
$Excel = $null

What is definitely not happening is anything to pull this all together. I could be all over the place here using the Excel com objects and Powershell mixed together, but there has to be a way.

Any, any help or pointing in the proper direction would be so appreciated.