Does anyone know of a simple way to insert the file name (less extension) as
data in a particular cell?
I have data that is exported froma test grading program as comma separated
data. I open it in Excel to format so it can be imported into another
program. The test scoring program exports the student ID and a test score.
The idea is to name the exported file as "Quiz 1.txt" then use the macro to
insert "Quiz 1" as the "B:1" cell of the score column.

THANKS!!!

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-5)

--
HTH

Bob Phillips

"Jim at SDSU" <Jim at wrote in message
...
Does anyone know of a simple way to insert the file name (less extension)
as
data in a particular cell?
I have data that is exported froma test grading program as comma separated
data. I open it in Excel to format so it can be imported into another
program. The test scoring program exports the student ID and a test score.
The idea is to name the exported file as "Quiz 1.txt" then use the macro
to
insert "Quiz 1" as the "B:1" cell of the score column.

THANKS!!!

Look at the bottom of this page for formulas.

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

--
Regards,
Tom Ogilvy

"Jim at SDSU" <Jim at wrote in message
...
Does anyone know of a simple way to insert the file name (less extension)
as
data in a particular cell?
I have data that is exported froma test grading program as comma separated
data. I open it in Excel to format so it can be imported into another
program. The test scoring program exports the student ID and a test score.
The idea is to name the exported file as "Quiz 1.txt" then use the macro
to
insert "Quiz 1" as the "B:1" cell of the score column.

THANKS!!!

THANKS, but I forgot to mention, I want this to be part of a macro that does
a lot of other stuff at the same time (e.g. reformats the Student ID, inserts
the header row, inserts the Student ID title etc.)

"Tom Ogilvy" wrote:

Look at the bottom of this page for formulas.

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

--
Regards,
Tom Ogilvy

"Jim at SDSU" <Jim at wrote in message
...
Does anyone know of a simple way to insert the file name (less extension)
as
data in a particular cell?
I have data that is exported froma test grading program as comma separated
data. I open it in Excel to format so it can be imported into another
program. The test scoring program exports the student ID and a test score.
The idea is to name the exported file as "Quiz 1.txt" then use the macro
to
insert "Quiz 1" as the "B:1" cell of the score column.

THANKS!!!

Range("B1").Value = left(activeworkbook.Name,len(ActiveWorkbook.name)-4)

--
Regards,
Tom Ogilvy

"Jim at SDSU" wrote in message
...
THANKS, but I forgot to mention, I want this to be part of a macro that
does
a lot of other stuff at the same time (e.g. reformats the Student ID,
inserts
the header row, inserts the Student ID title etc.)

"Tom Ogilvy" wrote:

Look at the bottom of this page for formulas.

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

--
Regards,
Tom Ogilvy

"Jim at SDSU" <Jim at wrote in message
...
Does anyone know of a simple way to insert the file name (less
extension)
as
data in a particular cell?
I have data that is exported froma test grading program as comma
separated
data. I open it in Excel to format so it can be imported into another
program. The test scoring program exports the student ID and a test
score.
The idea is to name the exported file as "Quiz 1.txt" then use the
macro
to
insert "Quiz 1" as the "B:1" cell of the score column.

THANKS!!!

That's "almost" it. Works if I save as an XLS first, but never gives a value
if it remains a text file. "cell (filename) " would work except it is the
full path instead of just the file name. I was hoping there was a VB function
that just says "insert the filename without the extension as text here"
I do appreciate your time though.


"Tom Ogilvy" wrote:

Range("B1").Value = left(activeworkbook.Name,len(ActiveWorkbook.name)-4)

Shouldn't make any difference how you save it.

Demo'd from the Immediate window on a workbook in excel saved as text.

? activeworkbook.Name
export.txt

? left(activeworkbook.Name,len(ActiveWorkbook.name)-4)
export

Perhaps you need to be more specific about how it doesn't work.

--
regards,
Tom Ogilvy


"Jim at SDSU" wrote in message
...
That's "almost" it. Works if I save as an XLS first, but never gives a
value
if it remains a text file. "cell (filename) " would work except it is the
full path instead of just the file name. I was hoping there was a VB
function
that just says "insert the filename without the extension as text here"
I do appreciate your time though.


"Tom Ogilvy" wrote:

Range("B1").Value = left(activeworkbook.Name,len(ActiveWorkbook.name)-4)

"Tom Ogilvy" wrote:




Perhaps you need to be more specific about how it doesn't work.

Tom,
Thanks for your time.

When I create a blank workbook and name it "test" and insert the function,
the proceedure works as expected.

However, if I take a comma seperated text file called "Exam 1.txt and insert
a row at the top, put the title "Student ID" in cell A1 and insert the
function in B1 it returns the #Name error as reported in the documentation of
thet function. The error is only supposed to be there for an unsaved file and
is supposed to correct itself when the file is saved. However when I save the
file either as a "CSV" file (the format I need to import into the other
program) or as an XLS file, the error remains. The "cell (filename) "
functions works fine EXCEPT, it includes the full path name of the file which
I don't want. I'm simply trying to eliminate the need for my users typing the
title of the second column by exporting the file from the test scoring
program with the file titled with the same name as should appear at the top
of the "B" column.

Well, it was my own "DUH!!"
I was trying to insert this directly into the macro. When I declaired a
function, put this into the function, and inserted the *function* into the
macro IT WORKED!!!
THANK YOU, THANK YOU, THANK YOU!!!

"Tom Ogilvy" wrote:

Range("B1").Value = left(activeworkbook.Name,len(ActiveWorkbook.name)-4)