ActiveX Controls
Download
1 / 29

ActiveX Controls - PowerPoint PPT Presentation


  • 83 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'ActiveX Controls' - ashby


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

ActiveX Controls

Presenters:Chris Gillespie

Jerry Beaverson


Pridgeon and clay
Pridgeon and Clay

Introduction Presentation:

Welcome to Pridgeon and Clay



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




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}.



ActiveX Controls

ActiveX Controls at Work


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*/


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*/


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}


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"


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*/


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*/


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".


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).


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).


ActiveX Controls

Pivot Tables


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}.



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".






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


ActiveX Controls

Thank You….

Chris Gillespie

[email protected]

Jerry Beaverson

[email protected]


ad