PDA

View Full Version : Problem with looping



Ajeans
01-18-2013, 10:22 AM
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:


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

mikerickson
01-18-2013, 09:00 PM
Ajeans,

Welcome to VBA Express.

In the future, please wrap your code in code tags. It makes it much easier to read.
either type the code tags in manualy, like

code goes here

or select the code and press the VBA button above the editor window.

I'll do it for your previous post this time.

Ajeans
01-19-2013, 02:45 AM
Hi Mikerickson,

Thanks for this, and for adding the tags for me. I'll do it properly next time.

Until then, any thoughts on the code? ;)

Cheers

mikerickson
01-21-2013, 10:50 AM
I notice that MyDir uses / as a path separator rather than :