Consulting

Results 1 to 16 of 16

Thread: VBA - Macro to open a file based on part of text in cell

  1. #1
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location

    VBA - Macro to open a file based on part of text in cell

    I've been trying to adapt a Macro I've used to open files which were referenced in an Excel Spreadsheet. However, this time around the reference in the Spreadsheet is prefaced with "work_" followed directly by the filename e.g.: work_1234 I am pretty sure I need to change something in the part of the code that is in bold but I do not know how to adapt ActiveCell.Text to only use the digits as the information needed to search the directories.

    Dim directories(10) As String, fileName As String, i As Integer

    directories(0) = "Users/username/folder/subfolder/goal/"
    directories(1) = "Users/username/folder/subfolder/goal/1"
    directories(2) = "Users/username/folder/subfolder/goal/2"
    directories(3) = "Users/username/folder/subfolder/goal/3"
    directories(4) = "Users/username/folder/subfolder/goal/4"

    i = 0

    Do While i < 5
    If ActiveCell.Text() = "" Then
    Exit Do
    End If

    'look for the numbers in the active cell that will be used when searching the folders listed above'
    fileName = Dir(directories(i) & "*" & ActiveCell.Text() & "*", MacID("Macintosh HD"))
    'fileName = "Users/username/folder/subfolder/goal/1234.html"'
    If fileName <> "" Then
    CreateObject("Shell.Application").Open (fileName) 'directories(i) & fileName)
    fileName = Dir()
    Exit Do
    End If
    i = i + 1
    Loop

    End Sub

    I hope managed to convey what I am trying to do and would be really grateful for any insights you could give me to help me with this. I'm not super familiar with VBA and after scouring the interwebs for what feels like forever I thought I'd check here to find some help!
    Last edited by ChrisS; 04-08-2020 at 10:43 AM. Reason: layers in format

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Chris,

    It would help to know what data is in the cells you're referencing in the above code.

  3. #3
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location
    Hi Zack,
    Thanks for responding to my post!
    The data in the cell is string consisting of "work_" followed by digits. These digits make up the corresponding html filename.
    I hope this answers your question.
    With kind regards,

    Christina

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Are you saying you want to make the paths like...

    directories(0) = "Users/username/folder/subfolder/goal/work_"
    directories(1) = "Users/username/folder/subfolder/goal/work_1"
    directories(2) = "Users/username/folder/subfolder/goal/work_2"
    directories(3) = "Users/username/folder/subfolder/goal/work_3"
    directories(4) = "Users/username/folder/subfolder/goal/work_4"

  5. #5
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location
    Quote Originally Posted by Zack Barresse View Post
    Are you saying you want to make the paths like...

    directories(0) = "Users/username/folder/subfolder/goal/work_"
    directories(1) = "Users/username/folder/subfolder/goal/work_1"
    directories(2) = "Users/username/folder/subfolder/goal/work_2"
    directories(3) = "Users/username/folder/subfolder/goal/work_3"
    directories(4) = "Users/username/folder/subfolder/goal/work_4"
    Dear Zack,
    I've changed them to resemble the path I will be using..
    I did not think that the problem was located with my listing of the directories as that has pretty much stayed the same from the last time I've been able to use this macro.
    But yeah - any pointers you have for me I appreciate!!
    With kind regards,
    Christina

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Christina,

    Thank you for the clarification. It still isn't fully clear to me. Are the directories() all different? It would help if we could see the values in the cells. I'm assuming your values look like this...

    work_1.html
    work_2.html
    work_3.html
    ...
    The directories are a little confusing. Are they all in the same directory? Are these different directories? It looks like you're searching multiple directories for a file name based on the activecell value. Is that correct?

  7. #7
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location
    Dear Zack,
    I apologize for not having been clearer in my original post - I really tried, but I maybe this will clarify matters a bit.

    First, with regards to the directories: there will be four folders with html files (1, 2, 3, 4) in the folder 'goal'

    directories(0) = "Users/username/folder/subfolder/goal/"
    directories(1) = "Users/username/folder/subfolder/goal/1"
    directories(2) = "Users/username/folder/subfolder/goal/2"
    directories(3) = "Users/username/folder/subfolder/goal/3"
    directories(4) = "Users/username/folder/subfolder/goal/4"

    So yes, I will be searching multiple directories for a file name based on parts of the content of the activecell.

    As for the values in the cells. You can see them here in column B:
    Screen Shot 2020-04-10 at 10.27.28 AM.jpg

    As such,
    "work_1234" can be found in directories(1) = "Users/username/folder/subfolder/goal/1" as "1234.html
    "work_5678" can be found in directories(2) = "Users/username/folder/subfolder/goal/2" as 5678.html
    "work_91011" can be found in directories(3) = "Users/username/folder/subfolder/goal/3" as 91011.html
    and so on..
    It is not entirely clear which html files are found in which folder which is why I wanted to macro to search all listed directories.

    I hope I have not complicated matters further and thanks again for responding to my post and giving this some of your time! I really appreciate it!
    With kind regards,

    Christina

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Ah! That helps immensely. One last question. For a file, let's say the first one, "work_1234", please confirm these assumptions:
    • These are html files, and the full file name is "work_1234.html"
    • A path *could* be "Users/username/folder/subfolder/goal/1/work_1234.html"
    • A path *could* be "Users/username/folder/subfolder/goal/2/work_1234.html", etc.


    Is that correct?

  9. #9
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location
    Hi Zack,
    I'm glad - and for your question:
    The full file name for "work_1234" is "1234.html". It only uses the digits that are in the cell without "work_".
    I think this is the part I am having trouble with in VBA.
    And you are absolutely right,
    the path *could* be either "Users/username/folder/subfolder/goal/1/work_1234.html" or
    path *could* be "Users/username/folder/subfolder/goal/2/work_1234.html", etc.
    Again, I really appreciate you taking the time to consider my problem!
    With kind regards,

    Christina

  10. #10
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Ok. So which one could be a file between the two paths:

    1. "Users/username/folder/subfolder/goal/1/work_1234.html"
    2. "Users/username/folder/subfolder/goal/1/1234.html"

    With the 1 being variable and set by your array, of course.

  11. #11
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location
    Option 2: "Users/username/folder/subfolder/goal/1/1234.html"
    The file only consists of a combination of numbers followed by html.

    I am starting to get a bit confused though

  12. #12
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Without testing, perhaps try

        Dim directories(10) As String
        Dim fileName As String
        Dim i As Long
    
        directories(0) = "Users/username/folder/subfolder/goal/"
        directories(1) = "Users/username/folder/subfolder/goal/1/"
        directories(2) = "Users/username/folder/subfolder/goal/2/"
        directories(3) = "Users/username/folder/subfolder/goal/3/"
        directories(4) = "Users/username/folder/subfolder/goal/4/"
    
        i = 0
    
        Do While i < 5
            If ActiveCell.Value = "" Then
                Exit Do
            End If
    
            fileName = Dir(directories(i) & Replace(ActiveCell.Value, "work_", ""), MacID("Macintosh HD"))
            If fileName <> "" Then
                CreateObject("Shell.Application").Open (fileName)
                fileName = Dir()
                Exit Do
            End If
            i = i + 1
        Loop

  13. #13
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location
    Hi,
    Thanks for this - I've tried it to implement your suggestion, however, I always seem to get this response:
    "Run-time error '5': invalid procedure call or argument"
    When trying to debug the code this line is highlighted:
    fileName = Dir(directories(i) & Replace(ActiveCell.Value, "work_", ""), MacID("Macintosh HD"))
    Do you maybe have an idea what this could mean? From what I can tell I've defined all the variables there are and structured the arguments properly, but I might just be missing something due to inexperience or lack of knowledge.
    With kind regards,

    Christina

  14. #14
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Since this is on a Macintosh, I'm not entirely sure about any of it. I'm assuming you're on Office 365 and have the latest version, which has fixed the long-standing Dir() issues which plagued Mac Excel VBA. Also, as far as I'm aware, the MacID() function only takes a 4 digit string, so I'm not sure what "Macintosh HD" would do.

    I fitted the code below so it would work on my machine. You should be able to dump it to your machine and test as well. Mostly I was tired of changing everything to run another test. However, this works for me.

    Sub zTest()
    
        Dim Directories(10) As String
        Dim FileName As String
        Dim i As Long
    
        #If Mac Then
            Directories(0) = "Users/username/folder/subfolder/goal/"
            Directories(1) = "Users/username/folder/subfolder/goal/1/"
            Directories(2) = "Users/username/folder/subfolder/goal/2/"
            Directories(3) = "Users/username/folder/subfolder/goal/3/"
            Directories(4) = "Users/username/folder/subfolder/goal/4/"
        #Else
            Directories(0) = "C:\Users\Zack\Desktop\Test\"
            Directories(1) = "C:\Users\Zack\Desktop\Test1\"
            Directories(2) = "C:\Users\Zack\Desktop\Test2\"
            Directories(2) = "C:\Users\Zack\Desktop\Test3\"
            Directories(2) = "C:\Users\Zack\Desktop\Test4\"
        #End If
    
        i = 0
    
        Do While i < 5
        
            If ActiveCell.Value = "" Then
                Exit Do
            End If
    
            #If Mac Then
                FileName = Dir(Directories(i) & Replace(ActiveCell.Value, "work_", ""))
            #Else
                FileName = Dir(Directories(i) & Replace(ActiveCell.Value, "work_", ""), vbNormal)
            #End If
            
            If FileName <> "" Then
                CreateObject("Shell.Application").Open Directories(i) & FileName
                FileName = Dir()
                Exit Do
            End If
            i = i + 1
        Loop
    
    End Sub

  15. #15
    VBAX Regular
    Joined
    Apr 2020
    Posts
    8
    Location

    Thumbs up Thank you for your efforts!

    Hi Zack,
    Thank you once more for giving my issue so much thought!
    I really appreciate it. So far I've managed to change the macro with your suggestions and I no longer get error messages, but the html also does not open up when I enter the combination of keys.
    It's super strange, and I've literally tried everything at this point and so have you - so thanks once more and take care,

    Christina

  16. #16
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hmm, it worked for me when I tested. Have you tried stepping through the code to ensure it's assigning the right file paths? I wish I had a Mac to test this on but I do not.

Tags for this Thread

Posting Permissions

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