by yeon (1 Submission)
Category: Internet/HTML
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Wed 7th August 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Insert any Excel graph in your web page
API Declarations
'[within excel]
'html (\msoffice\library\html.xla)
'[vb]
'ado or dao, nb:ado recordset dump only works with excel 9.0 lib
'excel 8.0 object library
VBcoders
'// hope you all find it useful ; )
sub writeHtml
Dim fso As FileSystemObject
Dim txtfile As TextStream
Dim xlApp as excel.application, xlWkBk as excel.workbook
Dim cursor as excel.range
Dim Wkspace As dao.Workspace
Dim DBase As dao.Database
Dim rs As dao.Recordset
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile("C:\webPage.htm", True)
'create html "template" file with graph reference tag ie:(<!--##Table##-->)
txtfile.Write "<html>" & vbCrLf
txtfile.Write "<body><div align='center'>" & vbCrLf
txtfile.Write "<!--##Table##-->" 'this string is replaced with image src
txtfile.Write "<br>" & vbCrLf
txtfile.Write "</div></body>" & vbCrLf
txtfile.Write "</html>"
txtfile.Close
'query some data from an access database
Set Wkspace = DBEngine.Workspaces(0)
FName = "C:\GraphData.mdb"
Set DBase = Wkspace.OpenDatabase(FName)
Set rs = DBase.OpenRecordset("select * from gphData;")
'get/open instance of excel and paste data in sheet2
Set xlApp = CreateObject("excel.application")
Set xlwkbk = xlApp.Workbooks.Open("c:\xlGraph.xls") ' open xl file
Set cursor = xlwkbk.sheets("sheet2").range("a1")
cursor.copyfromrecordset rs 'using dao because xl 8.0 was pre ado
'refresh graph in sheet1 (to be converted to gif format)
Dim chtObj As Excel.Chart
Set chtObj = Sheets("sheet1").ChartObjects("Chart 1").Chart
chtObj.SetSourceData Source:= _
Sheets("sheet2").Range("A1:A10,B1:B10") _
, PlotBy:=xlColumns
chtObj.Refresh
'call the xl function to convert graph and insert ref into webpage
xlApp.Run "xlGif"
end sub
'the code below is in the xl workbook module
'it needs an xl chart in sheet1
'parts of this code courtesy of article found stumbling along somewhere in msdn
Sub xlGif()
Dim ObjToConvert As Variant, pathxlHtm as String
pathxlHtm = "c:\webPage.htm"
' Populate objects for HTMLConvert.
Set ObjToConvert = Sheets("Sheet1").ChartObjects("Chart 1")
' Load Html.xla into memory.
AddIns("Internet Assistant Wizard").Installed = True
' Create the HTML document.
htmlconvert rangeandcharttoconvert:=ObjToConvert, _
usefrontpageforexistingfile:=False, _
addtofrontpageweb:=False, useexistingfile:=True, _
codepage:=1252, _
htmlfilepath:=pathxlHtm, _
existingfilepath:=pathxlHtm
End Sub