Having file name on the excel sheet automatically

[A-DESIGN]

Hi,

I need to have the file name in the excel sheet,can anyone give me some
instruction or codes that can do this.

Thanks,

 

[Doug Glancy]

ThisWorkbook.Worksheets(1).Name = ThisWorkbook.Name

If you want to have it happen automatically on opening the workbook, you
could put it in the Workbook_Open event. Paste this code into the
ThisWorkbook module in the VBE:

Private Sub Workbook_Open()
ThisWorkbook.Worksheets(1).Name = ThisWorkbook.Name
End Sub

hth,

Doug Glancy

 

[Tom Ogilvy]

See the formulas at the bottom of Chip Pearson's page:

http://www.cpearson.com/excel/excelF.htm

these formulas require that the workbook have been saved at least once
before they will word - so putting them in the default Book1 would not work
(until it had been saved).

 

[Doug Glancy]

Woops, forgot to stop and read before I answered!

Doug

 

[A-DESIGN]

Thanks for all replies but I need the file name to be written in one cell
inside the sheet and not to be as a name for the worksheet, also I need it
to be updated automatically when I do save as or rename the file.

Thanks
Afshin

 

[Doug Glancy]

Afshin,

The formulas at Chip Pearon's page that Tom suggested will do just what you
want, e.g.,

To return the file name without the path, use
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

hth,

Doug Glancy

 

[A-DESIGN]

Okay,Thank you very much

Afshin,

The formulas at Chip Pearon's page that Tom suggested will do just what
you
want, e.g.,

To return the file name without the path, use
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

hth,

Doug Glancy

Thanks for all replies but I need the file name to be written in one cell
inside the sheet and not to be as a name for the worksheet, also I need
it
to be updated automatically when I do save as or rename the file.

Thanks
Afshin

 

[A-DESIGN]

Is it possible to hide the extention of the file ?

Okay,Thank you very much

Afshin,

The formulas at Chip Pearon's page that Tom suggested will do just what
you
want, e.g.,

To return the file name without the path, use
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

hth,

Doug Glancy

Thanks for all replies but I need the file name to be written in one
cell
inside the sheet and not to be as a name for the worksheet, also I need
it
to be updated automatically when I do save as or rename the file.

Thanks
Afshin



See the formulas at the bottom of Chip Pearson's page:

http://www.cpearson.com/excel/excelF.htm

these formulas require that the workbook have been saved at least once
before they will word - so putting them in the default Book1 would
not
work
(until it had been saved).

--
Regards,
Tom Ogilvy


Hi,

I need to have the file name in the excel sheet,can anyone give me
some
instruction or codes that can do this.

Thanks,

 

[Doug Glancy]

Afshin,

I think this will do it:

hth,

Doug Glancy

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND(".",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Is it possible to hide the extention of the file ?

Okay,Thank you very much

Afshin,

The formulas at Chip Pearon's page that Tom suggested will do just what
you
want, e.g.,

To return the file name without the path, use
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

hth,

Doug Glancy

Thanks for all replies but I need the file name to be written in one
cell
inside the sheet and not to be as a name for the worksheet, also I need
it
to be updated automatically when I do save as or rename the file.

Thanks
Afshin



See the formulas at the bottom of Chip Pearson's page:

http://www.cpearson.com/excel/excelF.htm

these formulas require that the workbook have been saved at least once
before they will word - so putting them in the default Book1 would
not
work
(until it had been saved).

--
Regards,
Tom Ogilvy


Hi,

I need to have the file name in the excel sheet,can anyone give me
some
instruction or codes that can do this.

Thanks,