Hello,

i have a question, i am building a powershell but i am not very good at it :)

what i am trying to do is copy from a excel file a single cell (C2) to another excel file cell. this part work like a charm.. but when i run the .ps1 again it overwrites the data. i would like it to copy it to the next cell where there is no data inside.

this is powershell:

$excel=new-object -comobject excel.application;
$excel.visible=$true;
$SourceWorkBook=$Excel.Workbooks.open("G:\Book1.xlsx");
$TargetWorkBook=$excel.workBooks.open("c:\Server List.xlsx");
$SourceWorkBook.WorkSheets.item(1).activate();
$SourceRange1=$SourceWorkBook.WorkSheets.item(1).range("D2");
$SourceRange1.copy() | out-null;
$TargetWorkBook.WorkSheets.item(1).activate();
$TargetRange1=$TargetWorkBook.WorkSheets.item(1).range("B19");
$TargetWorkBook.ActiveSheet.Paste($TargetRange1);

 

please help

$sourceFile = "C:\TestFTP\Source.xlsx"
$destFile = "C:\TestFTP\Destination.xlsx"
$excel=new-object -comobject excel.application;
$excel.visible=$true;
#Setup the workbooks
$SourceWorkBook=$Excel.Workbooks.open($sourceFile);
$TargetWorkBook=$Excel.workBooks.open($destFile);
#Load the worksheets
$SourceWorkBook.Worksheets.item(1).activate();
$TargetWorkBook.WorkSheets.item(1).activate();
#Get the source location
$SourceRange1=$SourceWorkBook.WorkSheets.item(1).range("A1");
$SourceRange1.copy() | Out-Null
#Look value to ensure that it keeps searching for an empty cell
$foundEmptyCell = $false
#The first row to check in the range
$rowNumber = 1
#Declare the variable
$range = ""
#Loop through rows until you find one without a value
while (!$foundEmptyCell)
{
    $range = "A{0}" -f $rowNumber
    $TargetRange1=$TargetWorkBook.WorkSheets.item(1).range($range);
    if ($TargetRange1.Value2 -eq $null)
    {
       
        $foundEmptyCell = $true
    }
    else
    {
        #Write-Host $TargetRange1.Value2
        $rowNumber++
    }
}
#Paste the value in
$TargetWorkBook.ActiveSheet.Paste($TargetRange1)
#Save and close
$TargetWorkBook.Save()
$excel.quit()

The main thing you needed was a loop that checked the destination cell and if there was a value there already it should go to the next and write it there. Making it work on columns would be a lot more fiddly

man you are the best thanks!!

but is have one question when i change the

$range = "C{0}" -f $rowNumber

to say

$range = "C{19}" -f $rowNumber

it will give a error:

Error formatting a string: Index (zero based) must be greater than or equal to zero and less than the size of the argum
ent list..

eny idea why

thanks

Yup, the

<string> -f <value>

Is short hand for; take the string and swap the values on the right hand side in based on their sequence. So in my example it takes the row number and slots it into slot 0, marked with a {0}. You'd effectively asked it to insert the 20th item listed into that slot (it counts from zero) which doesn't exist as you only passed in one item, $rownumber.

If you want that row to start at line 19 then the line to change is:

$rowNumber = 1

That tells the list where to start looking.