none
Work on an open excel (POWERSHELL) RRS feed

  • Question

  • I've this Workbook, which I want to update.

    All I want is - powershell to update this already opened workbook.

    So I am looking for option to activate sheet on an already open Excel workbook.

    $xl=New-Object -ComObject "Excel.Application"
    $wb=$xl.Workbooks.open("C:\TEMP\Excel.xls")
    $xlWorkSheet=$wb.ActiveSheet
    $cells = $xlWorkSheet.Cells
    $cells=$ws.Cells
    $cells.item(1,1)="Server NAme"

    I had this code to be used. But this is opening a new file. And if I try to open the same file, it opens in readonly mode. I waish to APPEND already open workbook.

    Vivek

    Thursday, May 22, 2014 11:42 AM

Answers

  • That's weird, I would swear I tried $xl.workbooks and received only one object, but you're right, I get two.  And I forgot about the com instance I created.  So using 

    $xl=[Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
    $excel = $xl.workbooks | ?{$_.FullName -eq "c\temp\temp.xls"}
    

    should attach to the target excel spreadsheet.



    I hope this post has helped!

    • Marked as answer by Itsvivekg Thursday, May 29, 2014 7:40 AM
    Friday, May 23, 2014 10:53 PM

All replies

  • $xl=[Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")

    ¯\_(ツ)_/¯

    Thursday, May 22, 2014 12:24 PM
  • Thanks for reply.

    But wouldnt it pick all the active sessions? I will be running this script on a jump server, where we have multiple  users logged in and using EXCEL. Further, how to activate that particular excel Workbook.

    Vivek

    Friday, May 23, 2014 2:40 AM
  • Basically I am looking foe PS equivalent of VBA -

    Application.Workbooks("c\temp\temp.xls).Activate

    Friday, May 23, 2014 2:51 AM
  • The environments are not reproducible.


    ¯\_(ツ)_/¯

    Friday, May 23, 2014 3:26 AM
  • jrv's code example will work if the script/code is being executed as the user who's excel instance you want to access.  And only if it's the only instance they have open.  I tried playing around with it and researching other methods for Runtime.InteropServices.Marshal but I couldn't find anything useful.  Of course that doesn't mean nothing else exists - I just couldn't find anything in a reasonably dedicated effort spanning 5 minutes.  I could access the Excel instance, but with more than one workbook open, I could not reliably access a particular instance.

    I hope this post has helped!

    Friday, May 23, 2014 4:49 PM
  • Excel has only one instances

    with multiple documents.


    ¯\_(ツ)_/¯

    Friday, May 23, 2014 5:15 PM
  • I don't think that's entirely accurate.  I have three documents open:


    I hope this post has helped!


    Friday, May 23, 2014 6:24 PM
  • But you're right, I miss-spoke, the code will only work with one Excel document open.  Regardless of the number of 'instances' or 'documents' it will most likely be unreliable with more than one of anything open other than the document you want to manipulate.

    I hope this post has helped!

    Friday, May 23, 2014 6:27 PM
  • Rhys - all

    Excel runs single instance by default except if you launch it using COM.  It will then opena new instance with each COM instantiation.  This is and has been the Excel behavior since the beginning.

    If you open two excel documents  directoly you will be able to get both in the Excel instance.  I opened two separate workbooks and both are available. 

    PS C:\scripts> $xl=[Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
    PS C:\scripts> $xl.Workbooks.count
    2

    There can be only one Active document at a time but both documents are available.

    PS C:\scripts> $xl.Workbooks.Item(1).Name
    exjunk.csv
    PS C:\scripts> $xl.Workbooks.Item(2).Name
    test.xlsx
    PS C:\scripts> $xl.ActiveWorkbook.Name
    test.xlsx
    PS C:\scripts> $xl.Workbooks.Item(2).Activate()
    PS C:\scripts> $xl.ActiveWorkbook.Name
    test.xlsx
    PS C:\scripts> $xl.Workbooks.Item(1).Activate
    PS C:\scripts> $xl.ActiveWorkbook.Name
    exjunk.csv

    We use this quite often with no issues.

    I clicked on the Excel icon on the start menu and it opened Excel with a blank "Book1" and title of "Book1 - Excel"

    PS C:\scripts> $xl.Workbooks.count
    3
    PS C:\scripts> $xl.ActiveWorkbook.Name
    Book1

    Notice the count is now three and the new active workbook is the newest one opened.

    Now I selected another WB by clicking on it on the task bar.  Here is what PowerShell reports.

    PS C:\scripts> $xl.ActiveWorkbook.Name
    exjunk.csv

    I know non-programmers have a bit of an issue handling how this works.  It is by design and is critical to how Office products are able to work together and communicate seamlessly so well.  It is what made MS Office the defacto Office Suite. All efforts to unseat it have failed.

    It is also possible to disable this behavior in Excel.


    ¯\_(ツ)_/¯

    Friday, May 23, 2014 7:53 PM
  • Now let's open Excel using COM.

    PS C:\scripts> $xl2=New-Object -ComObject Excel.Application
    PS C:\scripts> $xl2.Visible
    False
    PS C:\scripts> $xl2.Visible=$true

    Now test to see how many books are open.

    PS C:\scripts> $xl.Workbooks.count
    3
    PS C:\scripts> $xl.ActiveWorkbook.Name
    exjunk.csv

    What is happening with the new Excelo?

    PS C:\scripts> $xl2.Workbooks.Count
    0

    So we see that using COM to launch Excel as an Application makes it a private copy.  It is invisible to all other copies launched by COM or by the Windows user.


    ¯\_(ツ)_/¯

    Friday, May 23, 2014 7:58 PM
  • But you're right, I miss-spoke, the code will only work with one Excel document open.  Regardless of the number of 'instances' or 'documents' it will most likely be unreliable with more than one of anything open other than the document you want to manipulate.

    I hope this post has helped!

    If the user launches excel and opens documents in any way except through automation then all documents will be visible in the Retrieved object and all can be used  You will get popups on the user screen when trying to do things with the users document.  The user will be asked if they want to allow changes.   This can be turned of if you launch with COM.  It cannot if you attach to the users copy.

    The two methods of attaching to Excel serve different purposes.  To fully understand how automation works and how it works with Office you need to consult the developer documentation on InProc COM and COM Application servers and Microsoft Office automation and object model.


    ¯\_(ツ)_/¯

    Friday, May 23, 2014 8:03 PM
  • That's weird, I would swear I tried $xl.workbooks and received only one object, but you're right, I get two.  And I forgot about the com instance I created.  So using 

    $xl=[Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
    $excel = $xl.workbooks | ?{$_.FullName -eq "c\temp\temp.xls"}
    

    should attach to the target excel spreadsheet.



    I hope this post has helped!

    • Marked as answer by Itsvivekg Thursday, May 29, 2014 7:40 AM
    Friday, May 23, 2014 10:53 PM
  • This is why I harp on the Excel object model:

    $xl=[Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application')
    PS C:\scripts> $xl.Workbooks.Item('Book1.xlsx').FullName
    C:\scripts\Book1.xlsx

    Note that we can get the workbook by name

    We do not want to enumerate anything when unnecessary.  It is slow and can bean issue.

    Ideally you would not want to create references as they can keep Excel from shutting down.  For short simple scritps I seldom set references outside of Excel.

    $xl.Workbooks.Item('Book1.xlsx'0.Activate()

    Then use it like this:
    $xl.ActiveWorkbook.Name
    $xl.ActiveWorkbook.Worksheets.Item('MuSheet').Activate()
    .... somework
    $xl.ActiveSheet.UsedRange.AutoFit()

    Useful things are Copy/Paste, select range and format range.


    ¯\_(ツ)_/¯


    • Edited by jrv Saturday, May 24, 2014 12:12 AM
    Saturday, May 24, 2014 12:11 AM
  • Thanks JRV

    SO Something like this:

    $xl=[Runtime.InteropServices.<USER>]::GetActiveObject('Excel.Application')
    $xl.Workbooks.Item('Book1.xlsx').Activate()

    Activating the workbook is thing I am looking for.

    One question (I havent tried it yet) - <user> will be something like - Domain\user or just the user.

    So the question is, what to use - WHOAMI or [Environment]::UserName.

    Vivek

    Saturday, May 24, 2014 4:43 AM
  • Marshal is the name of a .NET class, not a user's name.
    Saturday, May 24, 2014 5:28 AM
  • You cannot attach to another users session with any method.

    ¯\_(ツ)_/¯

    Saturday, May 24, 2014 10:38 AM
  • Thanks for the thing. It worked like my charm :-P.

    Vivek

    Thursday, May 29, 2014 7:41 AM
  • There is no hope for the undead.  (That would be those still breathing).

    I thought "Marshall" was the first name of Wyatt....Earp ;)

    Glad you were able to sort it out.


    ¯\_(ツ)_/¯



    • Edited by jrv Thursday, May 29, 2014 8:10 AM
    Thursday, May 29, 2014 8:09 AM