OLE Automation 
Author Message
 OLE Automation

Hello,
I found the following code in Samples of VFP 5 which sends
the results of a crosstab query to Microsoft Excel to
calculate Sales trends for products in the TESTDATA
database. The trend information is retrieved from Excel.
I want to know how updated data can be retreived back into
VFP table from Excel after manupulation.

Thanks & regards,
Riaz Ahmed

Sample Program Code
===================
LOCAL i,j,TrendFunc,xlsheet,XLApp,tmpsheet

WAIT WINDOW "Querying sales data..." NOWAIT

open data (HOME()+"samples\data\testdata.dbc")

SELECT Products.prod_name, MONTH(Orders.order_date),;
  SUM(Orditems.quantity);
 FROM testdata!orders, testdata!orditems, testdata!
products;
 WHERE Orders.order_id = Orditems.order_id;
   AND Products.product_id = Orditems.product_id;
   AND Orders.order_date > CTOD("12/31/94");
 GROUP BY Orditems.product_id, 2, Products.prod_name;
 ORDER BY Products.prod_name, 2;
 INTO CURSOR curSalesData

 DO (_GENXTAB) WITH 'ctabSalesData'

SELECT *, N_1 from ctabSalesData INTO ARRAY arrSalesData

WAIT WINDOW "Starting Excel..." NOWAIT

tmpsheet = GetObject('','excel.sheet')

XLApp = tmpsheet.application

XLApp.Visible = .t.
XLApp.WorkBooks.Add()
XLSheet = XLApp.ActiveSheet

XLSheet.Cells(1,1).Value = "Product"
XLSheet.Cells(1,2).Value = "Sales - Jan"
XLSheet.Cells(1,3).Value = "Sales - Feb"
XLSheet.Cells(1,4).Value = "Sales - Mar"
XLSheet.Cells(1,5).Value = "Trend"

FOR i = 1 to 10
 FOR j = 1 to 4
  IF ISNULL (arrSalesData(i,j)) THEN
   XLSheet.Cells(i+1,j).Value = 0
  ELSE
   XLSheet.Cells(i+1,j).Value = arrSalesData(i,j)
  ENDIF
 ENDFOR
 TrendFunc = "=TREND(B" + LTRIM(STR(i+1)) + ":D" + LTRIM
(STR(i+1)) + ",,4)"
 XLSheet.Cells(i+1,5).Value = TrendFunc
ENDFOR

FOR i = 1 to 10
 IF XLSheet.Cells(i+1,5).Value < 0 THEN
  XLSheet.Cells(i+1,5).Value = 0
 ELSE
  XLSheet.Cells(i+1,5).Value = INT(XLSheet.Cells
(i+1,5).Value)
 ENDIF
ENDFOR

WAIT WINDOW "Trend Data Retrieved" TIMEOUT 2
CLOSE DATA



Sat, 03 Sep 2005 19:08:13 GMT  
 OLE Automation
Hi Riaz,

There are various ways to go about it, but working from the example you
showed, you can basically do the reverse.  Rather than setting a value of a
cell, read it.  I haven't tested the following, but here's the idea...

FOR i = 1 to 10
   DIMENSION aFirstFourFields[1,4]
   FOR j = 1 to 4
      aFirstFourFields[1,j] = XLSheet.Cells(i+1,j).Value
   ENDFOR
   GO (I) IN MyTable
   GATHER FROM ARRAY aFirstFourFields
ENDFOR

HTH,

John


Quote:
> Hello,
> I found the following code in Samples of VFP 5 which sends
> the results of a crosstab query to Microsoft Excel to
> calculate Sales trends for products in the TESTDATA
> database. The trend information is retrieved from Excel.
> I want to know how updated data can be retreived back into
> VFP table from Excel after manupulation.

> Thanks & regards,
> Riaz Ahmed

> Sample Program Code
> ===================
> LOCAL i,j,TrendFunc,xlsheet,XLApp,tmpsheet

> WAIT WINDOW "Querying sales data..." NOWAIT

> open data (HOME()+"samples\data\testdata.dbc")

> SELECT Products.prod_name, MONTH(Orders.order_date),;
>   SUM(Orditems.quantity);
>  FROM testdata!orders, testdata!orditems, testdata!
> products;
>  WHERE Orders.order_id = Orditems.order_id;
>    AND Products.product_id = Orditems.product_id;
>    AND Orders.order_date > CTOD("12/31/94");
>  GROUP BY Orditems.product_id, 2, Products.prod_name;
>  ORDER BY Products.prod_name, 2;
>  INTO CURSOR curSalesData

>  DO (_GENXTAB) WITH 'ctabSalesData'

> SELECT *, N_1 from ctabSalesData INTO ARRAY arrSalesData

> WAIT WINDOW "Starting Excel..." NOWAIT

> tmpsheet = GetObject('','excel.sheet')

> XLApp = tmpsheet.application

> XLApp.Visible = .t.
> XLApp.WorkBooks.Add()
> XLSheet = XLApp.ActiveSheet

> XLSheet.Cells(1,1).Value = "Product"
> XLSheet.Cells(1,2).Value = "Sales - Jan"
> XLSheet.Cells(1,3).Value = "Sales - Feb"
> XLSheet.Cells(1,4).Value = "Sales - Mar"
> XLSheet.Cells(1,5).Value = "Trend"

> FOR i = 1 to 10
>  FOR j = 1 to 4
>   IF ISNULL (arrSalesData(i,j)) THEN
>    XLSheet.Cells(i+1,j).Value = 0
>   ELSE
>    XLSheet.Cells(i+1,j).Value = arrSalesData(i,j)
>   ENDIF
>  ENDFOR
>  TrendFunc = "=TREND(B" + LTRIM(STR(i+1)) + ":D" + LTRIM
> (STR(i+1)) + ",,4)"
>  XLSheet.Cells(i+1,5).Value = TrendFunc
> ENDFOR

> FOR i = 1 to 10
>  IF XLSheet.Cells(i+1,5).Value < 0 THEN
>   XLSheet.Cells(i+1,5).Value = 0
>  ELSE
>   XLSheet.Cells(i+1,5).Value = INT(XLSheet.Cells
> (i+1,5).Value)
>  ENDIF
> ENDFOR

> WAIT WINDOW "Trend Data Retrieved" TIMEOUT 2
> CLOSE DATA



Sat, 03 Sep 2005 23:22:12 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Word 97 OLE Automation error

2. VFP3.0 - Help using OLE Automation with Word95 FileClose function

3. OLE Automation of Microsoft Word

4. OpenOffice OLE Automation Type Parameter Problem

5. OLE Automation of Microsoft Word

6. OLE Automation. Save As question

7. Foxpro executable OLE automation server

8. OLE Automation, Word 9 and fast machines

9. VFP & OMNI OLE Automation

10. ole Automation and Word

11. FPW2.6 and OLE Automation

12. FPW2.6 and OLE automation

 

 
Powered by phpBB® Forum Software