Get Active Workbook or Worksheet Name Path FullName in Excel VBA

Home/Excel VBA/Get Active Workbook or Worksheet Name Path FullName in Excel VBA

Description:

When we are working with workbooks and worksheets, some times we may required to Get Active Workbook or Worksheet Name, Path of the workbook to know the directory, FullName(Complete path) of the workbook to know the location of the workbook, selected Range address in active sheet or selected Cell address in active sheet using Excel VBA.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Solution(s):

You can get Active Workbook Or Worksheet Name by using Name property of the workbook or worksheet.

Get Active Workbook or Worksheet Name – Example Cases:

Get an Active Workbook Name

You can use ActiveWorkbook property to return the active workbook name. You can use the following code to get the name of the Active Workbook

Code:
Sub DisplayWorkbookName()
MsgBox ActiveWorkbook.Name, vbInformation, "Workbook Name"
End Sub
Output:

Get Active Workbook-Worksheet Name

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see output as shown above
  7. Save the file as macro enabled workbook
Get an Active Workbook Path

You can use ActiveWorkbook property to return the active workbook Path.You can use the following code to Get Active Workbook Path to know the workbook directory.

Code:
Sub DisplayWorkbookPath()
MsgBox ActiveWorkbook.Path, vbInformation, "Workbook Path"
End Sub
Output:

Get Active Workbook Path

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see output as shown above
  7. Save the file as macro enabled workbook
Get an Active Workbook FullName

You can use ActiveWorkbook property to return the active workbook FullName. You can use the following code to get Active Workbook FullName to know the location of workbook.

Code:
Sub DisplayWorkbookFullName()
MsgBox ActiveWorkbook.FullName, vbInformation, "Workbook Complete Path"
End Sub
Output:

Get Active Workbook Full Path

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see output as shown above
  7. Save the file as macro enabled workbook
Get an Active Worksheet Name

You can use ActiveSheet property to return the ActiveSheet Name. You can use the following code to get Active Worksheet Name.

Code:
Sub DisplayWorkSheetName()
MsgBox ActiveSheet.Name, vbInformation, "Active Sheet Name"
End Sub
Output:

Get Active Worksheet Name

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to see the output
  6. You should see output as shown above
  7. Save the file as macro enabled workbook
Get an Active(Selected) Range Address

You can use Address property of the selected range(Selection Method). You can use the following code to get the selected range address in active sheet.

Code:
Sub SelectedRangeAddress()
'Variable Declaration
Dim MyRange As String
'Assign Selected Range Address to variable
MyRange = Selection.Address
'Display Output
MsgBox MyRange, vbInformation, "Range Address"
'-----------------------(OR)------------------
MsgBox Selection.Address, vbInformation, "Range Address"
End Sub
Output:

Get Selected Range Address

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Select a range from B2 to E11 in active sheet
  6. Goto code window and Press F5 to see the output
  7. You should see output as shown above
  8. Save the file as macro enabled workbook
Get an Active(Selected) Cell Address

You can use Address property of the selected cell(Selection Method). By using the following code you can get the selected cell address in active sheet.

Code:
Sub SelectedCellAddress()
'Variable Declaration
Dim MyCell As String
'Assign Selected Range Address to variable
MyCell = Selection.Address
'Display Output
MsgBox MyCell, vbInformation, "Cell Address"
'-----------------------(OR)------------------
MsgBox Selection.Address, vbInformation, "Cell Address"
End Sub
Output:

Get Selected Cell Address

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Select a cell F5 in active sheet
  6. Goto code window and Press F5 to see the output
  7. You should see output as shown above
  8. Save the file as macro enabled workbook
LIMITED TIME OFFER
By |January 10th, 2013|Excel VBA|1 Comment

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

One Comment

  1. Prabhaker July 5, 2017 at 4:10 PM - Reply

    thank you this is helpful

Leave A Comment


Related pages


shortcut for insert row in excelcombine columns in excel 2007how to do stacked bar chart in excel 2010excel vba range sortexcel 2010 slicer tutorialexcel vba charvba excel column widthplanning calendar template excelexcel averageif functioncreate pie chart in excel 2007vba access databasevb cellscompile excel vbaexcel dynamic charthow to find double entry in excel 2007excel tips for beginnersmacro in excel with exampleinterview basic questions and answers for freshersvba import excel to accessms excel test questions and answersexcel vba programming ebookvba countavba excel active cellhow to sort numerically in excelline spacing in excelunlock excel worksheet without passwordexcel vba ado recordsettranspose excel vbaexcel sales dashboardexample of pivot table in excel 2007activesheet unprotect passworddynamic named range excel 2010combo box in excelopen xlsx file in excel 20033d bubble chart excel 2010data entry in excel worksheetvba uppercaseexcel 2003 advanced filterlookup table excel 2010ddl and dmlvba convertexcel shortcut keys 2007how to hard return in excelhow to add chart title in excel 2007vb carriage returniferror function excel 2010vba excel columnshide columns vbahow to remove all macros from excel fileexcel activex controlpopular excel macrosexport vbaclose userform vba excelcontoh vba excelhow to convert xls to xml formatdelete duplicates in excelisnumber function in excelcombobox in excel vbavba database connection exampleexcel macro sampleshow to open xlsm file in excel 2010vba sql server connectionlogical functions in excel 2007powerpoint vbahypertext link codehow to unprotect sheet without passwordexcel lookup function exampleadodb recordset in vbaproject dashboard templateaccess vba input boxexcel vba sqlusing vlookup in vbahow to merge and center on excel