Display Cell B1 value in Sheet1 of all the workbooks in a message box

[aman]

Display Cell B1 value of Sheet1 of all the workbooks in a message box

Hi All

I have a form and there is a command button on it. I want the code that will run when that commandbutton is pressed and it should check the first sheet "Sheet1" in all the workbooks in the folder C:\Folder\Temp.

And if the first sheet name is not "sheet1" then just ignore that workbook and move to next workbooks. If the first sheet name is "Sheet1" in a workbook then display the value present in cell B1 in a message box.

So if there are 5 workbooks in the folder C:\Folder\Temp and two of them hasn't got first sheet named "Sheet1" then display value of B1 in rest of the 3 sheets in a message box one by one.

I hope anyone can help me in this.

Thanks

 

[aman]

Is there anyone who can help me in this please?

Its very urgent please come up with your ideas.

Many thanks

 

[ByteMyzer]

You might try something like the following:

[COLOR="Navy"]Public Sub[/COLOR] B1Display([COLOR="navy"]ByVal[/COLOR] sDirectory [COLOR="navy"]As String[/COLOR])

[COLOR="navy"]Dim[/COLOR] xlApp [COLOR="navy"]As Object
Dim[/COLOR] xlBook [COLOR="navy"]As Object
Dim [/COLOR]xlSheet [COLOR="navy"]As Object

Dim[/COLOR] cFile [COLOR="navy"]As[/COLOR] Collection
[COLOR="navy"]Dim[/COLOR] vFile [COLOR="navy"]As Variant

Set[/COLOR] cFile = [COLOR="navy"]New[/COLOR] Collection

vFile = Dir(sDirectory & "\*.xls*")
[COLOR="navy"]Do While[/COLOR] vFile > ""
    cFile.Add [COLOR="navy"]CStr[/COLOR](vFile)
    vFile = Dir
[COLOR="navy"]Loop

Set[/COLOR] xlApp = CreateObject("Excel.Application")

[COLOR="navy"]For Each[/COLOR] vFile [COLOR="navy"]In[/COLOR] cFile

    [COLOR="navy"]Set[/COLOR] xlBook = xlApp.Workbooks.Open(sDirectory & "\" & vFile, , [COLOR="navy"]True[/COLOR])

    [COLOR="navy"]Set[/COLOR] xlSheet = xlBook.Sheets(1)
    [COLOR="navy"]If[/COLOR] xlSheet.Name = "Sheet1" [COLOR="navy"]Then[/COLOR]
        MsgBox xlSheet.Range("B1").Value
    [COLOR="navy"]End If

    Set[/COLOR] xlSheet = [COLOR="navy"]Nothing[/COLOR]

    xlBook.Close
    [COLOR="navy"]Set[/COLOR] xlBook = [COLOR="navy"]Nothing

Next[/COLOR] vFile

[COLOR="navy"]Set[/COLOR] cFile = [COLOR="navy"]Nothing

End Sub[/COLOR]

Example:
B1Display "C:\Folder\Temp"