About Me

My photo

I'm a developer in SSIS ETL, C#, ASP.NET, and PowerShell and reside in Phoenix, Arizona. Professional classical guitarist, Renaissance lutenist.

Saturday, July 30, 2011

PowerShell/Excel Automation with ACE drivers

This is to document my experience using the ACE drivers to automate generating a report from a multisheeted Excel spreadsheet.
The spreadsheet was created by someone else, and the worksheet names included a hyphen, for example, "Project A - Client1". I experienced PowerShell exception errors when I tried to list the data, with the error message stating the punctuation is not allowed in the TABLE_NAME (which is the worksheet name) property, even though it's not a problem for Excel itself.
I had to find a way to change the worksheet names programmatically because renaming the Excel sheet by right clicking the tab did not change the name in the TABLE_NAME property itself for some reason, even if I closed the spreadsheet file and powershell session and reopened them.

No comments: