Having file name on the excel sheet automatically

A

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,
 
D

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
 
A

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
 
D

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

A-DESIGN

Okay,Thank you very much

Doug Glancy said:
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 said:
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
Click to expand...
Click to expand...
 
A

A-DESIGN

Is it possible to hide the extention of the file ?


A-DESIGN said:
Okay,Thank you very much

Doug Glancy said:
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 said:
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,
Click to expand...
Click to expand...
Click to expand...
 
D

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)
A-DESIGN said:
Is it possible to hide the extention of the file ?


A-DESIGN said:
Okay,Thank you very much

Doug Glancy said:
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,
Click to expand...
Click to expand...
Click to expand...
 
Top