|
Output the Name of the Current Excel Workbook Including Extension - UDF
Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function has no arguments and is very easy to use. Simply enter the function into any spreadsheet and the function will return the full name and extension of that spreadsheet.
This is a great function to use when you need to keep tabs on what version of an Excel file you are working with as well as on any master worksheet within a spreadsheet.
Where to install the macro: Module
UDF to Output the Name of the Current Excel Workbook Including Extension
Function NAMEWORKBOOKEXT() As String
NAMEWORKBOOKEXT = ThisWorkbook.Name
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
I have a few files sent out to field users. These users, for some reason, really love to change my .xlsm excel files to .xlsx when they save. Is there any VBA code that will help with this? The closest I could come up with is using the BeforeSave workbook event, but this still allows the user to make any changes they want to extension once the Save As dialog box appears.
Example of what I want:
User clicks save as, types in a new file name and selects .xls
Macro assigns the previous file extension to the file then saves it.
Hello All,
The worksheet has a status column that includes "Current" for current staff, "Past" for people who no longer work with the company etc. There are several types of positions i.e. 1st Year, 2nd Year, 3rd Year etc. What I am trying to do is count the number of positions i.e. 1st Year which only meet Current status. If there were 20 people with 1st Year status of which only 14 were current then it should return the number 14. Each position has its own cell so nothing fancy needed there just the count of each current position. Hopefully this makes sense. Thanks in advance!
I'm using a VLOOKUP formula to return the current value (from same column as VLOOKUP formula, i.e. column I), based on the sum of the COUNTA of (textual) values in column D + the COUNTA of daily values in column E. The "current value" will be in the row of the prior business day. So, if, as of yesterday, there were 3 business days in the fiscal month (the 25th, 26th, and 27th), the VLOOKUP would look up the 3rd row's value in column I and return that value. If the 27th was a holiday, "Holiday" would be entered in column D, and the COUNTA of columns D:E would still be 3. If, however, the holiday occured on some day later in the month, say the 10th day, then for today, I would still need the VLOOKUP formula to return the 3rd (row's) value, not the 4th value (i.e. the count of the 3 business days + the day where "Holiday" is entered in column E later in the month).
The problem lies in the need to enter the text "Holiday" in column D at the beginning of the month (in the row when the holiday will occur) when I create the worksheet. This throws off (increases) the VLOOKUP result by 1, and therefore returns 0 because there won't be a value in the VLOOKUP column for the VLOOKUP formula to return.
Does this make enough sense without seeing the worksheet? The VLOOKUP formula used is:
=IF($B$79=$B$80,0,VLOOKUP(SUM($B$79+COUNTA(D11:D45)),$B$11:I45,COLUMN(I6)-1,FALSE))
Cell [B79] maintains a running count of the number of days of data in column E.
(I attempted to download Colo's HTML Make, but was unsuccessful, so can't attach the part of my worksheet in question.)
Hi,
I have a date stored in the following format: mmddyyyy in cell Y2. I am trying to reference that date in a formula I am using in cell X2 to build a directory path. When I reference the date in Y2 it displays in a different format.
For example: The date in cell Y2 is 06112011 but when I reference it in X2 it displays as 40705.
How do I change this so it pulls in 06112011 in my formula in X2?
I've attached a spreadsheet with the formula I am building.
Thanks in advance!
Hi everyone,
This one is keeping me busy: I need a formula to output the path in which the current workbook is saved in.
So for example:
C:\excel\test.xls
The formula output would need to be:
C:\excel
I don't want to use a macro for this if I can somehow avoid it. I tried with LEFT, RIGHT, MID functions but I need to factor in that the name of the file (in the example above test.xls) can change..
Any ideas?
Thanks a million!
Titus
I am enclosing a file as well.
I have this file. On sheet1 is some data.
In the VBA code i have two variables
row_strt_1
row_end_1
I use currentregion twice.
I understand the output of the row_strt_1.........output is 17
I dont understand the outup of the row_end_1.............output is 19.
Can someone explain the row_end_1 ........why is it 19?
L9 will equal activeworkbook name without the extension. MY attempt was this...
range("L9").value=activeworkbook.name
Is there a way in MS Excel 2007 to have excel open to the last saved woorkbook? i.e. from power failure or reboot on the computer?
Thanks.
Pat.
Sub Macro1()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "COOPtbl1", "C:\Documents and Settings\kanegr\My Documents\Book1.xls", True, ""
End Sub
Heres the code I keep getting a
"Run-time error '424':
Object required"
Message.
|
|
