Returning an abbreviated filename

[JMH022]

Is it possible to return the MS-DOS name of a file to an excel worksheet?

I don't need the entire path, but just the name of the file (excluding ".xls" if possible).

For Example, if the path is
- C:\My Documents\TEST.xls
I only need TEST returned in a cell on the worksheet.

Any ideas?

 

[JMH022]

http://<websitehere>/res/294461376/294461376294457897191889/SalesRpt 2006-09-21 1318 rcout.csv

"SalesRpt" is what I need extracted from the file name so I can place it in a cell (B2). I do not know what all the other garbage means. The random numbers ahead of the report name seem to be just that--random. They change everytime the report is generated, as does the date and time shown after the report name.

Isn't programming fun?

 

[Cbrine]

Nice,
But using this in say Cell A1 and some vba, we should be able to extract that string from it. Got a meeting but after that I will give you some code.

Cal

 

[Cbrine]

Ok,
Try adding that formula to cell A1, and running this code.

Dim Filename As String
Filename = Mid(Range("A1"), InStrRev(Range("A1"), "/") + 1, Len(Range("A1")) - InStrRev(Range("A1"), "/") - 4)

HTH
Cal

 

[JMH022]

that worked like a charm! Thank You!

Now if I can figure out the other problem, I'll be set!

I open the csv from the web-based system, save it as .xls and ActiveWorkbook.Close to close it.

#1: it doesn't recognize the close command, so I manually click the [X] to close.
#2: In Task Manager, it still shows I have Excel running, even though it is not open.

 

[Cbrine]

JMH022,
Sorry for the delay in replying, I was in Dallas all last week, and wasn't able to do much here.

I think the problem you are running into now may be related to how the web designer code is actually made. You will not be able to resolve this. At least this is my guess.

http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

HTH
Cal

Kudos to Norie for making me aware of this issue, and Tusher for putting together a nice explanation.