Inserting a filename into a cell
 

Hi
try one of the following formulas (note: the workbook has to be save
before). Just use the formulas as they are shown (don't replace
'filename' with anything else)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file
name",A1),1))


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
How can I obtain the name of a file as a string? For instance, in
workbook C:\Documents and Settings\Me\Folder\file1.xls, I would like
file1 to be the string.

Inserting a filename into a cell
 

Hi
use these formulas directly in your worksheet 8no VBA required).

For a VBA solution simply use something like

sub insert_name()
with activesheet
.range("A1").value=.name
end with
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
Hi,
When I run a program with the codes below I get an error that says
"Compile Error: Function or Sub not defined" that refers to the
function "CELL(".


"Frank Kabel" wrote:

Hi
try one of the following formulas (note: the workbook has to be save
before). Just use the formulas as they are shown (don't replace
'filename' with anything else)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file
name",A1),1))


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
How can I obtain the name of a file as a string? For instance, in
workbook C:\Documents and Settings\Me\Folder\file1.xls, I would
like
file1 to be the string.

Inserting a filename into a cell
 

Hi
msgbox activeworkbook.name

--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
I'm sorry, I think I phrased the question poorly. I have about 2000
spreadsheets each containing a single data table, and I am trying to
compile averages. For each workbook, I take some averages and copy
them into a separate workbook. I would like to label each average I
copy into the separate workbook with the name of the file they came
from. I am wondering if there is a function in VBA that obtains the
file name of the ActiveWorkbook as a string. Thanks!

"Frank Kabel" wrote:

Hi
use these formulas directly in your worksheet 8no VBA required).

For a VBA solution simply use something like

sub insert_name()
with activesheet
.range("A1").value=.name
end with
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
Hi,
When I run a program with the codes below I get an error that says
"Compile Error: Function or Sub not defined" that refers to the
function "CELL(".


"Frank Kabel" wrote:

Hi
try one of the following formulas (note: the workbook has to be
save before). Just use the formulas as they are shown (don't
replace 'filename' with anything else)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only


=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name


=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file
name",A1),1))


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
How can I obtain the name of a file as a string? For instance, in
workbook C:\Documents and Settings\Me\Folder\file1.xls, I would
like
file1 to be the string.

Inserting a filename into a cell
 

That displays the string, but how can I obtain that string value as a string variable?

"Frank Kabel" wrote:

Hi
msgbox activeworkbook.name

--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
I'm sorry, I think I phrased the question poorly. I have about 2000
spreadsheets each containing a single data table, and I am trying to
compile averages. For each workbook, I take some averages and copy
them into a separate workbook. I would like to label each average I
copy into the separate workbook with the name of the file they came
from. I am wondering if there is a function in VBA that obtains the
file name of the ActiveWorkbook as a string. Thanks!

"Frank Kabel" wrote:

Hi
use these formulas directly in your worksheet 8no VBA required).

For a VBA solution simply use something like

sub insert_name()
with activesheet
.range("A1").value=.name
end with
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
Hi,
When I run a program with the codes below I get an error that says
"Compile Error: Function or Sub not defined" that refers to the
function "CELL(".


"Frank Kabel" wrote:

Hi
try one of the following formulas (note: the workbook has to be
save before). Just use the formulas as they are shown (don't
replace 'filename' with anything else)

File path and file name:
=CELL("filename",A1)

File path only
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only


=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name


=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("file
name",A1),1))


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
How can I obtain the name of a file as a string? For instance, in
workbook C:\Documents and Settings\Me\Folder\file1.xls, I would
like
file1 to be the string.

Inserting a filename into a cell
 

Try something like

Dim S As String
S = ActiveWorkbook.Name


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"davidoo2005" wrote in
message
...
That displays the string, but how can I obtain that string
value as a string variable?

"Frank Kabel" wrote:

Hi
msgbox activeworkbook.name

--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
I'm sorry, I think I phrased the question poorly. I have
about 2000
spreadsheets each containing a single data table, and I am
trying to
compile averages. For each workbook, I take some averages
and copy
them into a separate workbook. I would like to label each
average I
copy into the separate workbook with the name of the file
they came
from. I am wondering if there is a function in VBA that
obtains the
file name of the ActiveWorkbook as a string. Thanks!

"Frank Kabel" wrote:

Hi
use these formulas directly in your worksheet 8no VBA
required).

For a VBA solution simply use something like

sub insert_name()
with activesheet
.range("A1").value=.name
end with
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
Hi,
When I run a program with the codes below I get an error
that says
"Compile Error: Function or Sub not defined" that refers
to the
function "CELL(".


"Frank Kabel" wrote:

Hi
try one of the following formulas (note: the workbook
has to be
save before). Just use the formulas as they are shown
(don't
replace 'filename' with anything else)

File path and file name:
=CELL("filename",A1)

File path only

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

File name only



=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("
]",CEL
L("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name



=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL
("file
name",A1),1))


--
Regards
Frank Kabel
Frankfurt, Germany


davidoo2005 wrote:
How can I obtain the name of a file as a string? For
instance, in
workbook C:\Documents and Settings\Me\Folder\file1.xls,
I would
like
file1 to be the string.