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?

 

[Cbrine]

How about this?

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

HTH
Cal

 

[JMH022]

Thank you for your reply!

Some more information that might be helpful:
The file being opened is from a web-based program that opens a csv-formatted file. The file name varies depending on which report is run from the web-application. Once it is opened (in csv), I want the user to hit one button to save the file as a .xls file and then do some more processing that I don't need to go into here. I tried using Save As...won't help because the csv filename is not retained in the "Save As" box.

This is why I need the code to recognize the abbreviated filename so the file can be saved as the report name.

Right now I am using an input box for the user to enter the filename and using their input as the variable in the code, but after processing a few, this is cumbersome to the user.

Pretty complicated I know, but I'll appreciate any help!

 

[Cbrine]

So you are doing this in code?

Then use this, to save it as Excel

ActiveWorkbook.SaveAs Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1), xlWorkbookNormal

HTH
Cal

 

[JMH022]

Cal,

Thanks for the input, but it didn't work.

I think because the csv opens from the web-based report system, it does not recognize Excel as the parent application. Even some simple code like Application.DisplayAlerts = False wont work.

Is there a way to get the code to set Excel as the parent application so it recognizes the code?

 

[Cbrine]

Do you have the code that the web based app is using? You should be able to set a reference to the Excel application. I've never worked on excel from a web app, but many times using access to work with excel.
Something like this might work.

Dim XlApp as Excel.application
set xlapp = createobject("Excel.applicaiton")
xlapp.visible = true
xlapp.activeworkbook.saveas Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1), xlWorkbookNormal

HTH
Cal

 

[JMH022]

unfortunately, no. I don't have access to the web-based code.

is this code something that would have to be in the web-based application or can I load it under Option Explicit in mine?

 

[Cbrine]

No, It would end up opening a new instance of excel with nothing in it. I'm afraid this is outside my realm of experience. Anyone else have an idea of what Jim can do to accomplish this?

 

[Cbrine]

If you put this in a cell what is returned to the workbook?

=cell("filename")
If this returns the csv's name, we might be able to use it in conjunction with some vba to extract the name of the csv file without the extension?

Let me know.

Cal

 

[JMH022]

That is the latest issue I am experiencing too...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.

I am losing my hair rapidly!