I have a worksheet that gets emailed to me. I have a script that retrieves it and then creates a new CSV file. I copy and paste ranges of cells into the new loadable format for my system.
One of the columns in the NEW file is equal to two of the columns (cell by cell) added together from the original file. I have tried simple code like:
$ws2.range($range6).formula = "=$ws1.Range($range5)+$ws1.Range($range21)"
Where the ranges are equal to:
$range5="G79:G84"
$range21="L79:L84"
$range6="G2:G7"
However this returned #NAME in $range6.
How would I write the code in powershell to accomplish this?
TIA!
Melissa
Hi, welcome to the forum 
Please can you clarify some things:
You say it creates a new CSV file but you appear to be using the Excel COM object. Is the second file actually an XLS or XLSX file?
You say ‘worksheet’ - are $ws1 and $ws2 worksheets in the same workbook, or do you actually have two workbooks (two Excel files)?
Assuming two workbooks, do you need the second workbook to reference the ranges in the first workbook or can you just work out the values from the first workbook and plonk the values into the second workbook?
On the face of it, the #NAME is showing because the second workbook doesn’t know that the ranges in your formula are not in the same workbook, so you’d need to specify that in your formula.
You say it creates a new CSV file but you appear to be using the Excel COM object. Is the second file actually an XLS or XLSX file?
I actually create a CSV at the beginning of my script using: New-Item D:\autoloader\ShellArg\Zafiro2_Post.csv -ItemType File
Set-Content D:\autoloader\ShellArg\Zafiro2_Post.csv ‘bat name ,bat ref,well name,well ref,date,hours,oil,gas,water,tube,cas,choke,spm,rpm,chp,bhp,comments,FLP,FLT’
You say ‘worksheet’ - are $ws1 and $ws2 worksheets in the same workbook, or do you actually have two workbooks (two Excel files)?
The Worksheets are in different files
Assuming two workbooks, do you need the second workbook to reference the ranges in the first workbook or can you just work out the values from the first workbook and plonk the values into the second workbook?
I need to actually reference the range in the first workbook because every day the values change in the file.
Thanks for the questions, I hope my answer make sense. This is the first time I have used one of these forums.
I have tested it and my initial thought was correct: it is the way you’re trying to reference the first workbook from the second.
Here’s a working example using the correct syntax for the references. Mine adds A1+B1 through A5+B5 and puts the results in a second workbook in cells C1-C5.
$xl = New-Object -ComObject Excel.Application
$wb1 = $xl.Workbooks.Open('E:\Temp\Files\Book1.xlsx')
$wb2 = $xl.Workbooks.Open('E:\Temp\Files\Book2.xlsx')
$ws1 = $wb1.Worksheets.Item('Sheet1')
$ws2 = $wb2.Worksheets.Item('Sheet1')
$range5 = '$A$1:$A$5'
$range21 = '$B$1:$B$5'
$range6 = 'C1:C5'
$ws2.Range($range6).Formula = "=[$($wb1.Name)]$($ws1.Name)!$range5+[$($wb1.Name)]$($ws1.Name)!$range21"
Note the use of sub expressions such as $($wb1.Name) which is required when using dot notation inside double quotes.
This might be easier with the ImportExcel module. If you’re working with Excel and PowerShell a lot, it’s definitely worth investigating.
I will give this a go in the morning when my brain is not dead from trying to figure it out today and keep you posted. Thanks for your help!
We are getting closer! Now I have #VALUE! in the summed cells. Is there a way I can email you the two files that I am working with? the Powershell and the reference file? oh and the output that I get from it?
It sounds like the references in your formula aren’t quite right. Please share your PowerShell code on the forum; don’t forget to obfuscate any confidential details before posting.
Meh, without the reference files it truly means nothing:
New-Item D:\autoloader\ShellArg\Zafiro2_Post.csv -ItemType File
Set-Content D:\autoloader\ShellArg\Zafiro2_Post.csv 'bat name ,bat ref,well name,well ref,date,hours,oil,gas,water,tube,cas,choke,spm,rpm,chp,bhp,comments,FLP,FLT'
Add-Content -Path D:\autoloader\ShellArg\Zafiro2_Post.csv -Value 'Mac.e-1003,MAC_E_1003,Mac.e-1003,MAC_E_1003'
Add-Content -Path D:\autoloader\ShellArg\Zafiro2_Post.csv -Value 'Mac.x-1001 bis,MAC_X_1001,Mac.x-1001 bis,MAC_X_1001'
Add-Content -Path D:\autoloader\ShellArg\Zafiro2_Post.csv -Value 'Mac-1004(d),MAC_1004D,Mac-1004(d),MAC_1004D'
Add-Content -Path D:\autoloader\ShellArg\Zafiro2_Post.csv -Value 'Mac-1006(d),MAC_1006D,Mac-1006(d),MAC_1006D'
Add-Content -Path D:\autoloader\ShellArg\Zafiro2_Post.csv -Value 'SP.x-1,SP_X_1,SP.x-1,SP_X_1'
Add-Content -Path D:\autoloader\ShellArg\Zafiro2_Post.csv -Value 'SP.x-2,SP_X_2,SP.x-2,SP_X_2'
$range1="F5"
$range2="E2:E7"
$range3="D79:D84"
$range4="F2:F7"
$range5='$G$79:$G$84'
$range21='$L$79:$L$84'
$range6 ='G2:G7'
$range7="H79:H84"
$range8="H2:H7"
$range11="J79:J84"
$range12="L2:L7"
$range13="E79:E84"
$range14="M2:M7"
$range15="F79:F84"
$range16="N2:N7"
$range17="I79:I84"
$range18="R2:R7"
$range19="K79:K84"
$range20="K2:K7"
$file1 = 'D:\autoloader\ShellArg\Attachments\Attachment_0\Zafiro.xls'
$file2 = 'D:\autoloader\ShellArg\Zafiro2_Post.csv'
$xl = new-object -c excel.application
$xl.displayAlerts = $false
$wb1 = $xl.workbooks.open($file1, $null, $true)
$wb2 = $xl.workbooks.open($file2)
$ws1 = $wb1.WorkSheets.item(1)
$ws1.activate()
$range = $ws1.Range($range1).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range2).Select()
$ws2.Paste()
$wb2.Save()
$ws1.activate()
$range = $ws1.Range($range3).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range4).PasteSpecial(-4163)
$ws1.activate()
$range = $ws1.Range($range5).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range6).PasteSpecial(-4163)
$wb2.Save()
$ws1.activate()
$range = $ws1.Range($range7).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range8).PasteSpecial(-4163)
$wb2.Save()
$ws1.activate()
$range = $ws1.Range($range11).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range12).PasteSpecial(-4163)
$wb2.Save()
$ws1.activate()
$range = $ws1.Range($range13).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range14).PasteSpecial(-4163)
$wb2.Save()
$ws1.activate()
$range = $ws1.Range($range15).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range16).PasteSpecial(-4163)
$wb2.Save()
$ws1.activate()
$range = $ws1.Range($range17).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range18).PasteSpecial(-4163)
$wb2.Save()
$ws1.activate()
$range = $ws1.Range($range19).Copy()
$ws2 = $wb2.Worksheets.item(1)
$ws2.activate()
$x=$ws2.Range($range20).PasteSpecial(-4163)
$wb2.Save()
$ws2.range("I2").formula = "=M2*N2/100"
$ws2.range("I3").formula = "=M3*N3/100"
$ws2.range("I4").formula = "=M4*N4/100"
$ws2.range("I5").formula = "=M5*N5/100"
$ws2.range("I6").formula = "=M6*N6/100"
$ws2.range("I7").formula = "=M7*N7/100"
$ws2.Range($range6).Formula = "=[$($wb1.Name)]$($ws1.Name)!$range5+[$($wb1.Name)]$($ws1.Name)!$range21"
$wb1.close($false)
$wb2.close($true)
$xl.quit()
spps -n excel
Please can you edit your post and format it as code.
Just hit the </> button on an empty line, then copy and paste your code where shown.
Thanks.
Neat! Edited! Thank you very much!
That code looks fine. Are your ranges $range5 and $range21 correct and do you definitely have numbers in those fields? You’ll get a #VALUE error if you have text in those fields.
There is data. I’m so confused.
Nothing obvious there. I have sent you a PM with a link to my OneDrive so you can upload the file if you’re happy to share it.
Perfect! Thank you so much. The XLSX file is the reference file, the CSV is the file created through the PS file, and then there is the PS1 file. Please let me know if you have any questions.
Melissa
I think this problem may be related to the version of Excel that the original document was created in. It appears to struggle with the @ symbol that’s being added to the formula.
This almost works, it throws an error but the column appears to be populated with the correct values:
$ws2.Range($range6).Formula = "='[$($wb1.Name)]'$($ws1.Name)!$range5+'[$($wb1.Name)]'$($ws1.Name)!$range21"
Note the addition of single quotes around the workbook name.
Something odd I have noticed is that in your CSV file, I don’t see the formulas just the values. If I use my own CSV file, I see the formula.
So close! Except the value it returns is not the addition of the ranges. It is only range5, not range 5+range21. It seems to have an issue with the worksheet name in the error it throws.
Oops, I didn’t notice that. Still, that helps isolate the problem a bit.
I’m sure we can crack this by fiddling with the formula. I think it’s treating the + as the concatenation operator. Escaping it fixed the error but broke the references again…
Right, I’ve cracked it 
It was breaking because we don’t actually need to specify the range, just the start of the range. Excel is clever enough to figure it out.
Try this. I’ve set new variables so it doesn’t affect your other code as $range5 is referred to elsewhere.
$range5Start = 'G79'
$range21Start = 'L79'
$ws2.Range($range6).Formula = "=[$($wb1.Name)]$($ws1.Name)!$($range5Start)+[$($wb1.Name)]$($ws1.Name)!$($range21Start)"
1 Like
OMG! Yes!!! YOU ARE A GOD!!! And a lifesaver! I cannot tell you how grateful I am. Now… I am trying to learn Powershell, mostly for things like this, like manipulating excel and CSV files. Any suggestions for where to go to learn?
The best introduction to PowerShell is still, in my opinion, Learn Windows PowerShell in a Month of Lunches. It will teach you all the necessary fundamentals.
There is a newer edition of the book covering Mac and Linux versions but I’ve not read that one. Unless you’re working cross-platform, the Windows PowerShell book is the one to go for.
There are free books and videos linked from the main powershell.org site and /r/PowerShell/ also has some good links in the sidebar
For Excel, there is the ImportExcel module which I mentioned previously in this thread. If you’re working with Excel a lot, you should definitely look at that. It removes the need to work with COM objects in a lot of instances. However, if the COM objects are unavoidable, then looking at VBScript examples will usually lead to a PowerShell solution.
If you’re doing any work with SQL server, then look at dbtatools .
2 Likes
