So my Code so far is, What I need to be able to do is if Column A has "VIOS" anywhere within that cell, like "VIOS123" or "ABCVIOS" to delete that entire row. I have already created the ComObject, and set my paths. I am using Excel as Visible just so that I can test and follow the changes as I do them and will eventually be copying and pasting using powershell the result of formatting FilePath1.
I have it so that I can delete a specific row, but the location may change of the VIOS row so I need to make it more dynamic.
I have it so that I can delete a specific row, but the location may change of the VIOS row so I need to make it more dynamic.
Code:
## Defines Month Key
= (get-date).Year * 100 + (get-date).Month
## Defines Excel File from Server as "ExcelPath"
$ExcelPath = 'FilePath1.XLS'
## ## Defines Master Excel File from Server as "ExcelPath2"
$ExcelPath2 = 'FilePath2.XLS'
## Creates ComObject to interact with
$Excel = New-Object -ComObject Excel.Application
## Allows Vision of Workbook, Turn to False when not testing
$Excel.Visible = $true
## Uses Defined "Excel Path" (Line 6) and Opens workbook
$ExcelWorkBook = $Excel.Workbooks.Open($ExcelPath)
## Activates the Workbook's First Worksheet
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
##THIS IS WHERE I NEED HELP
$ExcelWorkSheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row