Files in the Same Directory

It is common practice to break up an application into a number of workbooks and keep the related workbook files in the same directory, including the workbook containing the code that controls the application. In this case, you could use the common directory name in your code when opening the related workbooks. However, if you "hard wire" the directory name into your code, you will have problems if the directory name changes, or if you copy the files to another directory on the same PC or another PC. You will have to edit the directory path in your macros.

To avoid maintenance problems in this situation, you can make use of ThisWorkbook.Path. ThisWorkbook is a reference to the workbook that contains the code. No matter where the workbook is located, the Path property of ThisWorkbook gives you the required path to locate the related files, as demonstrated in the following code:

Sub ActivateWorkbook2() Dim sPath As String Dim sFileName As String Dim sFullName As String Dim wkb As Workbook sFileName = "SalesDatal.xlsx" If blsWorkbookOpen(sFileName) Then Set wkb = Workbooks(sFileName) wkb.Activate Else sPath = ThisWorkbook.Path sFullName = sPath & "\" & sFileName Set wkb = Workbooks.Open(FileName:=sFullName) End If End Sub

0 0

Post a comment