Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Automate excel sheet

Status
Not open for further replies.
dotnik

dotnik

Programmer
Nov 14, 2003
24
IT
Hi,
I'm Italian's programmer, I live in Matera.
I have finding istruction for create a report in excel.
I must to generate a complex report from Visual FoxPro 6.0 to Excel because my customer Should be modify the it layout, fox example order of column, characters etc.

Thanks to all.
dotnik 78 from Italy

P.S.: I don't write very well to English but I hope that You understand me.
 
Sort by date Sort by votes
Take a look at the FAQ area of this thread. There a few examples including mine:

faq184-4428 Excel - How to do Automation from VFP

I believe there is another one that provides information on how to use XML data (from VFP) with Excel and it is faster than my automation example.

You can also copy data directly to excel, and later reformat the spreadsheet.

In short there are several ways to do. Review the FAQs. If you need additional assistance provide us much information (amount of data, etc.) as possible and we will try to recommend a solution for you.



Jim Osieczonek
Delta Business Group, LLC
 
Upvote 0 Downvote
There is also a book called ... Microsoft Office Automation with Visual Foxpro published by Hentzenwerke Publishing. Author: Tamar E. Granor and Della Martin

There are 3 chapters in that about Excel Automation.



Don


 
Upvote 0 Downvote
DOTNIK

There is also faq184-4266 to add to jimoo's comment.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
 
Upvote 0 Downvote
dotnik

Here is something I'm working currently on with column manupulation (font bolding, centering etc...), the #DEFINE are done in a header file.
Code: 
SELECT Lftdetail2003100.icid AS Cid, Lftdetail2003100.cani AS A_Line, ;
   Lftdetail2003100.DATE AS D_Date,Lftdetail2003100.ctime AS M_Time, ;
   Lftdetail2003100.cterm_ph AS Number_Dialed, Lftdetail2003100.cterm_pl AS Term_Place,;
   Lftdetail2003100.cterm_pr AS Term_Prov, Lftdetail2003100.Ndur AS Duration,;
   Lftdetail2003100.ncharge AS Charge, Projcode.ncode AS Acct_Code, Projcode.cname AS Employee;
   FROM  Lftdetail2003100 LEFT OUTER JOIN suntel!Projcode ;
   ON  Lftdetail2003100.cacc_t1 = Projcode.ncode;
   WHERE Lftdetail2003100.icid = 38340;
   AND Lftdetail2003100.codebill = "1";
   AND SUBSTR(Projcode.wtnno,1,3) <> &quot;403&quot;;
   GROUP BY Lftdetail2003100.cacc_t1, Lftdetail2003100.cani,;
   Lftdetail2003100.DATE, Lftdetail2003100.ctime,;
   Lftdetail2003100.corg_pr, Lftdetail2003100.cterm_ph,;
   Lftdetail2003100.cterm_pl, Lftdetail2003100.cterm_pr,;
   Lftdetail2003100.cdest, Lftdetail2003100.Ndur,;
   Lftdetail2003100.ncharge, Projcode.ncode;
   ORDER BY Projcode.cname,  Lftdetail2003100.DATE, Lftdetail2003100.ctime ;
   INTO CURSOR OBUSSMR3RAW READWRITE
SELECT SPACE(10) AS SPACE,Acct_Code,Employee, SUM(Duration) AS minutes, SUM(Charge) AS Charge FROM OBUSSMR3RAW GROUP BY Employee ORDER BY 2 INTO CURSOR cursor1
SELECT SPACE(10) AS SPACE,&quot;Acct. code&quot; AS field1,&quot;Employee&quot; AS field2,&quot;Minutes&quot; AS field3,;
   &quot;Charge&quot; AS field3 FROM cursor1 INTO CURSOR cursor4

LOCAL oExcel AS excel.APPLICATION
LOCAL owb AS excel.Workbook
oExcel = CREATEOBJECT(&quot;excel.application&quot;)
owb = oExcel.Workbooks.ADD()
oExcel.VISIBLE =.T.
WITH owb
   .Sheets.ADD
   .Sheets(1).SELECT
   WITH .activesheet
      .NAME = &quot;obus october 2003 smr3 report&quot;
      SELECT cursor4
      nrow=9
      nfieldno=AFIELDS(arrfielda,'cursor4')
      SCAN
         FOR N=1 TO nfieldno
            cfield='cursor4.'+arrfielda(N,1)
            .Cells(nrow,N).VALUE=(&cfield)
         ENDFOR
         .Cells(nrow,1).SELECT
         nrow=nrow+1
      ENDSCAN
      nfieldno=AFIELDS(arrfielda,'cursor1')
      nrow=10
      SELECT cursor1
      SCAN
         FOR N=1 TO nfieldno
            cfield='cursor1.'+arrfielda(N,1)
            .Cells(nrow,N).VALUE=(&cfield)
         ENDFOR
         .Cells(nrow,1).SELECT
         nrow=nrow+1
      ENDSCAN
   ENDWITH
   LOCAL cRange
   cRange = &quot;B10:&quot;+&quot;E&quot;+TRANSFORM(nrow-1)
   osheet = owb.activesheet
   osheet.RANGE(cRange).SELECT
   oRn = osheet.RANGE(cRange)
   WITH oRn
      .BORDERS(xlEdgeLeft)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlEdgeTop)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlEdgeBottom)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlEdgeRight)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlInsideVertical)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlInsideHorizontal)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   cRange = &quot;B&quot;+TRANSFORM(nrow)+&quot;:&quot;+&quot;E&quot;+TRANSFORM(nrow)
   osheet.RANGE(cRange).SELECT
   oRn = osheet.RANGE(cRange)
   WITH oRn
      .BORDERS(xlEdgeLeft)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlEdgeTop)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlEdgeBottom)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlEdgeRight)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   WITH oRn
      .BORDERS(xlInsideVertical)
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH

   WITH oRn
      .BORDERS(xlInsideHorizontal)
      .Interior.PATTERN = xlSolid
      .Interior.ColorIndex = 15
      .Interior.PatternColorIndex = xlAutomatic
      WITH .BORDERS
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      ENDWITH
   ENDWITH
   SELECT SPACE(10) AS SPACER,&quot;Total&quot; AS TOTAL,&quot;Grand Total&quot; AS GR,SUM(cursor1.minutes),SUM(cursor1.Charge) FROM cursor1 INTO CURSOR cursor2
    SELECT cursor2
   nfieldno=AFIELDS(arrfielda,'cursor2')
   WITH .activesheet
      SCAN
         FOR N=1 TO nfieldno
            cfield='cursor2.'+arrfielda(N,1)
            .Cells(nrow,N).VALUE=(&cfield)
         ENDFOR
         .Cells(nrow,1).SELECT
         nrow=nrow+1
      ENDSCAN
      cRange = &quot;B10:&quot;+&quot;C&quot;+TRANSFORM(nrow-1)
      osheet = owb.activesheet
      osheet.RANGE(cRange).SELECT
      oRn = osheet.RANGE(cRange)
      WITH oRn.FONT
         .NAME = &quot;Arial&quot;
         .FontStyle = &quot;Bold&quot;
         .SIZE = 10
         .Strikethrough = .F.
         .Superscript = .F.
         .Subscript = .F.
         .OutlineFont = .F.
         .SHADOW = .F.
         .Underline = xlUnderlineStyleNone
         .ColorIndex = xlAutomatic
      ENDWITH
      cRange = &quot;B9:E9&quot;
      osheet = owb.activesheet
      osheet.RANGE(cRange).SELECT
      oRn = osheet.RANGE(cRange)
      WITH oRn.FONT
         .NAME = &quot;Arial&quot;
         .FontStyle = &quot;Bold&quot;
         .SIZE = 10
         .Strikethrough = .F.
         .Superscript = .F.
         .Subscript = .F.
         .OutlineFont = .F.
         .SHADOW = .F.
         .Underline = xlUnderlineStyleNone
         .ColorIndex = xlAutomatic
      ENDWITH
      WITH oRn
         .BORDERS(xlEdgeLeft)
         WITH .BORDERS
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
         ENDWITH
      ENDWITH
      WITH oRn
         .BORDERS(xlEdgeTop)
         WITH .BORDERS
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
         ENDWITH
      ENDWITH
      WITH oRn
         .BORDERS(xlEdgeBottom)
         WITH .BORDERS
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
         ENDWITH
      ENDWITH
      WITH oRn
         .BORDERS(xlEdgeRight)
         WITH .BORDERS
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
         ENDWITH
      ENDWITH
      WITH oRn
         .BORDERS(xlInsideVertical)
         WITH .BORDERS
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
         ENDWITH
      ENDWITH
      .COLUMNS(&quot;D:D&quot;).AUTOFIT
      .COLUMNS(&quot;B:B&quot;).AUTOFIT
      .COLUMNS(&quot;C:C&quot;).AUTOFIT
      .COLUMNS(&quot;E:E&quot;).AUTOFIT
      .COLUMNS(&quot;E:E&quot;).NumberFormat = &quot;$#,##0.00&quot;
      .COLUMNS(&quot;B:B&quot;).HorizontalAlignment = xlCenter
      .COLUMNS(&quot;C:C&quot;).HorizontalAlignment = xlCenter
      .COLUMNS(&quot;D:D&quot;).HorizontalAlignment = xlCenter
      .COLUMNS(&quot;E:E&quot;).HorizontalAlignment = xlCenter
   ENDWITH
ENDWITH

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Question
Excel to Foxpro 2.6 1
Replies
19
Views
1K
brucewayne00100
B
trial03
  • Locked
  • Question
excel pivot table with XML data source
Replies
1
Views
289
Chriss Miller
C
GriffMG
  • Locked
  • Question
Not Strictly VFP - Cracking Password on Excel Spreadsheet
Replies
6
Views
718
Chris Miller
Chris Miller
TheLazyPig
  • Locked
  • Question
If an Excel file already exists in the folder... 1
Replies
2
Views
705
igorsandler
igorsandler
ravicoder
  • Locked
  • Question
excel automation printing to pdf error
Replies
8
Views
890
combo
combo

Part and Inventory Search

Sponsor

Back
Top