Returning an abbreviated filename

JMH022

Active Member
Joined
Mar 7, 2002
Messages
320
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?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
How about this?

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

HTH
Cal
 

JMH022

Active Member
Joined
Mar 7, 2002
Messages
320
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Active Member
Joined
Mar 7, 2002
Messages
320

ADVERTISEMENT

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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Active Member
Joined
Mar 7, 2002
Messages
320

ADVERTISEMENT

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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Active Member
Joined
Mar 7, 2002
Messages
320
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!
 

Forum statistics

Threads
1,137,341
Messages
5,680,922
Members
419,945
Latest member
Carrie Sellers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top