Solved

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

Posted on 2007-12-05
30
562 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IIS Cache Control settings 2 63
Internal to DMZ IIS Authentication. 3 94
add 'N to prepared ASP/VB insert statement 1 37
ReadyAPI 1.9.0 2 44
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

734 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