Excel - can't concetanate a concetanated cell

1

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

Paul

Posted 2013-09-03T09:29:08.273

Reputation: 203

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 – 2013-09-03T09:31:38.197

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 – 2013-09-03T09:49:22.647

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 – 2013-09-03T11:06:38.840

Well turns out the input was at fault! – Paul – 2013-09-03T11:24:45.523

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 – 2013-09-03T15:45:35.487

Answers

2

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

user222864

Posted 2013-09-03T09:29:08.273

Reputation:

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 – 2013-09-03T11:19:11.503

@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. – None – 2013-09-03T11:21:29.367

Asked: 2013-09-03T09:29:08.273

Viewed: 62 times

Active: 2013-09-04T14:23:51.333