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
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