INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

COM and Automation

Useful Excel Automation examples. by mgagnon
Posted: 5 Oct 03 (Edited 8 Oct 03)

Here is an on-going compilation of Excel automation samples.
  1. How to copy a .jpg from a general field to an Excel sheet.

    oExcel =CREATEOBJECT("excel.application")
    oWorkBook = oExcel.workbooks.add()
    oSheet = oWorkbook.activesheet
    USE e:\trans\pics AGAIN IN 0 && The table with the general field that holds the jpg.
    LOCATE && Go op
    KEYBOARD "{CTRL+C}{CTRL+W}" && Copy the jpg
    MODIFY GENERAL pics.pic
    oSheet.paste() && Paste the clipboard content in the the sheet
    oExcel.visible = .t.

  2. How to create a chart via Excel automation

    #DEFINE xlColumnClustered    51    
    LOCAL oExcel as Excel.application
    LOCAL oWorkbook,oSheet
    oExcel = CREATEOBJECT("Excel.application")
    oWorkbook= oExcel.Workbooks.Add()
    oSheet = oWorkbook.activesheet
    WITH oSheet
     .Range("A1").Select
        .Range("A1").FormulaR1C1 = "1"
        .Range("A2").Select
        .Range("A2").FormulaR1C1 = "2"
        .Range("A3").Select
        .Range("A3").FormulaR1C1 = "3"
        .Range("A4").Select
        .Range("A4").FormulaR1C1 = "4"
        .Range("A5").Select
        .Range("A5").FormulaR1C1 = "5"
        .Range("A6").Select
        .Range("A6").FormulaR1C1 = "6"
        .Range("B1").Select
        .Range("B1").FormulaR1C1 = "10"
        .Range("B2").Select
        .Range("B2").FormulaR1C1 = "11"
        .Range("B3").Select
        .Range("B3").FormulaR1C1 = "50"
        .Range("B4").Select
        .Range("B4").FormulaR1C1 = "60"
        .Range("B5").Select
        .Range("B5").FormulaR1C1 = "70"
        .Range("B6").Select
        .Range("B6").FormulaR1C1 = "90"
        .Range("A1:B6").Select
    ENDWITH
    WITH oWorkbook
        .Charts.Add
        .ActiveChart.ChartType = xlColumnClustered
        .ActiveChart.SetSourceData(oSheet.Range("A1:B6"))
        .ActiveChart.HasTitle = .f.
    ENDWITH
    oExcel.Visible =.t.


  3. How to delete a sheet from a workbook
    Local oSheet,oWorkBook,oExcel
    oExcel = CREATEOBJECT("Excel.application")
    oWorkBook = oExcel.Workbooks.Add()
    oSheet = oWorkBook.activeSheet
    oSheet.Delete()
    oExcel.Visible = .t.


  4. How to add a sheet to a workbook
    Local oSheet,oWorkBook,oExcel
    oExcel = CREATEOBJECT("Excel.application")
    oWorkBook = oExcel.Workbooks.Add()
    oWorkbook.Sheets.Add
    oExcel.Visible = .t.


  5. How to move a sheet within a Workbook.
    oExcel = CREATEOBJECT("excel.application")
    oWorkbook = oExcel.Workbooks.Add()
    oWorkbook.Sheets.Add
    oSheet = oWorkbook.ActiveSheet
    oSheet.Move(,oWorkbook.Sheets(4)) && Move after sheet3
    oSheet.Move(oWorkbook.Sheets(4),) && Move before sheet3
    oExcel.Visible =.t.

Mike Gagnon

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close