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?
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.
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.
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.
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.
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
