• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3547
  • Last Modified:

CreateObject("Excel.Application") does not work

I tried running the below piece of code server side just to open excel .
<%
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
%>

It does not throw any error,simply executes and shows up nothing.Am I missing something?Is it with code or someother problem?

I could run the below script client side and open excel and everything seems to be fine.But when i try the same piece of code server side.It does not work.

<Script Language="VBScript">
    Sub RunScript
   
        strCopy = MyTable.InnerHTML
        document.parentwindow.clipboardData.SetData "text", strCopy

        Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
   
        Set objWorkbook = objExcel.Workbooks.Add()
       
        Set objWorksheet = objWorkbook.Worksheets(1)
        objWorksheet.Paste
       
        'Dim xlmodule 'VBComponent
        Set xlmodule = objWorkbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule
       
         ' Add a macro to the module...
       Dim strCode
       strCode = _
          "sub MyMacro()" & vbCr & _
          "   Columns(1).ColumnWidth = 60" & vbCr & _          
          "end sub"
       xlmodule.CodeModule.AddFromString strCode

       ' Run the new macro!
       objExcel.Run "MyMacro"
       
    End Sub
</Script>

0
madhujinka
Asked:
madhujinka
  • 5
  • 3
1 Solution
 
purplepomegraniteCommented:
Did you try my suggestion from the other question?  Server-side, CreateObject needs to be used like this:

Set objExcel = Server.CreateObject("Excel.Application")
0
 
purplepomegraniteCommented:
Incidentally, how is this script going to run server-side?  It is from an ASP page I presume?

If so, you need to close Excel afterwards.  Currently, every time you run the script (when it works), an instance of Excel will be opened.  This will not be closed again except when explicitly closed (objExcel.Quit), so you could overload the server with numerous instances of Excel.
0
 
madhujinkaAuthor Commented:
I tried using the below piece of code as you mentioned.It does not seem to work.Is it with some IIS permission issue?

Set objExcel = Server.CreateObject("Excel.Application")
        objExcel.Visible = True
       
        objExcel.Quit
        'release
        Set objExcel = Nothing
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
madhujinkaAuthor Commented:
Forgot to mention yes it is ASP in which the above script is running.
0
 
purplepomegraniteCommented:
The objExcel.Visible=true doesn't make sense in the context of an asp page.  When run from asp, the object will be created, and any actions taken - but there is no user context in which to display Excel (ASP doesn't run under a user account with a display).  So while you can get code to run, and control Excel (assuming installed on the server), you can't make Excel appear and must be careful to close it when finished.

What is it you are trying to do with the script?
0
 
madhujinkaAuthor Commented:
you mean the excel will not be shown or opened to user even if the code runs perfectly?Why does this don't work server side when it is working client side?

I could see some posts from other users in this site  telling they are able to see excel sheet when they are using the same piece of code.

I want to export a html table to excel and attach a macro to it and run the macro when it is opened .
0
 
purplepomegraniteCommented:
You are trying to open Excel on the client machine?

The problem at the moment is that you are asking the server to open an instance of Excel.  It will do this in the context that the code is running, specifically ASP.  ASP runs in a special user account created by IIS - you won't be able to see the Excel object created.  I'd be very interested to see where someone is saying that they are seeing Excel using equivalent code - it shouldn't be possible.

I am presuming that you will be running this script from a remote web-browser?  If so, then you actually want to open Excel on the local computer, which means you want the script itself to run on the local computer (not on the server).  Is this correct?
0
 
purplepomegraniteCommented:
Ok, I think I've found the problem.

I've created a test page here: http://www.cedit.biz:81/test.html

Doesn't need to be asp, as you are using client-side scripting.  The problem is that IE is blocking the creation of Excel from the web-browser (for security).  To get around this, you need to add the site to Trusted Sites, and then ensure the option:

Initialize and script ActiveX controls no marked as safe for scripting - needs to be either enabled or prompt.

With the above two changes, the script will run - however, this will need to be done for every client machine that needs to run this script.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now