MSExcel Automation 1

[torturedmind]

One quick question: How can I convert this macro code to VFP6 format?

Range(Selection, Selection.End(xlToRight)).Select

I tried

loExcel.Selection.End(xlToRight).Select()

but it doesn't do anything, i.e., no actions at all. xlToRight has been #DEFINEd properly.

TIA

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[torturedmind]

Well, here's part of the code:

loExcel.Range("A1").Select()

WITH loExcel.Selection
    .End(xlToRight).Select()
    .AutoFilter()                [COLOR=green]&& Ok, all required cells have autofilter in place[/color]
    .Font.Bold = .T.             [COLOR=green]&& Only last cell is formatted[/color]
    .Font.ColorIndex = 2         [COLOR=green]&& Only last cell is formatted[/color]
    .Interior.ColorIndex = 43    [COLOR=green]&& Only last cell is formatted[/color]
    .Interior.Pattern = xlSolid  [COLOR=green]&& Only last cell is formatted[/color]
ENDWITH

My goal is simply to format the selected cells from column A row 1 to the last (dynamic?) column row 1. Just wondering why AutoFilter works when the rest don't.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[cbasoz]

with loExcel
 .Range(.Selection, .Selection.End(xlToRight)).Select
endwith

'Selection' is a property of the application object (loExcel).

Cetin Basoz
MS Foxpro MVP, MCP

 

[TamarGranor]

If you just want to format the range in use, why don't you do:

oExcel.UsedRange.<whatever you want to do>

You don't need to select things to work on them.

Tamar

 

[JRB-Bldr]

While I applaud your efforts of creating an Excel Macro and then trying to convert the VBA code into VFP Automation code, I cannot tell what you are trying to accomplish.

Perhaps in this case it would be better just to tell us what you want to do with the Selected Cells. With that info we might be able to suggest a different way of accomplishing the same goal.

Good Luck,
JRB-Bldr

 

[torturedmind]

Thank you all for the replies/suggestions/comments. I fairly understand now what to do (I think) after reading last night more stuffs about MSExcel object and its properties.

Thanks Cetin for pointing that out. For that I give you a star.

My goal is very simple. I only wanted row 1 to be formatted. Because of that, I cannot use UsedRange property. The exported data from VFP cursor may have varying column (field) counts, thus, the need to select only cell A1 up to the last used column of the same row.

I changed this code

WITH loExcel.Selection
    .End(xlToRight).Select()
ENDWITH

to what Cetin suggested.

WITH loExcel
    .Range(.Selection, .Selection.End(xlToRight)).Select()
ENDWITH

Everything's working now. The target row is much "prettier". Again, thanks so much to all. Peace!



kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[cbasoz]

My goal is very simple. I only wanted row 1 to be formatted. Because of that, I cannot use UsedRange property. The exported data from VFP cursor may have varying column (field) counts, thus, the need to select only cell A1 up to the last used column of the same row.

Wrong. You don't need to select as Tamar said (but macro recording almost always use Selection). You can use UsedRange. ie:

loRow1 = loExcel.ActiveWorkbook.ActiveSheet.UsedRange.Rows(1)

It is a 'Range' object just like 'selection' is. You could for example do this:

loExcel.ActiveWorkbook.ActiveSheet.UsedRange.Rows(1).Font.Bold = .t.

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

To find the last row or column in Excel the automation code is:


* --- Determine Last Row & Column in Excel Worksheet ---
nLastRow = oExcel.activesheet.UsedRange.ROWS.COUNT
nLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT

Since you already know the desired Row (represented below as a string cRow) and you now have a numeric value for the last column and you know the first column (1) - you can do a select just that row:

You compute the ASCII value to Col 1 (which would be 'A') and the ASCII character value for the last column (cLastCol)


* --- Select desired row ---
xlSheet.RANGE("A" + cRow + ":" + cLastCol + cRow).SELECT

And then do your desired formatting. Something like:

* --- Format Selected Rows As Desired ---
oExcel.SELECTION.NumberFormat = "0"
oExcel.SELECTION.FONT.Bold = .T.


Good Luck,
JRB-Bldr

 

[cbasoz]

Hmm probably it needs to be repeated. You DON'T need to select. Instead of:

xlSheet.RANGE("A" + cRow + ":" + cLastCol + cRow).SELECT

You could simply use:

xlSheet.UsedRange


Cetin Basoz
MS Foxpro MVP, MCP