| Answer | Below is some sample code in .asp that allows you to send a server based excel spreadsheet that has an external lookup to a database. The master Exel workbook if pened would refresh the data based on a QueryTable that queries MSSQl server.
Often in corporate companies you may want to send a snapshot report once a month to a group of employees or mamangers etc. this code saves the auto master file and deletes any references to Names (Named Ranges) that the Query Table (MSquery) or Data filter have created. This way the user receives a non updating snapshot in time of the report.
Excel is excellent for ding this type of adhoc, template driven reporting and is easy to configure in IIS/ASP environments. Lotus Domino can also use this type of concept except in the example you could only use the Excel automation code in LotusScript as LotusScript does not know the .asp code. It is possible on Win32 OS that LotusScript can reference the COM object from .asp.
<%
dim sFileName
'sFileName = request.QueryString("filename")
sFileName = "thefile"
Dim objExcel
Dim objWorkBook
'Instaniate EXCEL.APPPLICATION OBJECT
Set objExcel = server.CreateObject("EXCEL.APPLICATION")
dim rootPath
rootPath = Request.ServerVariables( "Appl_Physical_Path" )
sPath = "excel_reports\"
sSavePath = "excel_reports\sent\"
sExtension = ".xls"
sTemplatePath = rootPath & sPath & sFileName & sExtension
sFullSavePath = rootPath & sSavePath & sFilename & "_" & getshortdate(now()) & sExtension
'response.write "<BR>sTemplatePath = " & sTemplatePath
'response.write "<BR>sFullSavePath=" & sFullSavePath
'Get a Workbook object to the returned Woorkbook object from the application.open method
Set objWorkBook = objExcel.Workbooks.Open(sTemplatePath)
'By deletng the names from the worksheet, we disconnect the querytable that was in the master template
dim objNames
set objnames = objworkBook.names
'Basic VBA code to do some printing and how how to reference objects
if objnames is nothing then
response.write "objnames is nothing"
Else
response.write "<BR>objnames.count = " & objnames.count
For r = 1 To objnames.Count
response.write "<BR>objnames(objnames.count).name = " & objnames(r).name
response.write "<BR>objnames(objnames.count).refersto = " & objnames(r).refersto
response.write "<BR>objnames(objnames.count).namelocal = " & objnames(r).namelocal
response.write "<BR>objnames(objnames.count).value = " & objnames(r).value
response.write "<BR>objnames(objnames.count).parent = " & objnames(r).parent
response.write "<BR>objnames(objnames.count).index = " & objnames(r).index
response.write "<BR>NameExists = " & NameExists(objnames(r).name)
next
end if
'Delete function - Deletes Defined Names from the Names Collection.
Function DeleteName(TheName)
response.write "<BR>Deleted: " & TheName
On Error Resume Next
objworkBook.Names(TheName).Delete
End function
'We cannot put this routine in the loop of names as when we delete the index is less by one and thus we get errors due to names not found.
for i = 1 to objnames.Count
DeleteName(objnames(1).name)
next
'Sample function using the Names Collection ie using an index see if the actual defined name exists, could be used to decide on a delete or not. Remeber
'When using VBA (OLE Automation from ASP oir EVel Lotus Notes you will get many erros from referencing objects and methods wrongly. They need to be caught.
'TIP:
'I often record my actions that I wish to perform, then manually convert the recroded code to Automation code.
'When using ASP and LotusScript you cannot use the native recorded VBA code. VB can however. There needs to be explicit declarations of all object references.
Function NameExists(TheName)
On Error Resume Next
NameExists = Len(objnames(TheName).Name) <> 0
End Function
objWorkBook.SaveCopyAs(sFullSavePath) ' We use save copy as otherwise we have to pass extra constants - real pain
objWorkBook.Close False
Set objWorkBook = Nothing
'Use this command to stop Excel being left resident in the server tasks list.
objExcel.Quit
Set objExcel = Nothing
'Send the email (does not work in LotusScript)
Dim emailbody
set eml_objMail = server.createobject("CDONTS.NewMail")
eml_objMail.From = "reports@domain.com"
eml_objMail.To = "group@domain.com" 'Send to eval@arc.com
eml_objMail.cc = "individual@domain.com"
eml_objMail.Subject = "Your Monthly Report"
emailbody = "Attached is your monthly report" & vbCRLf
emailbody = emailbody & "If you have any problems with this report please contact reports@domain.com or consult our Technical Support Site." & vbcrlf & vbcrlf
emailbody = emailbody & "Domain Auto Report Manager" & vbcrlf
emailbody = emailbody & "http://www.domain.com" & vbcrlf
eml_objMail.Body = emailbody
eml_objMail.AttachFile sFullSavePath
eml_objMail.Send
set eml_objMail = nothing
'Function
'Gets a formatted date (does not work on LotusScript)
Function getshortdate(aDate)
if adate <> "" then
getshortdate = Day(aDate) & "-" & MonthName(Month(aDate), 2) & "-" & Year(aDate)
Else
getshortdate = ""
End if
End Function
%> |