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)

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)