Opening Excel in Safe Mode

Welcome Forums General PowerShell Q&A Opening Excel in Safe Mode

This topic contains 7 replies, has 2 voices, and was last updated by

 
Participant
8 months, 3 weeks ago.

  • Author
    Posts
  • #94393

    Participant
    Points: 1
    Rank: Member

    I have a script that open certain Excel workbooks and extracts data from them and then moves on to the next workbook. Some of these workbooks have some code that exits the user if a workbook is opened after a certain date but can be bypassed if opened in Safe Mode. The message that comes up causes someone to press OK on the popup message before the script continues.

    If I could open Excel in Safe Mode this message wuold not appear.

    The line that opens Excel is:

    $objExcel.Workbooks.Open($UW.fullname)

    Is there any way to force this open using Safe Mode instead of the default open method?

  • #94396

    Participant
    Points: 154
    Helping Hand
    Rank: Participant

    You might bypass Excel at all when you use the ImportExcel module. And as a positive side effect, it makes working with Excel files easier.

  • #94456

    Participant
    Points: 1
    Rank: Member

    Thank you, I saw a lot of examples of how to put data into an Excel workbook but not how to extract data with the ImportExcel module. I have a lot of fields that are named and some that are referenced by their sheet/cell number that I'd like to capture from multiple files and have it exported into a separate workbook.

    Because I added some code in the Workbook Open module that notified users that the file expired, the users are prompted and when they press OK, it kicks them out of the file and I get no data from it. Opening these files in safe mode bypasses the code and opens the file to extract the data.

    I'd be happy to do it without having to open Excel for every worksheet but I didn't see any information about ImportExcel to do what I need.

    • #94462

      Participant
      Points: 154
      Helping Hand
      Rank: Participant

      I have to admit that I haven't worked so much with the module myself. But as far as I understood you just have to get some data out of some Excel sheets, right? That shouldn't be that hard.

      but I didn't see any information about ImportExcel to do what I need

      So what do you need then? Import-Excel should be able to extract most of the data contained in an Excel file I thought.

  • #94503

    Participant
    Points: 1
    Rank: Member

    After reviewing all the available help for Import-Excel it does not appear that this will allow extracting data unless it's structured in columns and rows. I've reviewed the help and examples but nothing appears to suggest pulling named range data or cell referenced data.

    If there was an example of accessing an Excel file and extracting cell data, either by named range or by cell and page reference and exporting the captured data to cvs or other Excel file then I can work out the rest.

    So, open file, extract data, save data to cvs...

    In absence of this tool, and following my previous method, is there a way to open Excel in safe mode to bypass this code.

    As I enter this, I know it may sound like I'm trying bypass some security so maybe I'm not getting responses from a security perspective but I can open the file manually in safe mode, so that should ease any concerns.

    • #94506

      Participant
      Points: 154
      Helping Hand
      Rank: Participant

      I've reviewed the help and examples but nothing appears to suggest pulling named range data or cell referenced data.

      Did you try it at least with a test file? Even if there's no help or example it could work anyway.

      In absence of this tool, and following my previous method, is there a way to open Excel in safe mode to bypass this code.

      I'm almost sure that you've already found this but it's not what you was looking for: Description of the startup switches for Excel

      As I'm not working that much with Excel – mostly I am using it to display CSV files – it sound's like your Excel files are pretty "sophisticated" with a certain amount of formulas, macros or VBA plugins or something like this – it might be an option to "extend" these stuff to store the relevant data in a "coding" friendly format like CSV or even maybe simple text or XML or JSON.

  • #94582

    Participant
    Points: 1
    Rank: Member

    Okay, it was much simpler than I imagined and only involved setting the EnableEvents property in addition to a couple others that I was already setting. This should have been more obvious to me since we disable this in Excel VBA all the time to prevent actions from taking place during the running of the script.

    The entire ComObject section of code that matters then is:

    # Create an Object Excel.Application using Com interface
    $objExcel = New-Object -ComObject Excel.Application
    # Disable the 'visible' property so the document won't open in excel
    $objExcel.Visible = $false
    # Disable errors from Excel 
    $objExcel.DisplayAlerts = $true
    # Disable Events to prevent popups, etc.
    $objExcel.EnableEvents = $false
    • #94594

      Participant
      Points: 154
      Helping Hand
      Rank: Participant

      Finally. Great. I'm glad you found what you was looking for. 😀

      Have a nice Sunday.

The topic ‘Opening Excel in Safe Mode’ is closed to new replies.