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


unprotecting excel sheetmicrosoft excel match functionnested if functions excel 2010excel vba project exampleswhat is a hlookupexcel add ins freewaredata mining interview questions and answers pdfvb6 rename filevba set workbook nameaccess nested iifsumif formula examplevba message boxvba word application objectduplicate entries in exceldeleting duplicates in excelexcel duplicate deletesql tutorials for beginnersmatch vbanetezza sql case statementextract hyperlink in excellate binding vbamultiple project dashboard template excelado recordset openexcel instr functionusing match function in excelcountifs functionwhy we use vlookup function in exceltesting fresher interview questionsrow height in excel 2010switch case vbasample project budget spreadsheetrange vba variablevba remove duplicatesif statement in excel 2007delete duplicate cells in exceldashboard formats in excelhow to create a command button in excelsteps in creating a chart in excelremoving cells in excelvba output to text filetrend analysis chart in excelvb adodb.connectionfinding duplicates excelrow vbainterview question answer for fresherproject costing templateexcel iserrexcel 2013 if statementconstruction estimate template excelhow to use the sumif function in excelhow to use a pivot table in excel 2007excel linked cellsexcel autofit columnsexcel macros and vbaexcel vba protect worksheetvba concatenate functionunlock password protected excel workbookhow to teach excel for beginnersopen excel file using vbahow to remove empty rows from excelexcel replace commandlearn vlookup in excel 2013macro to copy worksheetbasic vba in excelvba create databaselock a spreadsheetedit macro in excelmacro vlookupiserrorunlock workbook excelmacro protect sheetproject plan template xls