makeHyperlinkString for external files should have sheet, row and col as optional parameters

[Danfro]

When using the makeHyperlinkString function for an external file, the parameter sheet is a "must-have" parameter, not an optional one.

When passing the string to writeFormula, the hyperlink text=link does get the sheet name added to the filename like this:
=HYPERLINK("[G:/foldername/filename.ext]'Sheetname'!rowcol

I can not reproduce it now anymore, but on the first run like that, my excel file got corrupted. Excel error report said, the file could be corrected by removing a named area.

The hyperlink string should only pass the filename without sheet information.

[Danfro]

R v3.6.1
R-Studio v1.2.1335
openxlsm v4.1.4
Excel 2016

Forgot to add that here for reference.

[ycphs]

It would be really helpful if you could provide sample files. If the problem doesn't occur, then we could consider the issue as closed?

[Danfro]

Not reproducible is only the corrupted file issue because I only tried with one file so far. Maybe next week at work I can find some time to do more tests on that and provide a sample file. I can't test at home because I do not have Excel available.

But the sheetname and row/column information is added every time. At least I do not see the purpose for those "insheet-information" when having a link to an external file. Maybe I am just missing something? 🤔

[Danfro]

I am more or less running the following code:

file <- paste("C:/test.xlsx", sep= "")
wb <- loadWorkbook(file)
sheet <- getSheetNames(file)[1]
linkString <- makeHyperlinkString(sheet, col = 1, row = 4, text = "test.png", file = "G:/test.png")
writeFormula(wb,blatt,x = linkString, startCol = 1, startRow = 1)
saveWorkbook(wb,file, overwrite = TRUE)

Here is the resulting file with a hyperlink in it's variations:
test.xlsx

As you can see, the formula created by makehyperlink does look like this:

=HYPERLINK("[C:/test.png]'Tabelle1'!A4"; "test.png")

When using the build in Excel =HYPERLINK() command it does look like this:

=HYPERLINK("C:/test.png";"test.png")

There are clearly some parts added that do not belong there.

And sheet is not an optional parameter. If I run the makehyperlink command without the sheet parameter, I do get the following error:

Error in sprintf("[%s]'%s'!%s", file, sheet, cell) : 
argument "sheet" is missing, with no default

[github-actions]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.