Extracting the filename from application.getopenfilename

Hi,

I am trying to extract the filename of a file used to import data using the application.getfilename function.

it returns the full path which is perfect for importing my data, but now i need to reduce the C:\data\imports\blaablaa\UKDATA.csv into purly UKDATA.csv

I dont want to restrict the path where the csv files are stored otherwise i would simply use a basic right$ command and specifiy the length of the known path to seperate path from filename.

Is there any ideas how i can achieve this, i feel i kind of need a FIND command which starts right to left rather than left to right.

any help appretiated.

It looks like you are going to use the code in VBA but your question is in the worksheet functions therefore I've come up with the following

Please Login or Register  to view this content.

Hi,

There's some code here http://www.exceltip.com/st/File_name...Excel/445.html
that you could adapt...

That's a lot of code to simply do this:

Please Login or Register  to view this content.

Originally Posted by rwgrietveld

It looks like you are going to use the code in VBA but your question is in the worksheet functions therefore I've come up with the following

Please Login or Register  to view this content.

Thank you, I am using VB to import a csv using the application.getopenfilename, but you are right in that I dump the variable flle name imported from getopenfilename (complete with path) into a cell and now need to manipulate it to extract the name without path.

So A1 has "C:\Desktop\CSV\SCSE Current Status.csv" (could be any path len)

and I need to extract the name 'SCSE Current Status.csv' for use as an indicator of the file imported without the path to confuse people.

The closest i can get is to insist the CSV files are in a sub folder named \CSV\ and then to look for \CSV\ to begin my right$ such as

=RIGHT(A1,LEN(A1)-FIND("\CSV\",A1)-4)

But I would rather remove the dependence on a specific named CSV sub folder as I am not the intended end user and we know what happens when we go live with this kind of thing.

your kind suggestion of the following looks like its working from the filename of the current worksheet, rather than a cell text string, and it does not seem to work for me.
=MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+1,FIND("]",CELL("filename",$A$1))-FIND("[",CELL("filename",$A$1),1)-1)

can you or anyone advise further, thanks in advance.

Originally Posted by pjoaquin

That's a lot of code to simply do this:

Please Login or Register  to view this content.

WOW, Thanks my friend, I think we have a winner. this seems to do the trick in a way i didnt imagine possible, hence i was staying clear of vb. was pleased to get a vb import, and didnt want to risk further trips into unknown.

Thanks a million.

Thanks all that read and assisted too. What a forum, what a community.

You are all stars.

Glad I could help, Tony.

One other formula option, assuming your full path is in A1, would be:

=MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255)

Whether you use VBA or formulas is up to you, but they're both certainly possible.

Cheers!

Originally Posted by pjoaquin

Glad I could help, Tony.

One other formula option, assuming your full path is in A1, would be:

=MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255)

Whether you use VBA or formulas is up to you, but they're both certainly possible.

Cheers!

That is a **** formula, I may print it and look at it from time to time for inspiration for how incredible excel is in the hands of an expert.

Thanks again, and keep up the great work.