Question

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

Asked by: OCUW

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>

                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:

Select allOpen in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2007-12-05 at 15:18:57ID23004763
Tags

vbscript

,

excel

,

form

Topics

VB Script

,

FrontPage-Expressions

,

Microsoft IIS Web Server

Participating Experts
4
Points
500
Comments
30

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. VBScript?
    How do I create dir boxes, filelist boxs in VBscript? Many Thanks
  2. links to vbscript and frontpage
    I need URL links to frontpage and vbscript web design technology.
  3. VBScript
    I use VBScript "Set fso = CreateObject("Scripting.FileSystemObject")" in my HTML,and I can get the expectant result when I test in local,but the error "ActiveX components cannot create object 'Scripting.FileSystemObject'" is displayed when I visi...
  4. VBscript.
    How can I Run my Notepad file (c:\testfile.txt)from asp? Please Help! <!--#include file="inc/adovbs.inc"--> <script language="vbScript"> Sub CreateTextFile dim txtFile,fso,a txtFile = "c:\testfile.txt" Set fso = CreateObject(&quo...
  5. vbscript compiler?
    i write a bunch of code in vb6 and then i have to deploy it in vbscript. moving/testing/debugging the code is a pain. isnt there some sort of tool for vb6 that can verify my vb6 code to be vbscript compliant? it is mainly for DTS packages and ASP.. but since i want the fle...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: BadotzPosted on 2007-12-05 at 15:38:24ID: 20416268

Sounds like Excel is not installed on the server.

 

by: OCUWPosted on 2007-12-05 at 15:39:59ID: 20416275

I installed Excel on the server this afternoon and completed a reboot.

 

by: BadotzPosted on 2007-12-05 at 15:40:48ID: 20416279

And did you try it after that? (Sorry - have to ask the simple questions first)

 

by: BadotzPosted on 2007-12-05 at 15:41:52ID: 20416283

Ya know, you can open an Excel spreadsheet - like you do a database - and use SQL commands against it...

 

by: BadotzPosted on 2007-12-05 at 15:43:43ID: 20416292

Towards your original question: perhaps Server.CreateObject("Excel.Application") will do the trick?

 

by: OCUWPosted on 2007-12-05 at 15:52:10ID: 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.

 

by: BadotzPosted on 2007-12-05 at 15:56:07ID: 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.

 

by: OCUWPosted on 2007-12-05 at 16:02:04ID: 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.

 

by: asawatzkiPosted on 2007-12-05 at 16:02:05ID: 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

 

by: OCUWPosted on 2007-12-05 at 16:12:57ID: 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.

 

by: asawatzkiPosted on 2007-12-05 at 16:15:38ID: 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.

 

by: OCUWPosted on 2007-12-05 at 16:24:37ID: 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.

 

by: asawatzkiPosted on 2007-12-05 at 16:27:32ID: 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.

 

by: OCUWPosted on 2007-12-05 at 16:36:59ID: 20416537

It gave me the error: Variable undefined "wscript", this is the same error when I tried placing Server.CreateObject.

 

by: BadotzPosted on 2007-12-05 at 17:02:16ID: 20416616

>>Server.CreateObject.

It's on the client, people...

 

by: chandru_solPosted on 2007-12-05 at 19:39:23ID: 20417202

try removing option explicit from your vbscript

 

by: BadotzPosted on 2007-12-06 at 06:05:38ID: 20419597

>>try removing option explicit from your vbscript

Why? What possible positive effect will that have?

 

by: asawatzkiPosted on 2007-12-06 at 07:24:08ID: 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.

 

by: chandru_solPosted on 2007-12-06 at 09:46:10ID: 20421523

You will know the error line number

 

by: BadotzPosted on 2007-12-06 at 09:53:04ID: 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.

 

by: chandru_solPosted on 2007-12-06 at 10:07:02ID: 20421691

Whoops! Yes you are right.

 

by: BadotzPosted on 2007-12-06 at 10:16:28ID: 20421756

Don't let my wife hear that ;-)

 

by: chandru_solPosted on 2007-12-06 at 10:21:52ID: 20421800

cheers :-)

 

by: OCUWPosted on 2007-12-06 at 10:41:22ID: 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?

 

by: BadotzPosted on 2007-12-06 at 10:46:35ID: 20422002

Depends on your users, and if this is an intranet (not internet) web application.

 

by: OCUWPosted on 2007-12-06 at 10:58:49ID: 20422115

It is IntRanet

 

by: BadotzPosted on 2007-12-06 at 11:05:32ID: 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?

 

by: asawatzkiPosted on 2007-12-06 at 11:37:25ID: 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.

 

by: OCUWPosted on 2007-12-06 at 15:43:47ID: 31413005

This answer lead me to find the solution on my own in ActiveX

 

by: dsackerPosted on 2008-01-21 at 19:31:32ID: 20711707

Thank you for this thread. It helped me solve the same problem.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...