Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

Need help with Excel form data automation, VBScript with FrontPage 2003

I have created an Intranet site here at my organization and as part of my project they want me to have form data output automatically to excel documents.  I have written a VBScript to do this and although it works great on my local system for testing, once I uploaded it to the IIS server it is throwing me the error:

ActiveX component can't create object: 'Excel.Application'

Can anyone help me with this?  I installed Excel on the server thinking that this would resolve the issue, but it did not.

If there is an alternate way to configure this altogether that would work too.  I mainly need to be able to take the data input to web forms and have it automatically output to an excel doc.

Thank you!

<script language=VBScript>
<!--
Option Explicit
 
Dim NewXlsFile, XLSTemplate
 
Sub SaveData()
Dim DirectoryName, EmpName, TodaysDateString, FullFileName
 
'MsgBox "Employee Name" & matreq.EmployeeName.value & _
 '"Company Name" & matreq.CompanyName.value & _
 '"Todays Date" & matreq.TodaysDate.value & _
 '"Managers Name" & matreq.ManagersName.value
 
Documents\temp\"
DirectoryName = "MaterialsRequestForms/"
EmpName = Replace(matreq.EmployeeName.Value," ","_",1) 'Replace spaces with underscore
TodaysDateString = Replace(matreq.TodaysDate.Value,"/","_",1) 'Replace slash with underscore
TodaysDateString = Replace(TodaysDateString,".","_",1) 'Replace slash with underscore
 
FullFileName = EmpName & "_" & TodaysDateString & ".xls"
 
XLSTemplate = DirectoryName & "MatReqTemplate.xls"
NewXlsFile = DirectoryName & FullFileName
 
 
SaveToExcel XLSTemplate, NewXlsFile
End Sub
 
Private Sub SaveToExcel(XLSTemplate, NewXlsFile)  ' Removed the object names from here.
Dim oWs, oWb
Dim objExcel
Dim objWorkbook
 
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(XLSTemplate) 'open the template workbook
'objExcel.Visible = True
objExcel.Visible = False 'This will cause the Excel application to not show
Set oWs = objExcel.ActiveSheet
Set oWb = objExcel.ActiveWorkbook
 
'First number is the row, second number is the column
oWS.Cells(4, 2).Value = matreq.EmployeeName.Value 'row 1, column 1
oWS.Cells(5, 2).Value = matreq.CompanyName.Value 'row 2, column 1
oWS.Cells(6, 2).Value = matreq.LE_InvestorRelationsManagerName.Value
oWS.Cells(7, 2).Value = matreq.TodaysDate.Value
oWS.Cells(8, 2).Value = matreq.DateAndTimeNeeded.value
oWS.Cells(9, 2).Value = matreq.NumberOf2006_2007Donors.value
oWS.Cells(12, 2).Value = matreq.LeadershipBrochures.value
oWS.Cells(13, 2).Value = matreq.Notes.value
oWS.Cells(17, 2).Value = matreq.PledgeOC.value
oWS.Cells(18, 2).Value = matreq.PledgeGeneric.value
oWS.Cells(19, 2).Value = matreq.PledgeSpanish.value
oWS.Cells(21, 2).Value = matreq.CampaignEnglish.value
oWS.Cells(22, 2).Value = matreq.CampaignSpanish.value
oWS.Cells(24, 2).Value = matreq.BookmarksOCUW_211.value
oWS.Cells(25, 2).Value = matreq.BookmarksVolunteerSolutions.value
oWS.Cells(27, 2).Value = matreq.PostersTwoSidedEnglish_Spanish.value
oWS.Cells(28, 2).Value = matreq.PostersVolunteerSolutions.value
oWS.Cells(29, 2).Value = matreq.PostersThermometers.value
oWS.Cells(31, 2).Value = matreq.EMCPacketsWithSpanishBrochure.value
oWS.Cells(32, 2).Value = matreq.ECMPacketsWithoutSpanishBrochure.value
oWS.Cells(33, 2).Value = matreq.ECMPacketsNewHireEN.value
oWS.Cells(34, 2).Value = matreq.ECMPacketsNewHireSP.value
oWS.Cells(35, 2).Value = matreq.ECMPacketsUWBaloons.value
oWS.Cells(39, 2).Value = matreq.VideosDateFrom.value
oWS.Cells(40, 2).Value = matreq.VideosDateTo.value
oWS.Cells(41, 2).Value = matreq.VideosComImpactVHS.value
oWS.Cells(42, 2).Value = matreq.VideosComImpactDVD.value
oWS.Cells(44, 2).Value = matreq.VideosComImpactLoop_VHS.value
oWS.Cells(45, 2).Value = matreq.VideosComImpactLoop_DVD.value
oWS.Cells(48, 2).Value = matreq.BannersDateFrom.value
oWS.Cells(49, 2).Value = matreq.BannersDateTo.value
oWS.Cells(50, 2).Value = matreq.BannersPodium.value
oWS.Cells(51, 2).Value = matreq.BannersOCUWEvent3x12.value
oWS.Cells(52, 2).Value = matreq.BannersOCUWEvents2x6.value
oWS.Cells(53, 2).Value = matreq.BannersCiyBanners.value
 
objExcel.DisplayAlerts = False 'Turn alerts off is saving the file as a file that already exists.
 'Turning off alerts will stop the message "The file already exists. Do you want to save?"
 
oWs.SaveAs(NewXlsFile)
oWb.Close
objExcel.Quit
'
Set oWs = Nothing
Set oWb = Nothing
Set objExcel = Nothing
 
End Sub
-->
</script>

Open in new window

0
OCUW
Asked:
OCUW
  • 11
  • 9
  • 5
  • +2
1 Solution
 
BadotzCommented:
Sounds like Excel is not installed on the server.
0
 
OCUWAuthor Commented:
I installed Excel on the server this afternoon and completed a reboot.
0
 
BadotzCommented:
And did you try it after that? (Sorry - have to ask the simple questions first)
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
BadotzCommented:
Ya know, you can open an Excel spreadsheet - like you do a database - and use SQL commands against it...
0
 
BadotzCommented:
Towards your original question: perhaps Server.CreateObject("Excel.Application") will do the trick?
0
 
OCUWAuthor Commented:
Ok, so now it is saying that: Variable is Undefined: "Server"...

Yes, I tried running this since installing Excel on the server.

Not sure whats meant about the SQL pull.
0
 
BadotzCommented:
Ah, you are running this on the client? Might be a permissions problem - the IUSR_SERVERNAME and IWAM_SERVERNAME (where "SERVERNAME" is the name of your server) users must have permission to create an Excel object.
0
 
OCUWAuthor Commented:
What permissions are required to "create an Excel object"?  They are both set to full control right now.  You are refering to setting these permissions on the web site through IIS correct?  And yes, I am running this on the client.
0
 
asawatzkiCommented:
This article should help you out.  There is probably a problem with the automation object for Excel.  Pay extra attention to the "Examine the automation server" section.

http://support.microsoft.com/kb/828550
0
 
OCUWAuthor Commented:
Well, I had read that before, and I did all the stuff under the Examine the automation server section and it all checked out.
0
 
asawatzkiCommented:
Do you have McAfee or another virus program that uses a "Script Blocker" feature?  If so, try temporarily disabling AV and running the script.
0
 
OCUWAuthor Commented:
I am thinking that the problem might possibly have to do with the scripting for where the tmplate file is located.  I have it set to MaterialsRequestForms/ which is a folder located within the WebSite, then the script later calls the file itself, which is located within this folder.  Does this look correct?

Disabling AV did not resolve the issue either.
0
 
asawatzkiCommented:
If it is failing on the ActiveX creation of Excel then that is most likely not your issue.  Does it say it is failing on line 35?  Also try changing it to wscript.createobject.
0
 
OCUWAuthor Commented:
It gave me the error: Variable undefined "wscript", this is the same error when I tried placing Server.CreateObject.
0
 
BadotzCommented:
>>Server.CreateObject.

It's on the client, people...
0
 
chandru_solCommented:
try removing option explicit from your vbscript
0
 
BadotzCommented:
>>try removing option explicit from your vbscript

Why? What possible positive effect will that have?
0
 
asawatzkiCommented:
On one of the clients you could try going in to Component Services and changing the DCOM permissions for Microsoft Excel so that anonymous has rights to all of them.  (Make sure you remove these permissions after the test because from a security standpoint they could be a problem).  

Also try adding the website to the trusted zone in IE.
0
 
chandru_solCommented:
You will know the error line number
0
 
BadotzCommented:
>>You will know the error line number

Huh? Option Explicit forces you to declare your variables before using them. It has nothing (directly) to do with errors.
0
 
chandru_solCommented:
Whoops! Yes you are right.
0
 
BadotzCommented:
Don't let my wife hear that ;-)
0
 
chandru_solCommented:
cheers :-)
0
 
OCUWAuthor Commented:
Ok, I found the fix.  As this was an ActiveX issue, I was able to resolve by going to the client workstation, opening IE > Tools > Internet Options > Security > (Internet Zone) Custom Level > and set the Initialize and Script ActiveX controls not marked as safe for scripting option under ActiveX controls and plug-ins.

The only question I have on this though is, will this negatively effect internet browsing?  I obviously just opened a hole in the security, but is it major?
0
 
BadotzCommented:
Depends on your users, and if this is an intranet (not internet) web application.
0
 
OCUWAuthor Commented:
It is IntRanet
0
 
BadotzCommented:
Then that lessens the security risk while running your app. Be aware, unless your Poor Users reset the security settings *before* surfing the inTERnet, they might be at risk. If possible, leave the changed settings to "Always prompt", eh?
0
 
asawatzkiCommented:
I agree with Badotz that you should leave it at Always prompt if that works.  Better to put leave it to the user to validate that what they are doing is safe, then letting anything in intranet do it automatically.
0
 
OCUWAuthor Commented:
This answer lead me to find the solution on my own in ActiveX
0
 
dsackerContract ERP Admin/ConsultantCommented:
Thank you for this thread. It helped me solve the same problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 11
  • 9
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now