Function to test hyperlink (excel)
Solved
anthony
-
anthony -
anthony -
Hello,
I implemented the HYPERLINK function with concatenation and it results in this
=HYPERLINK($T$4&F8&""\""&D8&C8&""_""&K8&TEXT(H8,"jjmmaa")&"".pdf",K8)
It's a database with document numbers (C8), revision indices (D8), supplier names (F8), a date of receipt of the acknowledgment (H8), and the AR response: Accepted, accepted with comments, or Refused (K8).
Here's what it looks like in one example:
C:\Users\thony\Desktop\SOCIETEBIDULE\A8230835_A140610.pdf
So, in my column L, I immediately have the AR as hyperlinks without having to point them.
Now here is my question. Is there a function or formula to integrate to show whether the file is actually there or not?
Right now, I can only tell if the file is present by clicking on each line in column L, but I need to know without clicking.
Thank you!
Configuration: Windows 7 / Firefox 3.6.12
I implemented the HYPERLINK function with concatenation and it results in this
=HYPERLINK($T$4&F8&""\""&D8&C8&""_""&K8&TEXT(H8,"jjmmaa")&"".pdf",K8)
It's a database with document numbers (C8), revision indices (D8), supplier names (F8), a date of receipt of the acknowledgment (H8), and the AR response: Accepted, accepted with comments, or Refused (K8).
Here's what it looks like in one example:
C:\Users\thony\Desktop\SOCIETEBIDULE\A8230835_A140610.pdf
So, in my column L, I immediately have the AR as hyperlinks without having to point them.
Now here is my question. Is there a function or formula to integrate to show whether the file is actually there or not?
Right now, I can only tell if the file is present by clicking on each line in column L, but I need to know without clicking.
Thank you!
Configuration: Windows 7 / Firefox 3.6.12
8 answers
Good evening
I had a somewhat similar problem, I solved it with a macro that reconfigures the hyperlinks.
If the file is present, the hyperlink text turns blue and underlined. Otherwise, there’s an error and the text remains black.
I don’t know if this is really what you were looking for.
Best regards
agi67
I had a somewhat similar problem, I solved it with a macro that reconfigures the hyperlinks.
If the file is present, the hyperlink text turns blue and underlined. Otherwise, there’s an error and the text remains black.
I don’t know if this is really what you were looking for.
Best regards
agi67
Now it would be better to have messages like "file found" when the hyperlink is present and "file missing" when the destination file is not there.
Because I want to develop the hyperlink function for something else.
We receive delivery lists of products with a reference, a supplier, and a batch number. On the other hand, we receive reports for each product.
The person takes the delivery list and checks in the folder if they have a report for this product. If they have it, they move to the next line; if it is missing, they claim it. In short, there are 4 A4 pages and it takes time and is of little interest to them.
My goal is to have the delivery list in Excel, and with the hyperlink function, they will search for the report for each line.
So, it needs to be clear at a glance where the missing reports are without having to click.
Because I want to develop the hyperlink function for something else.
We receive delivery lists of products with a reference, a supplier, and a batch number. On the other hand, we receive reports for each product.
The person takes the delivery list and checks in the folder if they have a report for this product. If they have it, they move to the next line; if it is missing, they claim it. In short, there are 4 A4 pages and it takes time and is of little interest to them.
My goal is to have the delivery list in Excel, and with the hyperlink function, they will search for the report for each line.
So, it needs to be clear at a glance where the missing reports are without having to click.
Hello everyone,
Agi67 was addressing the issue, right? The display style is just the form...
Otherwise, you can create a custom function:
returns TRUE if the file exists, which you can test to display whatever you want.
eric
Agi67 was addressing the issue, right? The display style is just the form...
Otherwise, you can create a custom function:
Function FileExists(filename As String) As Boolean FileExists = (Dir(filename) <> "") End Function
returns TRUE if the file exists, which you can test to display whatever you want.
eric
Yes, actually Agi67 had the solution. I was thinking about filtering on the column with absent or present to highlight the missing ones.
Stupid question for the pros, but what do I do with the function?
At what level should I modify it, and since there's no SUB, it's not a macro, so where do I paste it?
Stupid question for the pros, but what do I do with the function?
At what level should I modify it, and since there's no SUB, it's not a macro, so where do I paste it?
Alt+F11 to display the VBE editor
right-click on the project (on the left) and 'insert / module'
paste the code
A custom function is used like a native Excel function.
On your sheet with a file name (including its path) in A1, in B1 you enter:
=FileExists(A1)
to get TRUE or FALSE or:
=if(FileExists(A1); "ok"; "absent")
eric
eric
right-click on the project (on the left) and 'insert / module'
paste the code
A custom function is used like a native Excel function.
On your sheet with a file name (including its path) in A1, in B1 you enter:
=FileExists(A1)
to get TRUE or FALSE or:
=if(FileExists(A1); "ok"; "absent")
eric
eric
Hello
The function doesn't update automatically... is there a way to fix it?
Yes, you just need to add this line to the function:
You can also use Éric's function, which I salute, in a conditional formatting, avoiding the need for a column with a formula like this:
--
Always zen
The function doesn't update automatically... is there a way to fix it?
Yes, you just need to add this line to the function:
Application.volatile
You can also use Éric's function, which I salute, in a conditional formatting, avoiding the need for a column with a formula like this:
=NOT(FileExists(A1))
--
Always zen
Indeed, the idea of conditional formatting is a good one to save a column since my pivot table no longer works with this additional column.
I added the row. I went and modified a letter in my file in the directory but nothing happened in Excel; I still see "ok," but when I click on it, I obviously have no file.
Is there a refresh time with this row?
I added the row. I went and modified a letter in my file in the directory but nothing happened in Excel; I still see "ok," but when I click on it, I obviously have no file.
Is there a refresh time with this row?
Function FileExists(filename As String) As Boolean
Application.Volatile
FileExists = (Dir(filename) <> "")
End Function
Here’s what I wrote, and the difference is that the result changes as soon as I press F9 if I’ve modified the hyperlink file name, whereas it doesn’t change if I remove Application.Volatile.
Application.Volatile
FileExists = (Dir(filename) <> "")
End Function
Here’s what I wrote, and the difference is that the result changes as soon as I press F9 if I’ve modified the hyperlink file name, whereas it doesn’t change if I remove Application.Volatile.
Well, actually I used conditional formatting and it solves my problem since the response from the formatting is immediate in relation to the column containing the formula.
I set the cell to red for the condition =NOT(ExistsFile(A1))
and as soon as I change something in my file it turns red, so great.
I'll now look into creating a filter based on color instead of content like I wanted to do.
Thank you.
I set the cell to red for the condition =NOT(ExistsFile(A1))
and as soon as I change something in my file it turns red, so great.
I'll now look into creating a filter based on color instead of content like I wanted to do.
Thank you.