Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-12-05
30
Medium Priority
?
564 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 
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 1500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
A phishing scam that claims a recipient’s credit card details have been “suspended” is the latest trend in spoof emails.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

721 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