Solved

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

Posted on 2007-12-05
30
561 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Lync server 2013 or Skype for business Backup Service Error ID 4049 – After File Share Migration
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 automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
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…

830 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