Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
    
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
up vote 2 down vote accepted

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
share|improve this answer
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .