Using Powershell in Excel to check all cell values in column to trigger copy and paste per row

Question

I have a spreadsheet with 2 date columns with only one date value in either of them per row. Having two columns for this is redundant for my purposes so I would like to run a powershell script to check every cell in the Date2 column and if blank, copy the Date1 value on the same row to the Date2 column. That's the part I can't work out; To check all cells in the Date2 column and if blank, copy the adjacent Date1 value over. Once that's done, I'll delete the Date1 column. NOTE: The Date2 column has more dates so using it as the primary column to keep (less copy/pasting). TIA

Example Test.xlsx:
         A            B
1 |    Date1   |    Date2   |
2 | 01/01/1900 |            |
3 | 01/01/1900 |            |
4 |            | 01/01/1900 |
5 |            | 01/01/1900 |
6 | 01/01/1900 |            |
7 |            | 01/01/1900 |

$Excel = New-Object -Com Excel.Application -Property @{Visible = $false} # Start Excel and hide the window
$Excel.DisplayAlerts = $False # Disable comfirmation prompts
$Workbook = $Excel.Workbooks.Open(C:\Temp\Test.xlsx) # Open spreadsheet file

#This is the part I'm stuck on:
#If cell in column Date2 is empty, then copy the adjacent Date1 value to the Date2 cell - This is done until at the end of the columns

[void]$Workbook.Sheets.Item("Test").Cells.Item(1, 1).EntireColumn.Delete() # Deleting "Date1" column
$Workbook.Save() # Save changes
$Workbook.Close($true) # Close workbook
$Excel.Quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) # Release COM

Theo · Accepted Answer · 2020-12-23 11:11:51Z

2

All you need in your code is a loop through the rows and update the cell in Column 2 (B) if empty with the value of Column 1 (A).

Try

$Excel = New-Object -Com Excel.Application -Property @{Visible = $false} # Start Excel and hide the window
$Excel.DisplayAlerts = $False # Disable comfirmation prompts
$Workbook = $Excel.Workbooks.Open("D:\Test\Test.xlsx") # Open spreadsheet file
$Sheet    = $Workbook.Worksheets.Item("Test")

# get the total number of rows in the sheet
$rowCount = ($Sheet.UsedRange.Rows).Count
# update cell values in a loop
for ($row = 1; $row -le $rowCount; $row++) {
    $value = $Sheet.Cells.Item($row, 2).Text
    if ([string]::IsNullOrWhiteSpace($value)) {
        # copy the cell format first; then the value
        $Sheet.Cells.Item($row, 2).NumberFormat = $Sheet.Cells.Item($row, 1).NumberFormat
        $Sheet.Cells.Item($row, 2) = $Sheet.Cells.Item($row, 1)
    }
}

[void]$Sheet.Cells.Item(1, 1).EntireColumn.Delete() # Deleting "Date1" column
$Workbook.Save() # Save changes
$Workbook.Close($true) # Close workbook
$Excel.Quit() # Quit Excel

# clean up COM objects
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

edited Dec 23, 2020 at 11:11

answered Dec 22, 2020 at 10:34

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

S.Hampton Over a year ago

Hi Theo, that worked thanks but the dates that copied across lost their date format; 01/01/1900 copied over as '1'. Do you know how to set the cell to be in date format?

S.Hampton Over a year ago

I got it! I added an extra line at the end of the if statement in the for loop: $Sheet.Cells.Item($row, 2).NumberFormat = "d/MM/yyyy" # Only one 'd' to get rid of leading zeros. I need to test in January to confirm if the rest of my code wants M or MM for month. Cheers

Theo Over a year ago

@S.Hampton I have updated the code to also copy the original cell NumberFormat. This makes it more generic and you don't have to figure out what that format was yourself by trial and error. Cheers!

michiel Thai · Accepted Answer · 2020-12-22 07:06:15Z

0

Is this a recurring task? If not, using excel functions might be quickier.

Anyways, for excel data manipulation, I highly recommend using the ImportExcel module

So you would read the sheet with both columns as properties, create a third property based on a ternary operation of the first two.

answered Dec 22, 2020 at 7:06

michiel Thai

5971 gold badge9 silver badges20 bronze badges

Collectives™ on Stack Overflow

Using Powershell in Excel to check all cell values in column to trigger copy and paste per row

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related