Individual weblinks in a cell

[nmossvfp]

I am trying to populate an Excel column which contains a valid weblink to an invoice document. The link is a direct path to a billing pdf at a cloud location. It is assumed that any user who clicks the link will already have the necessary permissions to view the document or download it.

Excel seems to have some native protection against 'enabling' these links. However, clicking inside any individual cell or placing the cursor in to the cell and then clicking enter does 'activate' the link and it changes colors to the typical blue of a link. Then it works to retreive the document at that location.

My question, is there any way to create the column containing the Invoice pdf link so that all of them are always active? By active I mean that any user double-click on that specific cell will take the expected action?

[ggreen86]

Hello— Your question, “is there any way to create the column containing the Invoice pdf link so that all of them are always active? By active I mean that any user double-click on that specific cell will take the expected action?”, I do not understand what behavior you are looking for. Are you saying that when the link is clicked the first time, it is no longer available to be clicked on again (with the action to download)? Or, is it the colorization that Excel does when clicked – you want it ‘reset’ back to original appearance before the click? I am not sure what you are asking for… Greg From: nmossvfp ***@***.***> Sent: Tuesday, June 13, 2023 9:58 PM To: ggreen86/XLSX-Workbook-Class ***@***.***> Cc: Subscribed ***@***.***> Subject: [ggreen86/XLSX-Workbook-Class] Individual weblinks in a cell (Issue #89) I am trying to populate an Excel column which contains a valid weblink to an invoice document. The link is a direct path to a billing pdf at a cloud location. It is assumed that any user who clicks the link will already have the necessary permissions to view the document or download it. Excel seems to have some native protection against 'enabling' these links. However, clicking inside any individual cell or placing the cursor in to the cell and then clicking enter does 'activate' the link and it changes colors to the typical blue of a link. Then it works to retreive the document at that location. My question, is there any way to create the column containing the Invoice pdf link so that all of them are always active? By active I mean that any user double-click on that specific cell will take the expected action? — Reply to this email directly, view it on GitHub<#89>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AGWB33K6EKTTXKQIZFPKWDTXLELEDANCNFSM6AAAAAAZFVBQLY>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.******@***.***>>

[nmossvfp]

Greg,

Thanks for the quick response. I am creating an Excel and populating the sheet values in a loop via SETCELLVALUE
For example:

.setcellvalue(lnwb, currsheet, currow, 19, m.docnum)
.setcellvalue(lnwb, currsheet, currow, 20, m.doclink)

Where docnum is an invoice and doclink is a formatted URL. Both are ‘Text’.

When the spreadsheet is opened, all of the links in that column look like the example in the top row. They are black and do not have the expected single or double-click action associated with a web link.

Manually inserting your cursor and editing any part of the URL or clicking ENTER at the end of the line 'wakes it up' for lack of a better description and it becomes a clickable web link. I manually did this in the lower 6 rows to demonstrate this for you.

Attached screen capture:

Is there an alternate method or a parameter I could use to indicate that the contents although TEXT is actually a LINK ?
That's my question. As I write out this explanation I am wondering now if the observed behavior is the result of utilizing the relay method of link versus a straight up document extension such as .doc or .pdf or .htm ? Unfortunately I don't control that aspect.

Thanks

[ggreen86]

I think you should use the HYPERLINK() function and use method SetCellFormula() or try using the AddHyperLinkFile() method, instead of the SetCellValue() method. Greg From: nmossvfp ***@***.***> Sent: Wednesday, June 14, 2023 10:27 AM To: ggreen86/XLSX-Workbook-Class ***@***.***> Cc: ggreen86 ***@***.***>; Comment ***@***.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] Individual weblinks in a cell (Issue #89) Greg, Thanks for the quick response. I am creating an Excel and populating the sheet values in a loop via SETCELLVALUE For example: .setcellvalue(lnwb, currsheet, currow, 19, m.docnum) .setcellvalue(lnwb, currsheet, currow, 20, m.doclink) Where docnum is an invoice and doclink is a formatted URL. Both are ‘Text’. When the spreadsheet is opened, all of the links in that column look like the example in the top row. They are black and do not have the expected single or double-click action associated with a web link. Manually inserting your cursor and editing any part of the URL or clicking ENTER at the end of the line 'wakes it up' for lack of a better description and it becomes a clickable web link. I manually did this in the lower 6 rows to demonstrate this for you. Attached screen capture: [image]<https://user-images.githubusercontent.com/57226767/245823189-645c2c8c-0fa4-4366-873f-3d9b3a3a080e.png> Is there an alternate method or a parameter I could use to indicate that the contents although TEXT is actually a LINK ? That's my question. As I write out this explanation I am wondering now if the observed behavior is the result of utilizing the relay method of link versus a straight up document extension such as .doc or .pdf or .htm ? Unfortunately I don't control that aspect. Thanks — Reply to this email directly, view it on GitHub<#89 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AGWB33P77BEJTW7X3FEUMD3XLHC3FANCNFSM6AAAAAAZFVBQLY>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>

[nmossvfp]

.addHyperLinkFile(1, 2, 2, 20, 2, 20, “https://sample.weblink.validlink.com/232333”)
(note I am trying to populate a single cell in a loop so my start and end are often the same)

This would be perfect except that my attempts to use .AddHyperLinkFile() seem to be failing. It stops on the line IF ADIR(laFile, tcTarget) > 0 probably because the array is not being created from tcTarget.

Does the file you want to link to have to exist as a file? If Yes then the ADIR is looking to get fileinfo from the TcTarget but it’s not a file.

Would it be possible to create an .AddHyperlinkRaw() method that does not do the file check but just passes through the link with no validation?

[ggreen86]

Not sure if the AddHyperLinkFile method will work; will have to test it (can you do this for me?). The AddHyperLinkFile method saves an entry into the relationships table in order to create a .rels file for the sheet (this actually gives the location of the file). So, if the file is not on the drive, it may work; just not sure. Please edit the AddHyperLinkFile method and comment out lines 4, 11-13, in order to eliminate the check for the file existence. Please let me know the results so that I can update the method. ***@***.*** If this does not work, then the use of the HYPERLINK() function would be the answer. Greg From: nmossvfp ***@***.***> Sent: Wednesday, June 14, 2023 11:37 AM To: ggreen86/XLSX-Workbook-Class ***@***.***> Cc: ggreen86 ***@***.***>; Comment ***@***.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] Individual weblinks in a cell (Issue #89) .addHyperLinkFile(1, 2, 2, 20, 2, 20, “https://sample.weblink.validlink.com/232333”<https://sample.weblink.validlink.com/232333%E2%80%9D>) (note I am trying to populate a single cell in a loop so my start and end are often the same) This would be perfect except that my attempts to use .AddHyperLinkFile() seem to be failing. It stops on the line IF ADIR(laFile, tcTarget) > 0 probably because the array is not being created from tcTarget. Does the file you want to link to have to exist as a file? If Yes then the ADIR is looking to get fileinfo from the TcTarget but it’s not a file. Would it be possible to create an .AddHyperlinkRaw() method that does not do the file check but just passes through the link with no validation? — Reply to this email directly, view it on GitHub<#89 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AGWB33IQNSSRSGTHTBUDWYTXLHLBTANCNFSM6AAAAAAZFVBQLY>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>

[nmossvfp]

To test, I made my own addhyperlink method and commented out the lines you specified.

This line in the code points to an alias that does not exist named xl_sheet_rels

lnrelid = thisform.vfpxls.GetNextId(tnWB, tnSheet, "xl_sheet_rels")
obviously it fails right there.

[ggreen86]

The method GetNextId() is protected; you cannot call it outside of the class. It is designed for internal class use and not use by another developer. The value "xl_sheet_rels" is not an alias for a table; it is a value passed to a variable in the GetNextId() method for selecting the appropriate array that stores the Ids (see the DO CASE structure). Just comment out lines 4,11-13 in the method AddHyperLinkFile() for testing; after testing you can remove the comments from the lines. From: nmossvfp ***@***.***> Sent: Wednesday, June 14, 2023 1:45 PM To: ggreen86/XLSX-Workbook-Class ***@***.***> Cc: ggreen86 ***@***.***>; Comment ***@***.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] Individual weblinks in a cell (Issue #89) To test, I made my own addhyperlink method and commented out the lines you specified. This line in the code points to an alias that does not exist named xl_sheet_rels lnrelid = thisform.vfpxls.GetNextId(tnWB, tnSheet, "xl_sheet_rels") obviously it fails right there. — Reply to this email directly, view it on GitHub<#89 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AGWB33JHSAZCP5WYP23UG4LXLHZ75ANCNFSM6AAAAAAZFVBQLY>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>

[nmossvfp]

Ok I modified the lines of code in the class as you suggested to comment out the conditional lines of the addhyperlinkfile()

At first I thought it failed because my entire column of data was blank but then I realized, it wasn't blank after all the hyperlinks were all there underneath just not visible as any text so the columns contents looked blank.

I went back and added in the human readable text of the links themselves. Now it works as expected with the text of the weblink and a single click launches the link. Now that this is working, I am rethinking my process of writing out the web link itself as it is long and seems unecessary. I ran an alternate version of my spreadsheet with the same text in every row of "Click For Invoice" and will consider this for future runs.

Thank You!