merge xls and xlsx work books protected and hidden sheet

[Freshbambam]

Good afternoon,

BACKGROUND:-

I am using a bastardisation of Merge data from all workbooks in a folder (1)</SPAN></SPAN>
from Ron de Bruin, link:-

http://msdn.microsoft.com/en-us/library/cc837974.aspx</SPAN></SPAN>

</SPAN>
The difference is that I have to extract data from protected books on a hidden sheet (all books are unlocked with the same password and the hidden sheet name is the same).

I have added the following code:-

HD_Array = Array("file_name", "Project", "Name", "Task", "Amount")
Range("A1:D1").Value = HD_Array
' Start at row 2
rnum = 2
' Loop through all files in the myFiles array.
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(FNum), _
Password:="pass12", WriteResPassword:="pass12", UpdateLinks:=0)

Sheets("data_1").Visible = True ' This is the named sheet.
' Change here if other sheet required.
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resume Next
'***************************************
' Change this range if the units change
With mybook.Worksheets("data_1") 'sheet data copied from
Set sourceRange = .Range("a5:D50") 'range that is copied
End With


PROBLEM:-
On my machine it opens and extracts from both xls and xlsx files. Which is great.

The problem that I am encountering is that when I send this to a colleague it will only work on xls files.

Is it something to do with the configuration of excel (2007) or a code problem?

Or is my code a bit sh1tty, and needs improving?

Any suggestions?
</SPAN>

 

[Freshbambam]

Any ideas anyone?

 

[Freshbambam]

Bounce.
If I change the code to copy xlsx file types only it will only copy the first file info.

Any ideas, please?