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