Skip to main content

Posts

Showing posts from March, 2019

How To Extract A Excel Workbook's Name and Path || How to retrieve the workbook’s name, filepath

Hello Friends, Welcome to dptutorials If you want a cell to return the name of a workbook, or the workbook's filename and path. Then this is the tutorial to watch out. In this tutorial, let us use a small macro to retrieve the workbook’s name, file path, and sheet name. We can do the same trick using the formulae. Right click on the sheet tab and click on view code, or press Alt+F11 to go to VBA window. Select insert, module Copy this code and paste into this module, Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function Function Filepath() As String Filepath = ThisWorkbook.FullName End Function Function SheetName(rAnyCell) Application.Volatile SheetName = rAnyCell.Parent.Name End Function Close this VBA window and go back to the Excel window. Go to Formulas tab, click on insert function, under the category, and select the user-defined, you will find three functions Filepath, sheet name, and workbook name. If you click on file path

How To Merge Two Separate Schedules Into One Master Schedule In Primavera

In this tutorial, let us learn how to Merge two separate schedules into One Master schedule in Primavera P6. In this example, I am considering two separate projects which are these two, One is master schedule and one is the subproject. First, let us open the subproject and export this schedule as a .XER file onto Desktop. and now let us start the exact process of merging two separate schedules. 1. As a first step, We need to open the project schedule into which we want to insert the other project as a sub-project. So, let us open the Master schedule here. 2. Go to Import, select XER format, Click Next 3. Select Project and click Next 4. Now select the Subproject .xer file from the Desktop and click Next 5. Under Import action, select the option as “Update Existing Project” and under import to section, select the Master schedule which is already open. Click Next and Finally Click on Finish. 6. Please note and make sure that your WBS and activity IDs should not confl

How To Name a Workbook With the Text in a Cell || Save the Workbook with the cell Name using VBA

Hello Friends, Welcome to dptutorials. If you want to save your excel workbook using the text in a specific cell as a filename, you need to watch this tutorial. Click on the cell B2, enter some text and save it, you can see the file name as same as cell B2. Let us learn this trick now, how to Name a Workbook with the Text in a Cell Step1: Right click on sheet1 tab and select the view code option or else press Alt+F11 as a shortcut to go to VBA window. Step2: Insert module and copy this code Sub SaveAsCell( ) Dim strName As String On Error GoTo InvalidName strName = Sheet1.Range("B2") ActiveWorkbook.SaveAs strName Exit Sub InvalidName: MsgBox "The text: " & strName & _ " is not a valid file name.", vbCritical, "dptutorials.com" End Sub Step3: And paste it here in the VBA window Step4: Close this VBA window and go back to the Excel sheet. Step5: From the menu bar, go to view tab and under macros, click on view macro

How To Show Only Some WBS In The Gantt Chart In Primavera P6 || Free Primavera p6 Online tutorials

In this tutorial, let us learn how to show only some WBS in the Gantt Chart in Primavera P6 Generally, Gantt chart will show bars for each activity and If your Schedule is too big, sometimes you may feel it is distracting a lot. and you may sometimes like to present a report to show and focus only some of the WBS, rather than distracting with all bars. So let us see how we can do this in Primavera P6. This is my sample schedule with around approximate 1500 activities. and you can see the cluttering in the Gantt chart with all relationships So, to do the trick, right click on the Gantt chart portion, click on Bars,  you select the bar which is being shown in the Gantt chart. Go to bar settings, by default the "show bar when the collapsed" option is checked. Now you need to uncheck them all. Then you can see clean Gantt charts like this whenever you collapse and expand any particular WBS element in Primavera P6. So, friends, this is all about this trick for no

Automatically Change Worksheet Names Based On Cell Values || Rename Excel sheet with cell contents

Hello Friends, Welcome to dptutorials In some of our Excel reports, we may want to name the sheets automatically according to the names on each sheet without manually renaming them. So, in this tutorial, let us learn how we can automatically change Worksheet names based on Cell values using a small macro. In this example, I am considering a sample report of 6 states in 6 sheets and let us rename these sheets with that of States names from cell B2 of each sheet. Now, let us follow the step-by-step procedure Step-1: Right click on any sheet and select view code or press Alt+F11 as a shortcut Step2: Click on Insert from the menu bar, click on Module to insert a module Copy the code Sub RenameSheet() Dim rs As Worksheet For Each rs In Sheets rs.Name = rs.Range("B2") Next rs End Sub And paste in this new module. Step3: Close this VBA window and go back to your Excel workbook Step4: Go to the view tab and under macros, click on view macros Step5: After se