Consulting

Results 1 to 4 of 4

Thread: Problem with looping

  1. #1
    VBAX Newbie
    Joined
    Jan 2013
    Location
    Oxford, U.K.
    Posts
    2
    Location

    Problem with looping

    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]
    Last edited by mikerickson; 01-18-2013 at 09:00 PM.

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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

    [VBA] code goes here [/VBA]

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

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

  3. #3
    VBAX Newbie
    Joined
    Jan 2013
    Location
    Oxford, U.K.
    Posts
    2
    Location
    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

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    I notice that MyDir uses / as a path separator rather than :

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •