Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Location
    New York, New York
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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/libr...ice.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.
    [pre]Stephan Ip
    www.CustomOfficeDev.com
    [/pre]

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Stephan_services View Post
    ... 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 <script></script> 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.

  4. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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)

  5. #4
    New Lounger
    Join Date
    Feb 2009
    Location
    New York, New York
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    [pre]Stephan Ip
    www.CustomOfficeDev.com
    [/pre]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •