How do I select cells, copy, paste special, value

[borsker]

I am looking to select a range of cells and copy them, paste special - value, but I do not know the appropriate command. Here is as far as I got.

loExcel = CREATEOBJECT([Excel.application])
WITH loExcel
mcInFile = "c:\matrix.xls"
loExcel.workbooks.OPEN(mcInFile)
loSheet = .activesheet
  WITH loSheet 
     .range([A1:Z50]).copy
     ** Need Help Here!!!
  ENDWITH  
ENDWITH 

loExcel.QUIT
RELEASE loExcel
close all

 

[Mike Gagnon]

#Define xlPasteAll	-4104
#Define xlNone	-4142
loExcel = Createobject([Excel.application])
With loExcel
	mcInFile = "c:\matrix.xls"
	loExcel.workbooks.Open(mcInFile)
	loSheet = .activesheet
	With loSheet
		.Range([A1:Z50]).Select
		.Range([A1:Z50]).Copy
		.Range("A11:D13").Select && You need to determine where you want it pasted, this only an example
		.Range("A6:D8").PasteSpecial(xlPasteAll,xlNone,.F.,.F.)
	Endwith
Endwith
loExcel.Quit
Release loExcel
Close All

Mike Gagnon

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

 

[alvechurchdata]

If you follow the normal technique of recording a macro in Excel as you do the operation you'll generate code looking like this:
[TT]
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False
[/TT]
Fox doesn't know the values of the Excel constants so search for PasteSpecial in the Excel Object Browser to find them.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[borsker]

How do I find out what number to associate with "value"?

the number you gave me is for "all

 

[JRB-Bldr]

One of a variety of sources on the web for VFP's version of Excel constants is at:

http://fox.wikis.com/wc.dll?wiki~excelConstants~VFP

Just include these parameters in your code and the program will "know" them when needed.

NOTE: You may not need ALL of them.

Good Luck,
JRB-Bldr

 

[TamarGranor]

Just want to point out that there's no need to Select the range before copying it, nor before pasting. You get performance by not selecting things.

#Define xlPasteAll    -4104
#Define xlNone    -4142
loExcel = Createobject([Excel.application])
With loExcel
    mcInFile = "c:\matrix.xls"
    loExcel.workbooks.Open(mcInFile)
    loSheet = .activesheet
    With loSheet
        oCopyRange = .Range([A1:Z50])
        oCopyRange.Copy
        oPasteRange = .Range("A6:D8")
        oPasteRange.PasteSpecial(xlPasteAll,xlNone,.F.,.F.)
    Endwith
Endwith
loExcel.Quit
Release loExcel
Close All

 

[borsker]

I do not want to sound like a broken record, but I am trying also to change -
border style to none
line style to none
word wrap to none
shrink to fit to none
merge cells to none
and pattern to none

I was able to change font, bold, italic, underline, justify, font color, general character and autofit

 .Range([A1:Z50]).EntireColumn.AutoFit
		.RANGE([A1:Z50]).NumberFormat = "@"
		.Range([A1:Z50]).HorizontalAlignment = 1
		.Range([A1:Z50]).Font.Bold = .F.
		.Range([A1:Z50]).Font.Italic = .F.
		.Range([A1:Z50]).Font.Color = 1
		.Range([A1:Z50]).Font.Underline = 1

but I am haveing real problems with the rest of them.

 

[Mike Gagnon]

Have you tried the xlNone constant on any of these properties? If I record a macro in Excel and set the border of a cell to none, I get this in the macro.

Range("D4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Look at the website JRB suggested and you will find all the constants (the "xl" values) you need to make this work in VFP.

Mike Gagnon

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

 

[TamarGranor]

In addition to getting the constant information from a website, you can use either the VFP Object Browser or the Office Object Browser to find this information and lots more.

Tamar