1 / 29

ActiveX Controls

ActiveX Controls. Presenters: Chris Gillespie Jerry Beaverson. Pridgeon and Clay. Introduction Presentation:. Welcome to Pridgeon and Clay. Part Portfolio - Global. ActiveX Controls. ActiveX Controls – Definition

ashby
Download Presentation

ActiveX Controls

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ActiveX Controls Presenters:Chris Gillespie Jerry Beaverson

  2. Pridgeon and Clay Introduction Presentation: Welcome to Pridgeon and Clay

  3. Part Portfolio - Global

  4. ActiveX Controls • ActiveX Controls – Definition • Allows you to control an application through another application (like a puppet on a string) • Allows you to “prep” data and then load it • P&C’s Migration to the Use of ActiveX controls • Began about nine years ago • Expanded use over time • Useful tool to chart and graph (without the expense of a report writer) • Examples of How ActiveX Controls Can be Used • Capacity planning • Steel surcharge calculations • Price data loading • Router changes

  5. ActiveX Controls

  6. ActiveX Controls

  7. ActiveX Controls Transition from Microsoft world Visual BasicProgress Range("A1:C13").Select ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache. _ CreatePivotTable TableDestination:="", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 With ActiveSheet.PivotTables("PivotTable1").PivotFields("Order Qty") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Part No") .Orientation = xlRowField .Position = 2 End With ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Counter"), "Sum of Counter", xlSum End Sub /* chExcelApplication:Dialog:Dialogsheets("test"):Show. */ chWorkSheet:EnablePivotTable = TRUE. chWorkSheet:PivotTableWizard({&xlDatabase}, "Sheet1!A1:C" + STRING(iColumn), "", "PivotTable1", TRUE, TRUE, TRUE, , , ). /* Define row */ chExcelApplication:ActiveSheet:PivotTables("PivotTable1"):AddFields("Order Qty", "Data", , TRUE). chExcelApplication:ActiveSheet:PivotTables("PivotTable1"):AddFields("Part No", "Data", , TRUE). /* Define data*/ chExcelApplication:ActiveSheet:PivotTables("PivotTable1"):PivotFields("Counter"):ORIENTATION = {&xlDataField}.

  8. ActiveX Controls

  9. ActiveX Controls ActiveX Controls at Work

  10. ActiveX Controls DEF VAR Domain AS CHAR INIT "01" NO-UNDO. /* Program code*/ FOR EACH pt_mstr NO-LOCK WHERE pt_domain = Domain AND pt_part_type = "fab" AND pt_prod_line = "11" AND (pt_ord_qty >= 15000 AND pt_ord_qty <= 30000) : DISPLAY pt_part pt_ord_qty . END. /*FOR EACH pt_mstr*/

  11. ActiveX Controls DEF VAR Domain AS CHAR INIT "01" NO-UNDO. /* Program code*/ FOR EACH pt_mstr NO-LOCK WHERE pt_domain = Domain AND pt_part_type = "fab" AND pt_prod_line = "11" AND (pt_ord_qty >= 15000 AND pt_ord_qty <= 30000) : DISPLAY pt_part pt_ord_qty . END. /*FOR EACH pt_mstr*/

  12. ActiveX Controls DEF VAR Domain AS CHAR INIT "01" NO-UNDO. Additional variables: DEF NEW SHARED VAR chExcelApplication AS COM-HANDLE. DEF NEW SHARED VAR chWindow AS COM-HANDLE. DEF NEW SHARED VAR chWorkbook AS COM-HANDLE. DEF NEW SHARED VAR chWorksheet AS COM-HANDLE. DEF VAR iColumn AS INT INITIAL 1. DEF VAR cColumn AS CHAR NO-UNDO. DEF VAR xlfilename AS CHAR NO-UNDO. Excel include file: {XL5EN32.i}

  13. ActiveX Controls Launch Excel /*Define filename*/ xlfilename = "c:\Test.xls". /* create a new Excel Application object */ CREATE "Excel.Application" chExcelApplication. Setting the chExcelApplication to the Object application /* Launch Excel so it is visible to the user */ chExcelApplication:Visible = TRUE. Setting the visible property to the display the application /* create a new Workbook */ chWorkbook = chExcelApplication:Workbooks:Add(). Setting the chWorkbook to the Object application /* get the active Worksheet */ chWorkSheet = chExcelApplication:Sheets:Item(1). Setting the chWorkSheet to the Object application /* Set the column names */ chWorkSheet:Range("A1"):Value = "Part No". chWorkSheet:Range("B1"):Value = "Order Qty"

  14. ActiveX Controls /* Program code*/ FOR EACH pt_mstr NO-LOCK WHERE pt_domain = Domain AND pt_part_type = "fab" AND pt_prod_line = "11" AND (pt_ord_qty >= 15000 AND pt_ord_qty <= 30000) : /*Output*/ iColumn = iColumn + 1. cColumn = STRING(iColumn). /* Set column values - use of field names*/ chWorkSheet:Range("A" + cColumn):Value = pt_part. chWorkSheet:Range("B" + cColumn):Value = pt_ord_qty. END. /*FOR EACH pt_mstr*/

  15. ActiveX Controls /* Program code*/ FOR EACH pt_mstr NO-LOCK WHERE pt_domain = Domain AND pt_part_type = "fab" AND pt_prod_line = "11" AND (pt_ord_qty >= 15000 AND pt_ord_qty <= 30000) : /*Output*/ iColumn = iColumn + 1. cColumn = STRING(iColumn). /* Set column values - use of field names*/ chWorkSheet:Range("A" + cColumn):Value = pt_part. chWorkSheet:Range("B" + cColumn):Value = pt_ord_qty. END. /*FOR EACH pt_mstr*/

  16. ActiveX Controls Format WorkSheet: /* Set the column width */ chWorkSheet:Columns("A"):ColumnWidth = 12. chWorkSheet:Columns("B"):ColumnWidth = 9.5. /* Set the column formats */ /* "@" = text, date = mm/dd/yy;C, units = #,###, dollars = $#,### */ chWorkSheet:Range("A1:B1"):Font:Bold = TRUE. chWorkSheet:Range("B1:B1"):Font:Italic = TRUE. chWorkSheet:Range("A:B"):FONT:Size = 10. chWorkSheet:Range("A1:B1"):FONT:Size = 12. chWorkSheet:Range("A1:A1"):Interior:ColorIndex = 36. chWorkSheet:Range("B1:B1"):Interior:ColorIndex = 37. chWorkSheet:Range("A:A"):NumberFormat = "@". chWorkSheet:Range("B:B"):NumberFormat = "#,##0".

  17. ActiveX Controls /* Freeze panes */ chWorkSheet:Range("E2"):Activate. chWindow = chExcelApplication:Windows:Item(1). chWindow:FreezePanes = TRUE. /* Turn Autofilter On */ chWorkSheet:COLUMNS("A:A"):AutoFilter(1,,,,TRUE).

  18. ActiveX Controls /* Freeze panes */ chWorkSheet:Range("E2"):Activate. chWindow = chExcelApplication:Windows:Item(1). chWindow:FreezePanes = TRUE. /* Turn Autofilter On */ chWorkSheet:COLUMNS("A:A"):AutoFilter(1,,,,TRUE).

  19. ActiveX Controls Pivot Tables

  20. ActiveX Controls Pivot Table: /* chExcelApplication:Dialog:Dialogsheets("test"):Show. */ chWorkSheet:EnablePivotTable = TRUE. chWorkSheet:PivotTableWizard({&xlDatabase}, "Sheet1!A1:C" + STRING(iColumn), "", "PivotTable1", TRUE, TRUE, TRUE, , , ). /* Define row */ chExcelApplication:ActiveSheet:PivotTables("PivotTable1"):AddFields("Order Qty", "Data", , TRUE). chExcelApplication:ActiveSheet:PivotTables("PivotTable1"):AddFields("Part No", "Data", , TRUE). /* Define data */ chExcelApplication:ActiveSheet:PivotTables("PivotTable1"):PivotFields("Counter"):ORIENTATION = {&xlDataField}.

  21. ActiveX Controls

  22. ActiveX Controls /* Set the column formats */ chExcelApplication:ActiveSheet:Range("A:B"):NumberFormat = "@". chExcelApplication:ActiveSheet:Range("A:A"):FONT:Size = 12. chExcelApplication:ActiveSheet:Range("B:C"):FONT:Size = 10. chExcelApplication:ActiveSheet:Range("A1:A21"):Interior:ColorIndex = 36. chExcelApplication:ActiveSheet:Columns("A:A"):ColumnWidth = 20. chExcelApplication:ActiveSheet:Range("B:B"):FONT:ColorIndex = 12. chExcelApplication:ActiveSheet:Range("B:B"):FONT:Bold = TRUE. chExcelApplication:ActiveSheet:Range("B:B"):Font:Italic = TRUE. chExcelApplication:ActiveSheet:Columns("B:B"):ColumnWidth = 15. chExcelApplication:ActiveSheet:Range("C:C"):FONT:ColorIndex = 3. chExcelApplication:ActiveSheet:Range("C:C"):NumberFormat = "#,##0.00000000".

  23. ActiveX Controls

  24. ActiveX Controls Charts

  25. ActiveX Controls

  26. ActiveX Controls

  27. ActiveX Controls Resources • Windows Constants Include (by T. Bergman) http://www.oehive.org/project/TypeLibConstants • MSDN Office Development http://msdn.microsoft.com/en-us/library/bb726434.aspx • “Using Excel Visual Basic for Applications – Special Edition” (by Jeff Webb) 1996 QUE Publishing/Second Edition ISBN: 0-7897-0269-x

  28. ActiveX Controls Thank You…. Chris Gillespie cgillespie@pridgeonandclay.com Jerry Beaverson jbeaverson@pridgeonandclay.com

More Related