Cell A displays: general20130903.txt
Cell A formula: =CONCATENATE("general",YEAR(TODAY()),TEXT(MONTH(TODAY()),"00"),TEXT(DAY(TODAY()),"00"),".txt")

Cell B is pure text of value: \\asimple\uncpath

Cell C formula: =ChkFile(CONCATENATE(B43,"\",A43))

ChkFile is a very simple macro.

This set of affairs works fine for cases of Cell A being plain text but this calculated value results in Cell C showing #VALUE!.

Can anyone help please and if possible without using VBA?

Edit: I didn't originally want to supply the macro code as I didn't feel it was relevant but also .. I'm no VBA programmer so not sure if I'm breaking any golden standards even in a couple of lines!

Public Function ChkFile(Name As String)

    ChkFile = FileDateTime(Name)

End Function
  • It seems that the problem has to do with the very simple macro... As such, I don't think there's a solution not involving VBA. – Jerry Sep 3 '13 at 9:31
  • I did say if possible. Not sure why you think the macro is at fault when it works fine for scenarios of Cell A being plain text. If it is to do with the macro then surely it's just a matter of the input being sanitised somehow? – Paul Sep 3 '13 at 9:49
  • It seems so, because I can't see anything which could cause it :( I'm not VBA savvy either though, but it seems there's yet another function to look at... – Jerry Sep 3 '13 at 11:06
  • Well turns out the input was at fault! – Paul Sep 3 '13 at 11:24
  • just adding -1 to DAY part won't work on 1st of the month (because it won't set the month back 1 too) - try using ="general"&TEXT(TODAY()-1,"yyyymmdd")&".txt" – barry houdini Sep 3 '13 at 15:45

You get a #Value because either the file doesn't exist or there was a problem retrieving the data. If you modify your function just a bit you will be able to return a message when the file couldn't be found.

Public Function ChkFile(Name As String) As String

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    ChkFile = IIf(fso.fileExists(Name), FileDateTime(Name), "File doesn't exist!")
    Set fso = Nothing

End Function
  • Ah cheesey creesey. It was human error not programming. The date being checked should be yesterday (a simple -1 near the DAY) and now it works fine. Thanks. Btw, the backslashes, it is 3 when typed in this text editor which shows as 2 to the viewer. I assume SE has this as an escape character. My excel sheet is correct, as displayed here. – Paul Sep 3 '13 at 11:19
  • @Paul im glad you have solved your problem. I recommend using the above function anyway as it does an extra check for the file presence. – user222864 Sep 3 '13 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.