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!