ole/excel and BORDERS

[AlastairOz]

I am using ole automation with excel.
The issue I have is with the borders.
Here is what I am doing
I populate the data like this:

LOCAL nRow
nRow = 5
** populate our excel workbook
** get data
thisform.getreportdata
select cReportData

SCAN
xl.cells(nRow, 1).value = Assetid
xl.cells(nRow, 2).value = Walkorder
xl.cells(nRow, 3).value = ALLTRIM(assettype)
xl.cells(nRow, 4).value = ALLTRIM(size)
xl.cells(nrow, 4).value = ptdate
xl.cells(nrow, 6).value = ALLTRIM(Tagnumber)
xl.cells(nrow, 7).value = ALLTRIM(Location1)
xl.cells(nRow, 8).value = ALLTRIM(Location2)
xl.cells(nrow, 9).value = ALLTRIM(Location3)

** increment the row counter
nRow = nRow + 1
ENDSCAN

For the Borders I have 3 methods similar to this:
(I have a #DEFINE list as well)

xl.RANGE("A6:E32").Select
xl.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
xl.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With xl.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
EndWith
With xl.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
EndWith
With xl.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
EndWith
With xl.Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
EndWith
With xl.Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
EndWith
With xl.Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
EndWith

The other 2 methods just change the range selection:
xl.RANGE("F6:H32").Select
xl.RANGE("I632").Select

This covers the whole row and page and enables me to have the lines in various thicknesses as required.

What I want to do is populate the spreadsheet, keep the
various border styles, but only show the borders where the records exist.
Is there a way I can do this?

PS (I was able to do it, but I lost the line thickness: all
the borders were the same.)
I added extra lines in the scan code:

SCAN
xl.cells(nRow, 1).value = Assetid
xl.cells(nRow, 1).borders.LineStyle = xlContinuous
xl.cells(nRow, 1).borders.Weight = xlThin

etc
But the code needs to be like the methods above, and using
a variable somehow for the row.


Any help would be appreciated

Regards

Alastair

 

[JRB-Bldr]

When you say "borders where the records exist"...

Aren't you the one who is writing the records?

Can't you keep a record of where the records exist and then instead of using a fixed command:
xl.RANGE("A6:E32").Select
can't you use the saved values from when you write the records?

Good Luck,
JRB-Bldr

 

[AlastairOz]

The number of records varies according to user input.

Probably a record count at the time the select statement
is generated, and then use this to change the value in the
range select. The number that would need to change in this
case could be the number 32

so:

xl.range("A6:E32").select

would need to have a variable to replace the number 32

xl.range("A6:E Rec#").select
But I can't get this or any variations I have tried to work.

I can't seem to use a variable in that line of code.
Any ideas?

 

[Olaf Doschke]

Transform(number) creates a string from that number.

xl.range("A6:E"+Transform(number)).select

Also possible:

-Alltrim(STR(number))
-PADL(number,3,"0")
-Textmerge("A6:E<<number>>")

Transform(number) is perhaps easy enough and as a side effect very good, as it does work with any number (in the value range) creating the needed number of decimals, eg Transform(32) has 2 digits, Transform(103) has 3 digits.

Bye, Olaf.

 

[JRB-Bldr]

"The number of records varies according to user input."

I understand that, but even so, the user's input doesn't magically get into Excel.

The assumption is that it gets into Excel through your Excel Automation code.

If your application 'knows' the rows then it can use that 'knowledge' to make the appropriate Excel Automation Select and then create the border.

Otherwise if you are entering new rows into an existing Excel file then you can use:
* --- Determine Last Cell In The Worksheet ---
mnLastRow = oExcel.activesheet.UsedRange.ROWS.COUNT
mnLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT

To determine the total Row/Column range of the worksheet.

Good Luck,
JRB-Bldr

 

[TamarGranor]

In addition to the advice you've been given, be aware that you don't have to refer to cells using the "A110" notation. You can instead use the Cells collection to let you refer to cells by their row and column number. So:

.Range("A110")

becomes

.Range(.Cells(1,1), .Cells(4, 10))

I think it's much easier to write code this way.

Tamar

 

[AlastairOz]

Tamar

I tried your suggestion:

.Range(.Cells(1,1), .Cells(4, 10))


and I can't get it to work.
The error message says "Expression not valid outside of WITH/ENDWITH"
I put it in a WITH/ENDWITH and still got the same message.

Alastair

 

[Mike Lewis]

Alastair,

Tamar was just giving you a general suggestion for how to reference a specific cell. I don't think she intended for you to copy her code as is.

You need to supply the full reference to the object that you are addressing. In this case, you wouid put X1 in front of the .Range.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[AlastairOz]

Hi Mike,

I realized that the code was just a reference and I tried
many variations of that code before posting.
Still can't get it to work though...

 

[AlastairOz]

Olaf,
I have been working on your suggestion


xl.range("A6:E"+Transform(number)).select

replacing the Transform(number) part with a variable. eg



mnLastRow = xl.activesheet.UsedRange.ROWS.COUNT

xl.range("A6:E"+Transform(mnLastRow)).select

or

xl.range("A6:E"+(mnLastRow)).select

The variable did not work,that is the expression was accepted,
but did not do anything.

Any ideas?

Regards

Alastair

 

[JRB-Bldr]

If you are using the variable mnLastRow derived from the code line that I suggested above, then its value will be an Integer.

If you were using the TRACE & DEBUG WINDOWs to look at your code executing then you would have been able to see this for yourself.

Now that you know that the value is not a Character string, you can convert it as needed.

So, for example, if
mnLastRow = 25
then you would convert it to something like
mcLastRow = ALLTRIM(STR(mnLastRow))
and use it as:
xl.range("A6:E"+(mcLastRow)).select

Good Luck,
JRB-Bldr

 

[TamarGranor]

I just tried a simple example, just to demonstrate using the Cells formulation. The following works for me with Excel 2003 and VFP 9 SP2.

oXL=CREATEOBJECT("Excel.Application")
oW = oXL.Workbooks.Add()
oXL.Range(oXL.Cells(1,1), oXL.Cells(4,10)).Select()
oXL.Visible = .T.

Tamar

 

[Olaf Doschke]

Hi AlastairOz,

The command does select cells. Afterwards you have to apply changes to that selection, eg set Borders of the selected cells. And you already have that code.

Perhaps you should debug like Tamar shows you and set Excel visible after the Range().Select() to see which cells are selected.

Have a breakpoint there and then see what the rest of the code does with XL.Selection by singlestepping through it in the debugger.

Bye, Olaf.