200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi,
I want to hyperlink a command button to "C:\Users\dbuckley\Desktop\Machinery Register\(EXCEL_SHEET_NAME)\drawings"
The excel_sheet_name is a templete so the sheet and folder when created will have matching names.
So the name of the excel sheet and folders will always be changing.
=HYPERLINK("C:\Users\dbuckley\Desktop\Machinery Register\" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) & "\drawings","Link")This is the part that gets the sheet name:
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Per your new request, this is the macro that will do it:
Sub open_workbook()
Workbooks.Open ("C:\Users\dbuckley\Desktop\Machinery Register\" & ActiveSheet.Name & "\drawings")
End Sub
Attach it to a button by right-clicking the button and clicking Assign Macro...