Here is an on-going compilation of Excel automation samples.
- 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.
- 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.
- 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.
- 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.
- 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
|
No comments:
Post a Comment