none
Unable to create workbook_open event dynamically from vb script in sql server (OS -windows 2008 server) RRS feed

  • Question

  • Hi All,

     

    We are facing one issue since last 3 weeks, requesting you to kindly help me on this.

    Issue details are mentioned below:

     

    Problem Statement:

                    Unable to create workbook_open event  dynamically from vb script in sql server (OS -windows 2008 server)

     

    Issue details:
                    We have a VB script code (test.vbs), it opens an excel sheet and calls one function, this function will dynamically create an workbook_open event.

                    We are executing this VB script from sql server 2008  using ‘xp_cmdshell’ command and it has  ##xp_cmdshell_proxy_account## on the server on the server.

     

    Execution Command:
                    xp_cmdshell 'C:\windows\System32\CScript.exe //B D:\test.vbs "D:\test.xls"


    Success Scenario:

    When we log onto the server remotely with the proxy account and execute the query from local system (Query Analyzer), then above  command executes successfully.

     

    Failure Scenario:

    whenever we log off from the remote server (Or no active login to remote server) and and execute the query from local system (Query Analyzer), we are getting below error.
                   
    Error: Object variable or With block variable not set

     

    Code Snippet:

    Code to create an event dynamically.


     Dim VBComp As VBIDE.VBComponent
            Dim CodeMod As VBIDE.CodeModule
            Dim LineNum As Long
            Const DQUOTE = """" ' one " character

    With CodeMod
               LineNum = .CreateEventProc("Open", "Workbook")
               LineNum = LineNum + 1
                .InsertLines LineNum, _
                "   RunTestRpt"
    End With

    I am getting the issue exactly at with block.


    I think this is the issue with windows 2008 permissions only, the same code is working fine on windows 2003 server.

    Please help me on this issue.

     

     

    Thanks,

    Raja.

     

    • Moved by Martin_Xie Wednesday, January 18, 2012 2:44 AM Redirect to Scripting Forum for better support. (From:Visual Basic Language)
    • Moved by Richard MuellerMVP Saturday, January 21, 2012 1:28 AM VBA question (From:The Official Scripting Guys Forum!)
    Friday, January 13, 2012 6:32 AM

All replies

  • Hi

    Most often this error refer to that the object in not created and I can see 2 lines where this is occuring

    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule

    These 2 lines are getting ONLY reference of the object but not creating an object. To create it, you should use the "New" keyword. So

    Dim VBComp As New VBIDE.VBComponent
    Dim CodeMod As New VBIDE.CodeModule


    Friday, January 13, 2012 7:36 PM
  • Thanks Samir for your fast help.

    Hi Raja,

    Welcome to MSDN Forum.

     For VBScript questions, you will get better support at related VBScript forums.

    Official Scripting Forum http://social.technet.microsoft.com/Forums/en-US/ITCG/threads
    A Non-Microsoft VBScript Forum
    http://www.visualbasicscript.com/


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 18, 2012 2:42 AM
  • This code is not VBScript.

    Dim VBComp As VBIDE.VBComponent
            Dim CodeMod As VBIDE.CodeModule
            Dim LineNum As Long
            Const DQUOTE = """" ' one " character

    With CodeMod
               LineNum = .CreateEventProc("Open", "Workbook")
               LineNum = LineNum + 1
                .InsertLines LineNum, _
                "   RunTestRpt"
    End With

     

    This is VBA.  You need to post this in the Excel developers forum.

     

    Excell cannot run correctly as a service or under a service.  It will never be reloiable.  Microsoft has documented this very well.

     

     

     


    jv
    Wednesday, January 18, 2012 2:52 AM