WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

OLE with Excel
plus

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Convert the HISTOGRAM sheet to bmp

Keywords:   convert the HISTOGRAM sheet to bmp

Question:

The following script works to create a histogram; run it to show the results.

How can I convert the HISTOGRAM sheet to bmp or (preferably) jpeg, so I can insert it into a web page? [ I prefer not to work with Excel's Publish() method, or use furter OLE interaction.

Really appreciate some directions.


; Winbatch OLE: Buid a Hoistogram
; 1- Open an Excel OLE Object
; 2= check if the Analysis Pak is Installed
; 3- If not then quit
; 4= Otherwise add new Workbook, build data file, execute
;    macro code to create Histogram on Separate Worksheet
; Stan Littlefield - November 1, 2002


; UDF to check if sheet exists and delete if it does
#DefineFunction PutData( oWS, r,c,val )
oCell            = oWS.Cells(r,c)
oCell.Value      = val
ObjectClose( oCell )
Return( "" )
#EndFunction

;UDF to check for existence of Excel Addon
#DefineFunction isToolPak( DB, cAddon )
iT   = @FALSE
AI               = DB.AddIns
n                = AI.Count
For i= 1 To n
   x = DB.AddIns(i)
   If x.Title == cAddon && ! (x.Installed)
      x.Installed = @TRUE
      iT=@TRUE
   Else
      x.Installed = @FALSE
   Endif
Next
ObjectClose( AI )
Return( iT )
#EndFunction

;//// Script Starts Here /////
IntControl( 72,1,0,0,0 )
; must use VBA macros
cAddon           = "Analysis ToolPak - VBA"
cXLS             = StrCat( dirget(), "histogram.xls" )
If FileExist( cXLS ) Then FileDelete( cXLS )
lSuccess         = @FALSE

BoxOpen("Loading...","Please Wait")
DB               = ObjectOpen("Excel.Application")
DB.Visible       = @FALSE  ;initially
DB.UserControl   = @TRUE
DB.DisplayAlerts = @FALSE
oAPP             = DB.Workbooks
oXLS             = oAPP.add
oWS              = oXLS.Worksheets("Sheet3")
oWS.Delete()
oWS              = oXLS.Worksheets("Sheet2")
oWS.Delete()
oWS              = oXLS.Worksheets("Sheet1")
oWS.Activate()
oWS.Name         = "Sales"

If ! isToolPak( DB,cAddon )
   If isToolPak( DB,cAddon ) Then Goto ToolCode
Else
   Goto ToolCode
Endif

BoxShut()
message( "Excel Analysis Toolpak","ToolPak Is Not Installed")
:Close
ObjectClose( oWS )
ObjectClose( oXLS )
ObjectClose( oAPP )
DB.Quit
ObjectClose(DB)

If lSuccess Then Display(2,"HISTOGRAM Completed", "Saved as %cXLS%" )
Exit

:ToolCode
DB.Visible       = @TRUE  ;now display Worksheet
BoxShut()

;enter sample data
PutData(oWS,1,1,"Sales Data (in Thousands)")
PutData(oWS,2,1,"213")
PutData(oWS,3,1,"104")
PutData(oWS,4,1,"119")
PutData(oWS,5,1,"261")
PutData(oWS,6,1,"99")
PutData(oWS,7,1,"99")
PutData(oWS,8,1,"111")
PutData(oWS,9,1,"141")
PutData(oWS,10,1,"145")
PutData(oWS,11,1,"112")
PutData(oWS,12,1,"112")
PutData(oWS,13,1,"113")
PutData(oWS,14,1,"199")
PutData(oWS,15,1,"190")
PutData(oWS,16,1,"115")
PutData(oWS,17,1,"115")
PutData(oWS,18,1,"126")
PutData(oWS,19,1,"117")
PutData(oWS,20,1,"170")
PutData(oWS,21,1,"172")



;setup variables for macro call
cSheet           = "HISTOGRAM"
cRange           = oWS.Range("$A$1:$A$21")

; execute macro code
DB.Run("ATPVBAEN.XLA!Histogram",cRange,"HISTOGRAM", , @False, @False, @True, @True )

oXLS.SaveAs( cXLS )
lSuccess         = @TRUE
Goto Close

:Cancel
Goto Close

Answer:

With oC as my Chart Object - it was as easy as:
oC.Export( :: FileName=cGIF, FilterName="GIF" )

Article ID:   W15626
File Created: 2003:05:13:11:29:18
Last Updated: 2003:05:13:11:29:18