Excel Automation Question 1

[gbettle]

Howdy all,

Can anyone help converting the following Excel macro to VFP 9? I'm particularly confused about the PasteSpecial lines ...:

Selection.End(xlToRight).Select
Range("BA1").Select
ActiveCell.FormulaR1C1 = "1"
Range("BA1").Select
Selection.Copy
Range("R2:AY2").Select
Range("AY2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Range("G1").Select
Selection.End(xlToRight).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select

Many thanks!

Cheers,

Garry

 

[baltman]

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

coverts to

Selection.PasteSpecial(xlPasteAll, xlMultiply, False, False)

but you also need to define the Excel constants or hard code their actual values.

http://fox.wikis.com/wc.dll?Wiki~ExcelConstants~VFP

thus

Selection.PasteSpecial(-4104, 4, .f., .f.)

or

#DEFINE xlPasteAll -4104
#DEFINE xlMultiply 4
#DEFINE False .f.

Selection.PasteSpecial(xlPasteAll, xlMultiply, False, False)

 

[gbettle]

Man, that was quick.

Many, many thanks Baltman!

Cheers,

Garry

 

[gbettle]

Hi Baltman,

I'm nearly there ... It's choking on the following line:

oExcel.Range( Selection, Selection.End( xlDown)).Select

VFP is stopping with "Variable 'SELECTION' is not found."

Full code:

oExcel = CREATEOBJECT( [Excel.Application])
IF VARTYPE( oExcel) = [O]
oExcel.Application.Workbooks.Open( [c:\Games2.xls])
oExcel.Visible = True
oExcel.Selection.End( xlToRight).Select
oExcel.Range( [BA1]).Select
oExcel.ActiveCell.FormulaR1C1 = [1]
oExcel.Range( [BA1]).Select
oExcel.Selection.Copy
oExcel.Range( [R2:AY2]).Select
oExcel.Range( [AY2]).Activate
oExcel.Range( Selection, Selection.End( xlDown)).Select
oExcel.Selection.PasteSpecial( xlPasteAll, xlMultiply, False, False)
oExcel.Range( [G1]).Select
oExcel.Range( Selection, Selection.End( xlDown)).Select
oExcel.Selection.PasteSpecial( xlPasteAll, xlMultiply, False, False)
oExcel.Range( [G1]).Select
oExcel.Selection.End( xlToRight).Select
oExcel.Application.CutCopyMode = False
oExcel.Selection.ClearContents
oExcel.Range( [A1]).Select
oExcel.ActiveSheet.UsedRange.EntireColumn.Autofit
oExcel.Save
oExcel.Quit
ENDIF

Cheers,

Garry

 

[gbettle]

Nevermind, I've sorted it.

oExcel.Range( Selection, Selection.End( xlDown)).Select

should be

oExcel.Range( oExcel.Selection, oExcel.Selection.End( xlDown)).Select

Cheers,

Garry

PS: Any quick ways to stop Excel asking to confirm saving?

 

[baltman]

oExcel.displayalerts = .f.

 

[TamarGranor]

GBettle - I know you've got this working, but there's really no reason when you're doing Automation to work with the Selection object and, in particular, no reason to select cells. You can address cells directly.

For example, you could replace this code:

oExcel.Selection.End( xlToRight).Select
oExcel.Range( [BA1]).Select
oExcel.ActiveCell.FormulaR1C1 = [1]

with this:

oExcel.Range("BA1").FormulaR1C1 = "1"

Tamar

 

[gbettle]

Hi Tamar,

That does raise an interesting point.

I'm running:

oExcel.Selection.End( xlToRight).Select

to get the furthest most column in the sheet - I don't know how many columns there might be.

So, I'm really surprised about the next line:

oExcel.Range( [BA1]).Select

because it won't work properly if the number of columns change!

Basically, I'm trying to format a few columns that have been characters (Excel is showing them with a little triangle in the corner of the cells) to become numeric by multiplying them by 1. I don't want to format the entire column to numeric because I don't want any cells to show 0 - they should be blank. And I thought that if I just went to the rightmost column + 1, I could place a 1 and copy it for further processing - PastSpecial->Multiply.

So, two new questions:

1) How do you move (and select) through a sheet by relative reference? ie. go to last column + 1

and

2) How do you search for columns by their Header (first row) names?

Cheers,

Garry

 

[JRB-Bldr]

Perhaps you might want to think about using a couple additional Excel Automation commands

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION
[B]mnLastRow = oExcel.activesheet.UsedRange.ROWS.COUNT
mnLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT[/B]

Now by knowing the numeric of the First Row,Col (1,1) and the Last Row (mnLastRow,mnLastCol) you can use standard VFP FOR Loops to cycle through the values of the individual cells in question.

To "search for columns by their Header (first row) names" you merely FOR LOOP through the Columns (from 1 to mnLastCol) of the first row and examine the cell contents.

Then if you need to search a specific column determined in the previous utility, you can FOR LOOP through the Rows (from 1 to mnLastRow) examining the cell contents as you go.

Or you could automate an Excel Search for the contents of an individual cell and use it as a reference for relative position for further cell searching.
Using the VFP conversion of the following Excel Macro VBA code should work:

* ------ Excel Macro VBA Code -------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/10/2007 by JRB
'
    Cells.Select
    Selection.Find(What:="This Cell", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
End Sub

Good Luck,
JRB-Bldr