PasteSpecial Problem using OLE

[jmueller]

Hello,

Could somebody please tell me why the following code written in VFP8 does not work with Excel 2000?

xlValue = -4163
XlMultiply = 4
oExcel.Range("H6").PasteSpecial(xlValue,xlMultiply,.F.,.F.)

With the last line, I get the following error:

"Unable to get PasteSpecial property of the Range Class"

I'm trying to multiply the existing contents in cell H6 with .01 which as been copied using oExcel.Selection.Copy.

I've been looking for an answer to this problem all over the internet and am only finding people with the same problem but no answers.

Can somebody please help?

Thank You,
Jack

 

[Mike Gagnon]

I'm not sure this is your answer but lets start with the two first items in your code.

xlValue = -4163
XlMultiply = 4

These are suppose to be Excel contants, which is not the way you declare constants (not that it makes much difference) but for the form

 #DEFINE xlValue	2	
#DEFINE XlMultiply  4

The second thing is that xlValue being an Excel Constant preset at 2, I'm not sure what you are trying to achieve by redefining a variable xlValue to -4163, which is the preset values of the constant xlValues. Similar but not the same.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jmueller]

Sorry about that... I meant to type: xlValues (with the "s"). Also, xlValues is actually nXlValues and xlMultiply is nXlMultiply.

Maybe I should "review" my posts hey?

I've never used #Define. I just create variables such as nXlValues and assign the constant value to it (as i've done above). I haven't had any problems with any other excel functions using this format.

So is there any other info you (or anyone) can offer? Someone must have successfully used PasteSpecial in their code, right?

 

[Mike Gagnon]

Here is an example on how to use "PasteSpecial", this was tested on VFP9.0 and Excel 2003. This will put values in increment of 10 in column A, put a value of 100 in column B, and multiply the values in column A with the value found in cell B1. I still recommend use use the Excel constants, rather than variables. This example is based in this example:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=40

#Define xlPasteAll	-4104
#Define xlMultiply	4
#DEFINE xlFillDefault	0	
oExcel = Createobject('excel.application')
oExcel.workbooks.Add()
losheet = oExcel.ActiveSheet
losheet.Range("A1").Select
WITH oExcel.activecell
    .FormulaR1C1 = "10"
endwith
loSheet.Range("A2").Select
WITH oExcel.activecell
    .FormulaR1C1 = "20"
endwith
loSheet.Range("A1:A2").Select
oExcel.Selection.AutoFill(loSheet.Range("A1:A10"),(xlFillDefault))

loSheet.Range("B1").Select
WITH oExcel.ActiveCell
.FormulaR1C1 = "100"
ENDWITH
loSheet.Range("B1").Select
oExcel.Application.CutCopyMode = .f.
oExcel.Selection.Copy
loSheet.Range("A1:A10").Select
oExcel.Selection.PasteSpecial(xlPasteAll,xlMultiply,.F.,.F.)
oExcel.visible = .t.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[jmueller]

Thanks Mike.

I will try it out and respond.

Jack.