After reading the recent post concerning the difficulties with generating a bitmap from Alpha data in the style of a chart and printing same, I thought there might be another way. Suppose you export the data you want to an excel file and use excel to create and print the chart.
This function assumes you have exported some values from a table to excel. It opens the spreadsheet, decides what the active area is, selects that area, writes a macro which will chart the selected data, prints the chart, closes the spreadsheet without changing anything. I have only tested with a simple two column spreadsheet, thus far. If anyone is interested in further development I will be glad to revisit it. I don't use charting much myself.
To experiment with the function just create a new spreadsheet with some values in columns A and B. A simple sample attached.
Thanks as always to John Magno for the original Excel routine and Marcel Kollenaar for his macro generation instruction.
This function assumes you have exported some values from a table to excel. It opens the spreadsheet, decides what the active area is, selects that area, writes a macro which will chart the selected data, prints the chart, closes the spreadsheet without changing anything. I have only tested with a simple two column spreadsheet, thus far. If anyone is interested in further development I will be glad to revisit it. I don't use charting much myself.
To experiment with the function just create a new spreadsheet with some values in columns A and B. A simple sample attached.
Code:
FUNCTION new_chart AS C (excelname AS C)
Dim xlApp as p
Dim myCell as c
xlApp = ole.create("Excel.Application")
xlApp.Workbooks.Open(excelname)
'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
xlapp.DisplayAlerts = .F.
'establish last row value
lastrow = xlApp.Activecell.SpecialCells("11").Row
lastcol = xlApp.Activecell.SpecialCells("11").Column
lastcell = chr(lastcol+64)+lastrow
xlApp.Range("A1:"+lastcell).Select()
'Create a module in the Excel Project section.
dim vbext_ct_StdModule as n
vbext_ct_StdModule = 1 'VBEXT constant
xlApp.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule)' Create a module in the Excel project environment.
dim VBComp as P
'Assumption is made that there is no Module1 in the empty Excel file.
'If created the module is always named "Module1"
VBComp = xlApp.VBE.ActiveVBProject.VBComponents.Item("Module1")
'Now write the macro into Excel. Code presentation is simple for faster a "AH Erlebnis".
VBComp.CodeModule.InsertLines(1,"Public Sub Prt_chart()")
VBComp.CodeModule.InsertLines(2,"'This code is created with Alpha Five")
VBComp.CodeModule.InsertLines(3,"Charts.Add")
VBComp.CodeModule.InsertLines(4,"ActiveChart.ChartType = xlColumnClustered")
VBComp.CodeModule.InsertLines(5,"ActiveChart.SetSourceData Source:=Sheets("+quote("Sheet1")+").Range("+quote("A1:"+lastcell)+"), PlotBy:=xlColumns")
VBComp.CodeModule.InsertLines(6,"ActiveChart.Location Where:=xlLocationAsObject, Name:="+quote("Sheet1"))
VBComp.CodeModule.InsertLines(7,"With ActiveChart")
VBComp.CodeModule.InsertLines(8,".HasTitle = True")
VBComp.CodeModule.InsertLines(9,".ChartTitle.Characters.Text = "+quote("Some Title"))
VBComp.CodeModule.InsertLines(10,".Axes(xlCategory, xlPrimary).HasTitle = True")
VBComp.CodeModule.InsertLines(11,".Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "+quote("X axis title"))
VBComp.CodeModule.InsertLines(12,".Axes(xlValue, xlPrimary).HasTitle = True")
VBComp.CodeModule.InsertLines(13,".Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "+quote("y axis title"))
VBComp.CodeModule.InsertLines(14,"ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True")
VBComp.CodeModule.InsertLines(15,"End With")
VBComp.CodeModule.InsertLines(16,"End Sub")
'Now run the macro that was created by Alpha- and VBA-code.
xlApp.Application.Run("Prt_chart")
xlApp.Workbooks.Close()
xlapp.DisplayAlerts = .T.
xlApp.Quit()
END FUNCTION
'to call the function, use the example on the next line
'new_chart("c:\excelfilename.xls")
Thanks as always to John Magno for the original Excel routine and Marcel Kollenaar for his macro generation instruction.
Comment