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

[ChrisS]

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!

[Zack Barresse]

Chris,

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

[ChrisS]

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

[Zack Barresse]

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"

[ChrisS]

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

[Zack Barresse]

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?

[ChrisS]

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

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

[Zack Barresse]

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?

[ChrisS]

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

[Zack Barresse]

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.

[ChrisS]

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

[Zack Barresse]

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

[ChrisS]

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

[Zack Barresse]

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

[ChrisS]

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

[Zack Barresse]

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.