How to extract file name for use in a cell?

[norm]

Hi, Using XL2002 and Windows 2000

If I have a file named A00015249CR.csv and I want to
place the first 11 characters of the filename in a cell, how
would I do that, if it's possible.

What I need to do is run several scripts on files that return simialr
data, so inserting the filename into an inserted column at the left
of each dataset would allow me to combine the data into one file
and still know where each row of data originated.

Thanks in advance!

Norm

 

[Guest]

Use the cell function

=cell("filename",A1) where A1 is any cell in the workbook associated with
the name yu are looking for.

 

[Guest]

if you are doing it with code

Set bk = Activeworkbook
s = left(bk.Name,Instr(1,bk.name,".",vbTextcompare)-1)

 

[Father Guido]

On Fri, 23 Feb 2007 11:52:10 -0800, Tom Ogilvy

~Set bk = Activeworkbook
~s = left(bk.Name,Instr(1,bk.name,".",vbTextcompare)-1)


Thanks Tom -- it works great!

Norm

 

[Father Guido]

On Fri, 23 Feb 2007 11:26:08 -0800, Joel

~Use the cell function
~
~=cell("filename",A1) where A1 is any cell in the workbook associated
with
~the name yu are looking for.

Thanks Joel, but that's not quite what I was looking for. Although I
guess I could write the filename within the file as a source. I wanted
to extract the filename (less extension) from the file, thereby making
my macro universal and requiring no advance setup. I.e. I can create
one file and then copy it changing the name but keeping all internal
information generic without having to modify each file.

Thanks again, I probably will be able to use your tip in another
situation.

Norm

~
~"(e-mail address removed)" wrote:
~
~> Hi, Using XL2002 and Windows 2000
~>
~> If I have a file named A00015249CR.csv and I want to
~> place the first 11 characters of the filename in a cell, how
~> would I do that, if it's possible.
~>
~> What I need to do is run several scripts on files that return
simialr
~> data, so inserting the filename into an inserted column at the left
~> of each dataset would allow me to combine the data into one file
~> and still know where each row of data originated.
~>
~> Thanks in advance!
~>
~> Norm
~>
~>