Find Excel files that have hidden workbook
 

I am pretty new to coding in VB.NET. I wanted to write a code to search
through a list of Excel files and find those files that have a hidden
workbook. I tried to capture the value of
mExcelObj.ActiveWindow.Visible into a string and check if it is true or
false, based on which I could identify whether the Excel file has a
hidden workbook or not. Any guidance would be appreciated.

Thanks

Find Excel files that have hidden workbook
 

An XL file IS a workbook.
For me, outside of XL (in the OS/File System etc) it is a file, whilst when
opened in XL it is a workbook.
There can only be 1 workbook in a file. It can however have multiple
Worksheets.
Assuming you are referring to whether the WB is hidden when opened (the
default for Personal.xls), try this VBA.

Private Sub CommandButton1_Click()
Dim FileNames() As String
Dim i As Long
Dim WB As Workbook
Dim RetVal As Boolean

'Fill the values from list
'FileNames() = ???

For i = LBound(FileNames) To UBound(FileNames)
Set WB = Workbooks.Open(FileNames(i))
RetVal = IsWBHidden(WB)
'Save the result somewhere
WB.Close False
Next

End Sub

Private Function IsWBHidden(WB As Workbook) As Boolean
Dim i As Long
IsWBHidden = True
With WB.Windows
For i = 1 To .Count
If .Item(i).Visible = True Then
IsWBHidden = False
Exit Function
End If
Next
End With
End Function

NickHK

"sparrow" wrote in message
oups.com...
I am pretty new to coding in VB.NET. I wanted to write a code to search
through a list of Excel files and find those files that have a hidden
workbook. I tried to capture the value of
mExcelObj.ActiveWindow.Visible into a string and check if it is true or
false, based on which I could identify whether the Excel file has a
hidden workbook or not. Any guidance would be appreciated.

Thanks

Find Excel files that have hidden workbook
 

Thank you so much. It worked really well. Thanks once again for your
time.

NickHK wrote:
An XL file IS a workbook.
For me, outside of XL (in the OS/File System etc) it is a file, whilst when
opened in XL it is a workbook.
There can only be 1 workbook in a file. It can however have multiple
Worksheets.
Assuming you are referring to whether the WB is hidden when opened (the
default for Personal.xls), try this VBA.

Private Sub CommandButton1_Click()
Dim FileNames() As String
Dim i As Long
Dim WB As Workbook
Dim RetVal As Boolean

'Fill the values from list
'FileNames() = ???

For i = LBound(FileNames) To UBound(FileNames)
Set WB = Workbooks.Open(FileNames(i))
RetVal = IsWBHidden(WB)
'Save the result somewhere
WB.Close False
Next

End Sub

Private Function IsWBHidden(WB As Workbook) As Boolean
Dim i As Long
IsWBHidden = True
With WB.Windows
For i = 1 To .Count
If .Item(i).Visible = True Then
IsWBHidden = False
Exit Function
End If
Next
End With
End Function

NickHK

"sparrow" wrote in message
oups.com...
I am pretty new to coding in VB.NET. I wanted to write a code to search
through a list of Excel files and find those files that have a hidden
workbook. I tried to capture the value of
mExcelObj.ActiveWindow.Visible into a string and check if it is true or
false, based on which I could identify whether the Excel file has a
hidden workbook or not. Any guidance would be appreciated.

Thanks