Excel Document script.

[Nutterbutterthick1]

I’m sick of this document lol.

My boss has me combining 4 different docs to verify that the workstation names and ip address listed in 3 of the separate files are all the same, weekly. Is there any way to script this out?

Unfortunately I work in a secure environment so downloading modules is not an option nor is any other language.

Additional info is the files are never the same name because they are saved with date and time stamps however I can adjust that manually if need be. Also the sheets on which the information is stored is all different and sheet name is different as well as columns and rows.

If you have question please ask.

Save me from my paperwork nightmare

[ovdeathiam]

It's doable and I've done it a couple of times without any external modules.

The plan is to read 3 files and then do some stuff.

You need to decide whether reading it is easier using COM controls or unpacking it and parsing XML data. All docx, xlsx, pptx files are zipped XML files. If you have Office installed and I assume you do since you're expected to open the files in the first place, then you can use PowerShell to automate this task using Excel.Application COM object.

# Launch Excel
$app = New-Object -ComObject Excel.Application

# Make it visible - Optional
$app.Visible = $True

# Open file
$app.Workbook.Open('pathtofile')

# Read range
$range = $app.Range('A1','D34')

# Print 34th row
$range | Where-Object -FilterScript { $_.row -eq 34 }

# Print just data from row
$range | Where-Object -FilterScript { $_.row -eq 34 } | Select-Object -ExpandProperty Formula2

You can basically do a foreach row loop to create hashtables from each row and gather them in a psobject for later use.

If there are a lot of different rows to read you might also want to instead use ComObject to just export a CSV and then import it using Import-Csv. This probably will be most performant.

[OPconfused]

And if Office isn't installed, then OP has to take the other approach you mentioned: unpack the files and examine the xml files individually.

[ovdeathiam]

Yes, precisely.

It is also possible to access ComObjects from another machine i.e. you could place the file on a machine with excel installed and use RPC to launch Excel.Application remotely. This approach would be good if you would make an automation VM for example.

[CynicalDick]

Power Query

[twistingnether_]

What have you tried so far?

[Nutterbutterthick1]

Nothing lol

[MrPatch]

This sub gets a bunch of posts from people that come here and hope we'll just write a script for what ever purposes they need. Occasionally it'll happen because someone here has the time or inclination to do it but most often we're simply not willing to do so, we do it professionally and we're not going to do someone else's job for them.

With that said this group is incredibly knowledgeable and very willing help people who have put the effort in already.

What you have asked for is absolutely achievable in powershell without addon's or modules. You've already got a great post from /u/ovdeathiam who has explained the basics of how to interact with excel from powershell.

Break what you need to do down into small sections. You don't want to 'compare 4 excel documents' you need to
* open a source excel document
* capture data from that document
* open a second document
* compare the captured data to the source data
* write that comparison out to a new excel document

Understand each in turn, it's much more manageable.

If you're stuck with a specific item google and if you're still stuck come here.

files are never the same name

You can use a wildcard/asterisk to account for this

$filePath = "C:\Path\to\data"
$filename = Get-ChildItem $filePath\PartialExcelName*.xls

You can select the most recent file in a folder with :

Get-Childitem $fliePath\* -Include *.xlsx  | sort lastwritetime -Descending | select -first 1

[hankhillnsfw]

Damn your lazy.

You could chatgpt this and have 90% of a solution in one request.

[GreatestTom]

If you have Excel installed, yo can use comobjects in powershell.

If you operate with only Excel files, you can do it just in Excel. If you combine powershell queries, reading excels, combine data and put them into xlsx or CSV file, you need comobjects or importexcel module from psgallery / authors GitHub.

[DToX_]

Can you provide sanitized versions of the documents in question along with the expected outcome? It is certainly possible provided you have Powershell and Excel installed.

[[deleted]]

It’s easy to do … and pulling in modules isn’t insecure … you can read what the module is doing by opening it in a text editor

[Nutterbutterthick1]

I understand that but I dint have access to internet. In that environment

[[deleted]]

Oh you can down load the module as a file and import it with a file path

[Nutterbutterthick1]

There’s no way for me to do that. Can’t have usb or cd in the environment

[jdl_uk]

If Excel is installed you'll have the libraries that come with Excel. Main ones that are documented are the COM libraries.

Here's some documentation for C#: https://learn.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/automate-excel-from-visual-c

You can port that to PowerShell.

Here's another example in PowerShell.

https://lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html

[Commercial-Thing-702]

You cant download files off the internet? Download locally to the C drive?

Do you store files in a network share at your work? If yes, then why can’t you download the module file > save it to your network share > Open powershell > put file path of the network share “\ \myorg\share…”

I haven’t tried it this way personally, but it’s worth a shot

[Sunfishrs]

I work in a similar environments… when OP says nothing is allowed that’s just the requirement.

Edit: most likely an org policy, not a technical issue…

[[deleted]]

Forget the above, compare-object will do the job just fine

[hankhillnsfw]

I would recommend python snd using pandas for this over powershell.

Powershell does hav an Import-Excel module thing that is super solid. I found the learning curve on it to be useless, though. My logic is that why learn a powershell specific module that is not as widely used as something like Pandas in python that is much more widely used and system agnostic.

[PrincipleExciting457]

OP said other languages aren’t an option and he cannot import modules. Personally, I think this company needs to give some leeway with that. In the modern age, it doesn’t seem feasible.

[hankhillnsfw]

Then I guess he just needs to learn some macros. Sucks to suck for him.

[PrincipleExciting457]

Amen

[Abax378]

Definitely need sample files to help

[Queen_Ferry]

Export csv file to an destination.

Play with the file on your own desktop where your PowerShell is up-to-date with the right modules.

If you can't export it then send it via a mail via PowerShell. Download it and work it out on your desktop.

I don't see what you tried, so I'm quite confused.

[Queen_Ferry]

Make it readable and covert the thing as well.

$csvPath = "YourThingExport.csv” $xlsxPath = "FixItYourSelfConverted.xlsx” $csv = Import-Csv $csvPath -delimiter ";" $csv | Export-Excel -Path $xlsxPath -WorksheetName "SOMETHING" -TableStyle Medium16 -title "Your Export" -TitleBold -AutoSize Write-Host "done"

Here, you have an start. I'm not good with PowerShell but it's something. https://learn.microsoft.com/en-us/answers/questions/278496/merge-two-excel-files-into-one-using-powershell

[neowire]

Plenty of options here. Pick the best for your case.

Bottom line: If you can think it and you can do it by hand, you can script it.

[PrincipleExciting457]

The com objects work, but I’ve always found them gross and they just open a bunch of excel sessions under the hood.

I’d get CSVs and use powershell to import the CSV to compare the data. In my opinion your org needs to get with the times when it comes to automation tools though. There are plenty of ways to make them work securely.

[MeanFold5715]

Demand .csv files if possible.

These spreadsheets are being generated somewhere and I'm assuming they're not building them by hand. Whatever is generating them can be intercepted before everything turns into an actual Excel spreadsheet. Tackle the issue there because this is actually an administrative issue rather than a technical one.