I know just enough PowerShell to get by and can usually figure out how to research how to’s but I cannot seem to find a sound example of how to connect to multiple Excel files and update a “master” spreadsheet via OLEDB/SQL. I am used to sql but certainly open to up-to-date ways to do this…I tend to try to use easier to read format in case code breaks (no nested loops for example) and next coder can figure it out…hence why a 1 liner usually does the trick when I update other sources like a database…
file_01.xlsx is a master sheet that will need to be updated by various sheets
file_02.xlsx contains keywords that are used to try to match a column value in master and if a match update a specific column with file_02.xlsx Column 1 value
the following is by no means meant to work but simply to try to give everyone a way to see what my attempt is - thanks
$directory = split-path -parent $MyInvocation.MyCommand.Definition
$spreadsheet_01 = "$directory\file_01.xlsx"
$spreadsheet_02 = "$directory\file_02.xlsx"
$connection = New-Object System.Data.OleDb.OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$spreadsheet_01;Extended Properties='Excel 12.0;HDR=YES;IMEX=0';")
$connection.open()
$excel = New-Object -Com Excel.Application
$book = $excel.Workbooks.Open($spreadsheet_02)
$sheet = $book.Worksheets.item(1)
$range = $sheet.UsedRange
foreach ($row in $range.Rows)
{
Set connection.Execute("UPDATE [sheet1$] Set [Column_01] ='$row.Cells.Item(1).Text' WHERE [Column_01] -match $row.Cells.Item(2).Text")
}
# ... save spreadsheet_01 and close all open connections
Thanks for the welcome. Yes, I had looked into these type of modules and most certainly would be ideal but I am limited to what I can install. So I have to use what’s basically installed by default and cannot import modules which may require permissions. Thanks!
hmmm … I’m unsure if you got what I meant … you really just have to run this code snippet as it is in a PowerShell console. PowerShell will download the module for you and install it in your profile. No need to download it in advance.
Yes sir…I was referencing your first post. So, I ran snippet as suggested and thumbs up! Thanks for the persistence and patience…this module should be very useful to me - thanks!