Hi all,
I have scoured through countless posts hoping to find a solution, but I have failed. The problem seems to be that concatenate adds "" around the table_array which messes it up.
My goal is to search a specified excel file for the yesterday's date. It will be in the 'E' column in the format of 'YYYY-MM-DD HH:MM:SS'.
From my excel file I have a column of server shares and file names that must be passed to a vba function to get find the specific file name (as they may have the seconds on them).
My example looks like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Server Path (Column A)[/TD]
[TD]File Name+WildCard (Column B)[/TD]
[/TR]
[TR]
[TD]\\appdata-eta\ttt\bbb_daily\dcd[/TD]
[TD]NCMDFJASD2017-12-11*.xlsx[/TD]
[/TR]
[TR]
[TD]\\appdata-eta\ttt\bbb_daily\dcd\Archive[/TD]
[TD]NASDASD2017-12-11*.xlsx[/TD]
[/TR]
</tbody>[/TABLE]
I have to call a macro getfilename(A2,B2) to have it find the specific file name.
I tried to put this in the VLookup and also putting it into a cell I reference later...neither worked
so I have concatenate("'",A2,"[",getfilename(a2,b2),"]","Report 1'!$E:$E") to make my table_array
The rest is VLOOKUP(TEXT(TODAY()-1,"yyyy-mm-dd")&"*",table_array,1,FALSE))
I am losing hair from all the head scratching trying to figure this one out, so hopefully someone can help!!
Thank you in advance!
I have scoured through countless posts hoping to find a solution, but I have failed. The problem seems to be that concatenate adds "" around the table_array which messes it up.
My goal is to search a specified excel file for the yesterday's date. It will be in the 'E' column in the format of 'YYYY-MM-DD HH:MM:SS'.
From my excel file I have a column of server shares and file names that must be passed to a vba function to get find the specific file name (as they may have the seconds on them).
My example looks like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Server Path (Column A)[/TD]
[TD]File Name+WildCard (Column B)[/TD]
[/TR]
[TR]
[TD]\\appdata-eta\ttt\bbb_daily\dcd[/TD]
[TD]NCMDFJASD2017-12-11*.xlsx[/TD]
[/TR]
[TR]
[TD]\\appdata-eta\ttt\bbb_daily\dcd\Archive[/TD]
[TD]NASDASD2017-12-11*.xlsx[/TD]
[/TR]
</tbody>[/TABLE]
I have to call a macro getfilename(A2,B2) to have it find the specific file name.
I tried to put this in the VLookup and also putting it into a cell I reference later...neither worked
so I have concatenate("'",A2,"[",getfilename(a2,b2),"]","Report 1'!$E:$E") to make my table_array
The rest is VLOOKUP(TEXT(TODAY()-1,"yyyy-mm-dd")&"*",table_array,1,FALSE))
I am losing hair from all the head scratching trying to figure this one out, so hopefully someone can help!!
Thank you in advance!
