I want to add a dynamic hyper link to a column (cell) so that I can open the folder after I run the macro. There are two worksheets in my workbook: Main, Sheet1. In cell B5 on Main, I have a root folder specified. I need to build a hyper link to a cell.
Here is the program i have. I got 1004 error at the formula assignment.
Public Sub TestHyperLink()
Dim myWS As Worksheet
Set myWS = ThisWorkbook.Sheets("Sheet1")
Dim strFolder As String
Dim strTemp As String
strFolder = "20230215Test" 'This is a folder under the root folder path specified on "Main" worksheet.
'Build the formula based on strFolder by combining it with the root path specified in cell: Main!R5C2
strTemp = "=HYPERLINK(Main!R5C2" & Chr(32) & Chr(38) & Chr(32) & Chr(34) & Chr(34) & "\" & strFolder & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "Open Folder" & Chr(34) & Chr(34) & ")"
myWS.Cells(24, 2).FormulaR1C1 = strTemp 'I got 1004 error here.
End Sub
Now I debug above and copied the variable value from strTemp, and assign the static string value directly: strTemp = "=HYPERLINK(Main!R5C2 & ""\20230215Test"",""Open Folder"")". Then I got the result I needed without error. I could not figure out why.
Public Sub TestHyperLink()
Dim myWS As Worksheet
Set myWS = ThisWorkbook.Sheets("Sheet1")
Dim strFolder As String
Dim strTemp As String
strFolder = "20230215Test" 'This is a folder under the root folder path specified on "Main" worksheet.
'strTemp = "=HYPERLINK(Main!R5C2" & Chr(32) & Chr(38) & Chr(32) & Chr(34) & Chr(34) & "\" & strFolder & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & "Open Folder" & Chr(34) & Chr(34) & ")"
strTemp = "=HYPERLINK(Main!R5C2 & ""\20230215Test"",""Open Folder"")"
myWS.Cells(24, 2).FormulaR1C1 = strTemp
End Sub
