Solved

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

Posted on 2007-12-05
30
557 Views
Last Modified: 2013-12-24
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
Comment
Question by:OCUW
  • 11
  • 9
  • 5
  • +2
30 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 20416268
Sounds like Excel is not installed on the server.
0
 

Author Comment

by:OCUW
ID: 20416275
I installed Excel on the server this afternoon and completed a reboot.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20416279
And did you try it after that? (Sorry - have to ask the simple questions first)
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20416283
Ya know, you can open an Excel spreadsheet - like you do a database - and use SQL commands against it...
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20416292
Towards your original question: perhaps Server.CreateObject("Excel.Application") will do the trick?
0
 

Author Comment

by:OCUW
ID: 20416323
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
 
LVL 29

Expert Comment

by:Badotz
ID: 20416340
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
 

Author Comment

by:OCUW
ID: 20416379
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
 
LVL 9

Expert Comment

by:asawatzki
ID: 20416380
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
 

Author Comment

by:OCUW
ID: 20416424
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
 
LVL 9

Expert Comment

by:asawatzki
ID: 20416433
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
 

Author Comment

by:OCUW
ID: 20416474
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
 
LVL 9

Expert Comment

by:asawatzki
ID: 20416488
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
 

Author Comment

by:OCUW
ID: 20416537
It gave me the error: Variable undefined "wscript", this is the same error when I tried placing Server.CreateObject.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20416616
>>Server.CreateObject.

It's on the client, people...
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 12

Expert Comment

by:chandru_sol
ID: 20417202
try removing option explicit from your vbscript
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20419597
>>try removing option explicit from your vbscript

Why? What possible positive effect will that have?
0
 
LVL 9

Accepted Solution

by:
asawatzki earned 500 total points
ID: 20420299
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
 
LVL 12

Expert Comment

by:chandru_sol
ID: 20421523
You will know the error line number
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20421573
>>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
 
LVL 12

Expert Comment

by:chandru_sol
ID: 20421691
Whoops! Yes you are right.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20421756
Don't let my wife hear that ;-)
0
 
LVL 12

Expert Comment

by:chandru_sol
ID: 20421800
cheers :-)
0
 

Author Comment

by:OCUW
ID: 20421957
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
 
LVL 29

Expert Comment

by:Badotz
ID: 20422002
Depends on your users, and if this is an intranet (not internet) web application.
0
 

Author Comment

by:OCUW
ID: 20422115
It is IntRanet
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20422152
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
 
LVL 9

Expert Comment

by:asawatzki
ID: 20422433
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
 

Author Closing Comment

by:OCUW
ID: 31413005
This answer lead me to find the solution on my own in ActiveX
0
 
LVL 20

Expert Comment

by:dsacker
ID: 20711707
Thank you for this thread. It helped me solve the same problem.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now