I am trying to open an Excel file using Powershell, and then get the name (or index number) of the active worksheet.
(The end goal is to print/export the active worksheet as a PDF.)
$ExcelFileName = 'C:\Users\Me\Desktop\graph.xlsx'
# creates path to excel file
$Excel = New-Object -COMObject Excel.Application
# creates an excel com object
$Excel.Workbooks.Open($ExcelFileName)
# opens the workbook file
$WorkSheetName = $excel.WorksheetView.Sheet
# supposedly returns the name of the active work sheet
$WorkSheetObject = $excel.Workbooks.Item($WorkSheetName)
# error happens here when trying to create worksheet object
$WorkSheetObject.ExportAsFixedFormat(0,[ref]$NewFileName)
# this code would export worksheet to pdf
This code produces an error (see below). The Workbooks.Item method seems to require the sheet name as a string or integer, which WorksheetView.Sheet is not returning. Maybe.
Invalid Index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
At line:9 char:5
$WorkSheetObject = $excel.Workbooks.Item($WorkSheetName))
I've also tried using these lines of code to return the active sheet name or index number, but I get the same error when I pass them as the $WorkSheetName (see below).
$ActiveSheet = $excel.Workbook.ActiveSheet
$WorkSheetName = $ActiveSheet.name
$WorkSheetObject = $excel.Workbooks.Item($WorkSheetName)
I haven't found anything else to answer this in the Microsoft docs or on forums, so thanks for any help you can offer.
CodePudding user response：
You have to mine for what you are after if you do not know the full namespace already. For Example:
$ExcelFileName = 'D:\temp\Test.xlsx'
$ExcelApp = New-Object -ComObject Excel.Application
$ExcelApp.Workbooks.Open($ExcelFileName)
# Results
<#
...
ActiveChart :
ActiveSheet : System.__ComObject
...
#>
$ExcelApp.Work
# Results
<#
Workbooks WorkbookAddinInstall WorkbookAfterXmlImport WorkbookBeforeXmlExport WorkbookNewChart WorkbookPivotTableOpenConnection
WorksheetFunction WorkbookAddinUninstall WorkbookBeforeClose WorkbookBeforeXmlImport WorkbookNewSheet WorkbookRowsetComplete
Worksheets WorkbookAfterSave WorkbookBeforePrint WorkbookDeactivate WorkbookOpen WorkbookSync
WorkbookActivate WorkbookAfterXmlExport WorkbookBeforeSave WorkbookModelChange WorkbookPivotTableCloseConnection
Microsoft.Office.Interop.Excel.Workbooks Workbooks {get;}
#>
<#
Note that there is no property called '$ExcelApp.WorksheetView.Sheet' at this level of the Excel COM.
So all the remaining code does not matter.
However, there is,
#>
$ExcelApp.Worksheets
# Results
<#
Worksheets
#>
# So,
$ExcelApp.Active
<#
ActiveCell ActiveDialog ActiveMenuBar ActiveProtectedViewWindow ActiveWindow ActivateMicrosoftApp
ActiveChart ActiveEncryptionSession ActivePrinter ActiveSheet ActiveWorkbook
Microsoft.Office.Interop.Excel.Range ActiveCell {get;}
#>
# Now, digging further
$ExcelApp.ActiveSheet.
# Results
<#
_AutoFilter EnableFormatConditionsCalculation Previous __PrintOut Drawings PrintOut
_CodeName EnableOutlining PrintedCommentPages __SaveAs DropDowns PrintPreview
_DisplayRightToLeft EnablePivotTable ProtectContents _CheckSpelling Equals Protect
_Sort EnableSelection ProtectDrawingObjects _Evaluate Evaluate Rectangles
Application FilterMode Protection _ExportAsFixedFormat ExportAsFixedFormat ResetAllPageBreaks
AutoFilter HPageBreaks ProtectionMode _PasteSpecial GetHashCode SaveAs
AutoFilterMode Hyperlinks ProtectScenarios _PrintOut GetLifetimeService Scenarios
Cells Index QueryTables _Protect GetType ScrollBars
CircularReference ListObjects Rows _SaveAs GroupBoxes Select
CodeName MailEnvelope Scripts Activate GroupObjects SetBackgroundPicture
Columns Name ScrollArea Arcs InitializeLifetimeService ShowAllData
Comments NamedSheetViews Shapes Buttons Labels ShowDataForm
CommentsThreaded Names SmartTags Calculate Lines Spinners
ConsolidationFunction Next Sort ChartObjects ListBoxes TextBoxes
ConsolidationOptions OnCalculate StandardHeight CheckBoxes Move ToString
ConsolidationSources OnData StandardWidth CheckSpelling OLEObjects Unprotect
Creator OnDoubleClick Tab CircleInvalid OptionButtons XmlDataQuery
CustomProperties OnEntry TransitionExpEval ClearArrows Ovals XmlMapQuery
DisplayAutomaticPageBreaks OnSheetActivate TransitionFormEntry ClearCircles Paste Range
DisplayPageBreaks OnSheetDeactivate Type Copy PasteSpecial
DisplayRightToLeft Outline UsedRange CreateObjRef Pictures
EnableAutoFilter PageSetup Visible Delete PivotTables
EnableCalculation Parent VPageBreaks DrawingObjects PivotTableWizard
AutoFilter _AutoFilter () {get}
#>