• Script object in VBA

    Author
    Topic
    #470279

    Hi folks, can someone point me to where I can get some more information with working with the Microsoft Office Script object using VBA?

    The only thing I’ve been able to find is this, and it’s just really limited:
    http://msdn.microsoft.com/en-us/library/aa141424%28v=office.10%29.aspx

    It does explain how I can add and delete scripts from a Word or Excel file, but what I really need to do is to be able to programmatically edit the embedded script.

    Thanks.

    Viewing 2 reply threads
    Author
    Replies
    • #1233993

      … what I really need to do is to be able to programmatically edit the embedded script.

      Hmmm, scripts. What kinds of scripts? According to the documentation you linked (Understanding Script Object Properties), there is a scriptText property that contains the entire contents between the tags. Does that let you do what you want?

      If you are actually trying to edit VBA attached in a code module, you probably will need to use the VBA Extensibility library.

    • #1234113

      The download of the MS help file for VB Script is here…
      Windows Script 5.6 Documentation
      It can be used in and with VBA in the VBEditior.
      ‘–
      Jim Cone
      Portland, Oregon USA
      Special Sort Excel add-in (review)

    • #1234392

      I appreciate you guys getting back to me. Maybe I should explain more clearly what I’m trying to do here.

      Basically I’m trying to manipulate metadata stored in Excel worksheets as embedded XML scripts, and yes, as Jefferson pointed out, using the ScriptText property as follows does allow me to retrieve the XML:

      Code:
      XMLScript = Worksheets("BExRepositorySheet").Scripts(1).ScriptText
      Debug.Print XMLScript
      

      will yield approximately over 12,000 lines of XML metadata stored in the first Scripts collection in the worksheet.

      What I need to do is programmatically alter a little segment of that XML. According to the documentation I found, you can add and remove scripts from the Scripts collection, but is there a way to just edit it. Otherwise I would need to write all those 12,000+ lines of XML to a temporary file, do my metadata manipulation, delete the original script, then write the contents of the temporary file back into a new script. Seems kind of clunky.

      I was just figuring someone else here must have worked with embedded scripts in Word or Excel before and go give me some more information than what I’ve found so far.

      Thanks.

    Viewing 2 reply threads
    Reply To: Script object in VBA

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: