Excel Auto. Filtering using VFP6 1

[omr1119]

Hi,

I want to extract data file to xls format. but some column colored should be different from other column.
Ex. all Yield < Target will be automatic become red Colors.

I did already a Sample Program but, it's not run correctly.
does anybody can figure out the correct syntax.

*****Error
.Selection.AutoFilter Field:=1, Criteria1:=&quot;<99.99&quot;, Operator:=xlAnd
Error in line 97: Syntax error.
*****
sample Programs

#DEFINE True .T.
#DEFINE False .F.
#DEFINE xlSaveChanges 2
#DEFINE xlDialogSendMail 2

oExcel = CREATEOBJECT (&quot;Excel.Application&quot
oExcel.VISIBLE = .T.



WITH oExcel
.DisplayAlerts = .F.
.Workbooks.OPEN ('c:\windows\temp\fgs.XLS')
.ROWS(&quot;1:1&quot.SELECT
.SELECTION.FONT.Bold = True
.Cells.SELECT
.Cells.EntireColumn.AutoFit
.ROWS(&quot;1:1&quot.SELECT
.SELECTION.EntireRow.INSERT
.SELECTION.FONT.Bold = True
.SELECTION.FONT
.SELECTION.FONT.NAME = &quot;Arial&quot;
.SELECTION.FONT.SIZE = 10
.RANGE(&quot;A2&quot.SELECT
.SELECTION.EntireRow.INSERT
.COLUMNS(&quot;G:G&quot.SELECT
.SELECTION.NumberFormat = &quot;#,##0&quot;
.COLUMNS(&quot;H:H&quot.SELECT
.ROWS(&quot;3:3&quot.SELECT
.SELECTION.FONT.Bold = True
.SELECTION.FONT
.SELECTION.FONT.NAME = &quot;Arial&quot;
.SELECTION.FONT.SIZE = 8
.ROWS(&quot;4:65536&quot.SELECT
.SELECTION.FONT.Bold = False
.SELECTION.FONT
.SELECTION.FONT.NAME = &quot;Arial&quot;
.SELECTION.FONT.SIZE = 8
.SELECTION.WrapText = False
.SELECTION.ORIENTATION = 0
.SELECTION.AddIndent = False
.SELECTION.ShrinkToFit = False
.SELECTION.MergeCells = False
.ROWS(&quot;1:1&quot.SELECT
.SELECTION.FONT.Bold = True
.Cells.SELECT
.Cells.EntireColumn.AutoFit
.ROWS(&quot;1:1&quot.SELECT
.RANGE(&quot;A1&quot.SELECT
.ActiveCell.FormulaR1C1 = 'Un-Ship Finished Goods'
.ROWS(&quot;1:1&quot.SELECT
.SELECTION.FONT.Bold = True
.SELECTION.FONT
.SELECTION.FONT.NAME = &quot;Arial&quot;
.SELECTION.FONT.SIZE = 12
.RANGE(&quot;A1&quot.SELECT
.Range(&quot;J3:K3&quot.Select
.Selection.AutoFilter
.Selection.AutoFilter Field:=1, Criteria1:=&quot;<99.99&quot;, Operator:=xlAnd
.Columns(&quot;J:J&quot.Select
With .Selection.Font
.Name = &quot;Arial&quot;
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
EndWith
.Range(&quot;J3&quot.Select
With .Selection.Font
.Name = &quot;Arial&quot;
.FontStyle = &quot;Bold&quot;
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
EndWith
.Selection.AutoFilter
ENDWITH


Thanks,
OMR1119

 

[Mike Lewis]

Omr,

.Selection.AutoFilter Field:=1, Criteria1:=&quot;<99.99&quot;, Operator:=xlAnd

This is not VFP code. I don't know the exact sequence of parameters, but in general the VFP equivalent would be something like this:

.Selection.AutoFilter(1,&quot;<99.99&quot;,xlAnd)

Also, you will need to find the actual numeric value of xlAnd, and either insert that value in place of xlAnd in the above code, or set up a #DEFINE for it.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[omr1119]

Hi Mike,

It's working....

After the Auto. filter was activate i need to mark those
Column with RED Color..
I'll try this parameters below

.Selection.colorIndex = 3

... but nothing happen... Is this Parameter's a VPF Code or any Suggestion?

Thanks Again

 

[monikai]

i haven't time to check it, but i find this:
With Selection.Interior
.ColorIndex = 3
End With

Regards from Monika (Warszawa - Poland)
(monikai@yahoo.com)

 

[Mike Lewis]

Omr,

.Selection.colorIndex = 3

No, I don't think that's right. Try:

.Selection.font.colorIndex = 3

Or maybe:

.Selection.font.Color = rgb(255,0,0)

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[omr1119]

Hi Mike,

Your right! it is working well now.

Thanks Again

Monikai,
I also try your suggestion it's also useful, i will try to use this in my &quot;Forecolor&quot; condition.

Best Regards,

OMR