Excel Sheets in a column

I can't see a use for this and I wrote it. :smile: Not a good way to start.

I did it as an exercise and if it helps anyone else, that is a good thing. Also if anyone can improve it, go for it including any correction.

It is a VB script which opens a excel file and reads out the sheet names to a column (needed to do something not in metadata). Because of this you'll need excel installed and I wouldn't open it on a huge number of excel files - although Dopus does handle that part well in any case, it is just slow but quicker then me opening each.

It is basically the column example code (thanks Leo) with a Excel application call, open hidden with a error check on the open file call. Then a loop to build the sheet name variable.

Kept my own groupings in it (excel files, Directories, Not excel) but if you comment these out you'll get groups on the sheet names.

Also left in a bit of cell reading code, so you could have it pull out the contents of any cell. Note it could be a range if desired by putting A1:B2 in it or a combination cells and sheets etc.

Had a quick search if something similar had been done (surprise no) and saw a few requests to read new file names etc from excel. You could easily modify this script to a command one and read in cells for renaming files if you wished in a loop. Note other Office stuff works the same way (Word, Outlook).

Note I don't frequent here much, so please forgive me if I don't reply to any questions this may generate quickly.


option explicit

' This is a script for Directory Opus.
' See https://www.gpsoft.com.au/DScripts/redirect.asp?page=scripts for development information.
' Called by Directory Opus to initialize the script
Function OnInit(initData)
	Dim cmd

	initData.name = "Column Excel Spy"
	initData.version = "V1.0"
	initData.copyright = "(c) 2022 no its not"
	initData.desc = "Reads sheet names out of excel files"
	initData.default_enable = true
	initData.min_version = "12.0"
	initData.default_enable = true

	'Create a new ScriptColumn object and initialize it to add the column to Opus
    Set cmd = initData.AddColumn()
    cmd.name = "ExcelSpy"
    cmd.method = "OnExcelSpy"
    cmd.label = "Excel Spy"
    cmd.autogroup = false      ' we provide our own grouping information
    cmd.autorefresh = true      ' refresh column when files change
    cmd.justify = "left"
End Function

Function OnExcelSpy(ScriptColumnData)

	Dim item
	Dim EValue
	Dim Excel, Book, Sheet

	On Error Resume Next ' in case opening file in Excel fails, step on and test
	' ScriptColumnData is a ScriptColumnData object. first check that this is the right column (it should be since we only added one)
    If (ScriptColumnData.col <> "ExcelSpy") Then
        Exit Function
	End If

	Set Item = ScriptColumnData.item

	If (Item.is_dir) Then
		ScriptColumnData.value = ""
        ScriptColumnData.group = "Directories"
        ScriptColumnData.sort = 3
    ElseIf (Item.ext = ".xls" or Item.ext = ".xlsx") Then

		Set Excel = CreateObject("Excel.Application")	'Creating an Excel Object
		Excel.visible = False							'Making an Excel Object visible
		Set Book = Excel.Workbooks.open(Item.realpath)	'Opening an Excel file

		If Err.Number <> 0 then
			ScriptColumnData.value = Err.Description
			ScriptColumnData.group = "Not Excel files"
			ScriptColumnData.sort = 2
			Exit Function
		End If

		'Use this to read a cell(s) out to the column
		'Set Sheet = Book.Worksheets(1)    'Referring Sheet1 of excel file
		'EValue = Sheet.Range("A2").Value  'Value from the specified cell will be read and shown may wish to change

		' Use this to read just sheet names to string
		For Each Sheet In Book.Worksheets
			If (EValue = "") Then
				EValue = Sheet.Name
				EValue = EValue & ", " & Sheet.Name
			End If

		ScriptColumnData.value = EValue
        ScriptColumnData.group = "Excel files"
        ScriptColumnData.sort = 1

		Book.Close				'Closing a Workbook
		Excel.Quit				'Exit from Excel Application
		Set Sheet = Nothing		'Releasing Worksheet object
		Set Book = Nothing		'Releasing Workbook object
		Set Excel = Nothing		'Releasing Excel object

		ScriptColumnData.value = ""
        ScriptColumnData.group = "Not Excel files"
        ScriptColumnData.sort = 2
	End If

End Function