Filename Function

[gdesreu]

Does anyone have a function to grab the filename and extension without the filepath? I found this formula but I think a function would be a lot easier

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

Thanks

 

[Momentman]

There is not a single function that can do that, at least for now.

Do you mean a User Defined Function (UDF)

maybe

Function Wkbname() As String
    Wkbname = ThisWorkbook.Name
End Function

 

[gdesreu]

There is not a single function that can do that, at least for now.

Do you mean a User Defined Function (UDF)

maybe

Function Wkbname() As String
    Wkbname = ThisWorkbook.Name
End Function

Yes I meant UDF. How would you call the function? wkbname() returns the module name and not the actual name of the workbook. - Thanks

 

[Momentman]

I. Open VBA (using ALT+F11
2. Insert a Module (AlT+I+M), ensure you are inserting in the appropriate workbook, you can look at the project explorer on the left in the VBE to be certain
3. Paste the lines of code in there

You should then be able to use the function like =Wkbname() in the worksheet

 

[gdesreu]

I. Open VBA (using ALT+F11
2. Insert a Module (AlT+I+M), ensure you are inserting in the appropriate workbook, you can look at the project explorer on the left in the VBE to be certain
3. Paste the lines of code in there

You should then be able to use the function like =Wkbname() in the worksheet

OK my bad, I forgot to add that I process a lot of files daily and I was hoping to add this to an xlam file so that I can use it on any excel file I am in. - Thanks, sorry I didnt mention that.

 

[Momentman]

If the macro is saved your PersonalMacroWorkbook, it should be available to all workbooks.

You can Record a samole macro and choose to store in the "personal Macro Workbook" thereby creating the Personal macro Workbook, after which you can proceed to dump the code in there(you should see the workbook in the VBE in the Project Explorer)

That should work

 

[gdesreu]

If the macro is saved your PersonalMacroWorkbook, it should be available to all workbooks.

You can Record a samole macro and choose to store in the "personal Macro Workbook" thereby creating the Personal macro Workbook, after which you can proceed to dump the code in there(you should see the workbook in the VBE in the Project Explorer)

That should work

ok thanks I will give that a try!

 

[gdesreu]

If the macro is saved your PersonalMacroWorkbook, it should be available to all workbooks.

You can Record a samole macro and choose to store in the "personal Macro Workbook" thereby creating the Personal macro Workbook, after which you can proceed to dump the code in there(you should see the workbook in the VBE in the Project Explorer)

That should work

Thanks for your help. I was able to get what I wanted by using this UDF in the xlam file. I changed it from "thisworkbook" to "activeworkbook"

Function Getname() As String
    Getname = ActiveWorkbook.Name
End Function

 

[Momentman]

Thanks for your help. I was able to get what I wanted by using this UDF in the xlam file. I changed it from "thisworkbook" to "activeworkbook"

Function Getname() As String
    Getname = ActiveWorkbook.Name
End Function

Many ways to skin a cat...

If you use Thisworkbook, it would refer to .xlam as opposed to the actual workbook, so that switch was necessary. Well done

 

[gdesreu]

Many ways to skin a cat...

If you use Thisworkbook, it would refer to .xlam as opposed to the actual workbook, so that switch was necessary. Well done

Thanks again, you gave me the structure I needed to see how it was done. This should save me quite a bit of time. Have a nice day.