How do I show the file name in a cell?

Posted by Melody on May 29, 2001 6:18 AM

I have a worksheet which, once calculated, is sent via macro to update a separate worksheet. Data in the files are entered in new file #s each time (ie, Data1.xls, Data2.xls, etc.), and I am now up to Data30.xls. I would like to have a new cell in the "sent information" which shows the fiel in which the original data can be found (ie, the last cell of the information sent is "Data30.xls"). How do I do this?

Thanks in advance!!!

Posted by Barrie Davidson on May 29, 2001 6:41 AM

If you are looking to do this via a formula, the formula would be:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

If you want to do it via a macro, your syntax would be:
Range("A4").Formula = Application.ActiveWorkbook.Name

Where A4 is the cell you want to put the file name in.

Barrie

Posted by Melody on May 29, 2001 6:48 AM

Thanks!!!!!!!