How to get the modified date of a file into an excel cell

[karen1]

I have an excel addin that lets me get the modified date of a file, but it is not working now.
Can you help me debug it? It works perfectly for someone else in my company and failed for others. Thank you for any help you can offer.

Here is the code:

Function FileModDate(filespec)

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.GetFile(filespec)

FileModDate = f.DateLastModified

End Function

In the excel cell A2 I put c:\temp\Ben-Terry.pdf
In the excel cell B2 I put filemoddate(A1)
The error I get is: #VALUE!

 

[jasonb75]

I only get an error if the file doesn't exist or hasn't been saved.

Have you tried it with the function code in your workbook instead of using it as an add-in?

Are there any admin restrictions in place that prevent you from right clicking files and viewing the properties? If there are then that might mean also that the code doesn't have access to them.

 

[karen1]

I only get an error if the file doesn't exist or hasn't been saved.

Have you tried it with the function code in your workbook instead of using it as an add-in?

Are there any admin restrictions in place that prevent you from right clicking files and viewing the properties? If there are then that might mean also that the code doesn't have access to them.

Thank you for helping me. I can right click and see file properties. How would I bring the function code into the workbook?

 

[jasonb75]

Open the code editor by pressing Alt and f11, add a new module by going to Insert then Module in the code editor, and add the code into that module.

I'm not quite sure how that will work having the add-in and code in the workbook (not something I've tried), but if it does work then it shows that there is a problem with your pc accessing the add-in. Although this does seem unlikely, given that you are seeing #VALUE!, for an add in problem I would have expected #NAME? or a runtime error.

 

[karen1]

Open the code editor by pressing Alt and f11, add a new module by going to Insert then Module in the code editor, and add the code into that module.

I'm not quite sure how that will work having the add-in and code in the workbook (not something I've tried), but if it does work then it shows that there is a problem with your pc accessing the add-in. Although this does seem unlikely, given that you are seeing #VALUE!, for an add in problem I would have expected #NAME? or a runtime error.

Oh, I already did that.

 

[jasonb75]

With the text formula returning #VALUE! I can only assume that there is a difference in the permissions between the users / pc's where it works and where it doesn't.
The only people likely to be able to help with that would be your company I.T. dept.