Problem with looping

[Ajeans]

Hi all,

I'm an *absolute* novice using Excel VB in Office 2011 for Mac. My approach is to use a combination of the record function, and searching fora like these for posted code which does something similar to what I want.

On this basis, I have come up with the below, which is nice and short and is designed to do a few simple things: firstly, it opens a new workbook called "Analysed experiments" (that works at least!), then it needs to look in an existing folder called "Individual Ca experiments" (full path in the code below) and run a few lines of code on every excel workbook in that folder, of which there are an uncertain number; this code should then harvest a single column of data from a sheet called "graphs" which will be present in each wb, and then pastes these side by side in the above new wb "Analysed experiments".

I am getting a consistent error at the line "StrFile = Dir(strPath, MacID("XLS5"))" - object not recognised. I have tinkered with this and changed it to a few plausible-sounding things, but no use.

Can anybody help me by telling me what is wrong? Much gratitude if so....I've spent hours trying to get this working now, but I really don't know what I'm doing TBH. It could be nonsense code for all I know, I wish I had some training in this!

Cheers,

Alex

Here's the code:


[vba]Sub Ca_data_harvesting()
'
' Ca_data_harvesting Macro
'
Set NewBook = Workbooks.Add
With NewBook
.Title = "Analysed experiments"
ActiveWorkbook.SaveAs Filename:="Analysed experiments.xls"
End With

MyDir = "Users/alexanderjeans/Desktop/Individual Ca experiments"
strPath = MyDir & ":"

StrFile = Dir(strPath, MacID("XLS5"))

Do While Len(StrFile) > 0
If Right(StrFile, 3) = "xls" Then
ActiveWorkbook.Open
Sheets("Graphs").Select
Range("B2:B40").Select
Selection.Copy
Windows("Analysed experiments.xls").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Debug.Print StrFile
End If

StrFile = Dir
Loop
End Sub[/vba]