Translate CELL("filename") to VBA

I have managed parsing date (and convert to Excel date format) from the name of imported to Excel text file (path included). For the sake of this post is unnecessary complicate):

=DATE(LEFT(MID(CELL("filename"),FIND("2",CELL("filename")),FIND("_",CELL("filename"))-FIND("2",CELL("filename"))-12),4),MID(MID(CELL("filename"),FIND("2",CELL("filename")),FIND("_",CELL("filename"))-FIND("2",CELL("filename"))-12),5,2),RIGHT(MID(CELL("filename"),FIND("2",CELL("filename")),FIND("_",CELL("filename"))-FIND("2",CELL("filename"))-12),2))

How to move this function to VBA. I have tried formula: Range(""A1").Formula = "=...." and obviously it did not work. Argument of CELL in VBA can't have quotes. Result supposed to go to cell A1 of the ActiveWorksheet.

I would appreciate help/suggestions
Thank you
Tomasz

Thank you Richard for your willingness to help,
my answer will be wordy, clear indication that I am a novice.
1. Data is held in daily txt files in tab separated columns.
2. only file name carries the date when file was collected.
3. I want to "process" data from each of the *.txt files from some of the columns (to get min, max, graph etc.) and save the results in a spreadsheet, in dated rows. One row for each processed file, for each day.
4. I have managed to have all steps in excel - practically manual process:
- open txt file in excel
- extract date by =CELL("filename") and reformat to excel DATE format
- copy/paste to another spreadsheet - do "processing"
- move results - one row per one file - to another spreadsheet (with a help of one macro allowing to place results in a new row).

All works - with an inconvenience of few manual steps. Now I attempt to use macro(s) for the same.

Tomasz

Thank you for the suggestion - I tried in VBA double quotation and it works, similar to Range.Formula("A1") = "=CELL(""filename"")"
Cell A1 contains correctly parsed date from the file name. Now the question is how to automate the process (see my earlier response to Richard - below). Working on it.
Thank you
Tomasz

Originally Posted by tomaszs

2. only file name carries the date when file was collected.

When I said "If you could post what CELL("filename") is returning to you, that would help." in Message #3, my hope was that you would post more than that above. What I would like you to do is put this formula in a cell in your file...

=CELL("filename")

and post back here what you see in the cell. Then we can use that to see what your formula is actually doing and then give you the VBA equivalent (which is what you asked for).

I am sorry, my elaborate answer missed the point and did not satisfied any of you (Richard, Rick). My next posts will have more code and less words. At the moment double quote in VBA CELL(""filename"") solved immediate dilemma. txt file parsing works as well.

Thank you