Excel related syntax for a NX program - Defining a range of cells

Submitted by JXB on Wed, 03/18/2015 - 00:20
Forums: 
.NET programming

To all

I have a NX “macro” which requires copy-and-pasting data in Excel. The excel application is started & closed by the NX program. I am having trouble with defining the final range in excel as the keyword ‘Cells’ is not recognised.
In an excel macro a line like: mysheet.Range(Cells(i,j),Cells(i+10,j+10)) works no problem.

I have marked the line ('HERE---) where I am having trouble

Const xlDown as Long = -4121
Const xlUp as Long = -4162
Const xlToRight As Long = -4161
Const xlMultiply As Long = 4
Const xlPasteAll As Long = -4104

shData.Range("A1").Value = 99

LastRow = shData.Range("A65000").End(xlUp).Row
LastColumn = shData.Range("B2").End(xlToRight).Column

shData.Range("A1").Select.Copy
‘HERE: Line below is not working
shData.Range(Cells(2,2),Cells(LastRow,LastColumn)).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

Thanks
Regards
JXB

NXJournaling - Fri, 03/20/2015 - 07:30

Try qualifying the "Cells" reference with the parent object (the worksheet). In Excel macros, you can take some shortcuts because it is using "early binding" to resolve references. We can do the same with NXOpen, but only if you have an author license. If you are running the code as a journal, you will need to fully qualify the objects you use in Excel.

shData.Range(shData.Cells(2,2),shData.Cells(LastRow,LastColumn)).Select

JXB - Wed, 03/25/2015 - 05:59

thanks for that. I have used a "brute force approach" and is seems to work

shData.Range("A1").Value = TheScaleFactor
shData.Range("A1").Copy
shData.Range(shData.Cells(2,2),shData.Cells(LastRow,LastColumn)).PasteSpecial(Paste:=xlPasteAll, _
Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False)
objExcel.CutCopyMode = False
shData.Range("A1").ClearContents

Thanks
Regards