Need help with a powershell script that modifies excel spreadsheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1220060%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20powershell%20script%20that%20modifies%20excel%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220060%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20powershell%20script%20to%20look%20into%20a%20folder%20and%20pull%20the%20excel%20files%2C%20open%20them%20%2C%20Look%20at%20the%20header%20and%20footer%20(I%20know%2C%20excel%20doesn't%20have%20headers%20and%20footers...%20First%20and%20last%20line%20works)%20and%20if%20they%20match%20a%20input%20sting%2C%20change%20it%20to%20a%20new%20one.%20Any%20cases%20of%20XYZ%20become%20ABC%20(in%20the%20first%20and%20last%20line%20only)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20the%20script%20doing%20everything%20I%20need%20EXCEPT%20that%20it%201).%20does%20not%20reconcile%20case%20(I%20don't%20want%20to%20change%20%22xyz%22%2C%20just%20%22XYZ%22..%2C%20tried%20the%20LookAt%20parameter%20on%20line%20105%2C%20with%20no%20success)%202)%20it%20does%20change%20partial%20matches%20(it%20changes%20%22XYZ2%22%20to%20%22ABC2%22...%20Not%20want%20I%20wanted%2C%20and%20I%20tried%20the%20MatchCase%20parameter%20with%20no%20luck)%20and%203).%20lastly%20It%20looks%20beyond%20the%20first%20and%20last%20line...%20I'd%20prefer%20to%20just%20look%20at%201st%20and%20last%20but%20if%20we%20can't%2C%20we%20can't.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIt%20looks%20like%20the%20%24Worksheet.Columns.Replace%20(line%20105)%20does%20not%20take%20the%20parameters%20I%20need%2C%20so%20I%20need%20another%20way%20to%20do%20that%20functionality.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENote%2C%20I%20don't%20have%20Visual%20studio%20in%20this%20environment%20(just%20notepad%2B%2B)%2C%20Powershell%20version%20is%205.1.17763.1007%2C%20Excel%20version%20is%2016.0.4939.1000%20(32bit)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESnip%20of%20the%20Script%20is%20attached%2C%20and%20any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EMichael%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1220060%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Regular Visitor

I am working on a powershell script to look into a folder and pull the excel files, open them , Look at the header and footer (I know, excel doesn't have headers and footers... First and last line works) and if they match a input sting, change it to a new one. Any cases of XYZ become ABC (in the first and last line only)


I have the script doing everything I need EXCEPT that it 1). does not reconcile case (I don't want to change "xyz", just "XYZ".., tried the LookAt parameter on line 105, with no success) 2) it does change partial matches (it changes "XYZ2" to "ABC2"... Not want I wanted, and I tried the MatchCase parameter with no luck) and 3). lastly It looks beyond the first and last line... I'd prefer to just look at 1st and last but if we can't, we can't.


It looks like the $Worksheet.Columns.Replace (line 105) does not take the parameters I need, so I need another way to do that functionality.


Note, I don't have Visual studio in this environment (just notepad++), Powershell version is 5.1.17763.1007, Excel version is 16.0.4939.1000 (32bit)


Snip of the Script is attached, and any help would be appreciated.


Thanks.


Michael

0 Replies