VBA Express : Excel - Follow any path to all your projects folders

Excel

Follow any path to all your projects folders

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

johnske

Description:

No matter where an end-user places your project folder on their computer or, even if they should rename it, the paths to all the projects folders remain open.

Discussion:

Despite all warnings, end-users can often make your project almost completely inoperable by putting it in the wrong path, and/or renaming it, AND/or they make multiple copies of it - all with different names. ActiveWorkbook.Path can take care of all those possibilities and put your mind at ease.

Code:

instructions for use

			Option Explicit 
 
 '//basic principle
Sub OpenBooks() 
    Dim ProjectPath$ 
    ProjectPath = ActiveWorkbook.Path 
     '//opens books A and B no matter whereabouts on the computer the project
     '//folder should be placed, and/or if it has also been renamed.
    Application.Workbooks.Open(ProjectPath & "\FolderA\BookA.xls").Activate 
    Application.Workbooks.Open(ProjectPath & "\FolderB\BookB.xls").Activate 
End Sub 
 
 
 '//this is the other demo used in the attachment
Sub PutDataInBooks() 
     
    Application.ScreenUpdating = False 
     
     '//(shows the differences between 'Name', 'Path', & 'FullName')
    Range("A1") = "THIS IS THE PROJECT" 
    Range("A2") = "ActiveWorkbook.Name = " & ActiveWorkbook.Name 
    Range("A3") = "ActiveWorkbook.Path = " & ActiveWorkbook.Path 
    Range("A4") = "ActiveWorkbook.FullName = " & ActiveWorkbook.FullName 
     
    Application.Workbooks.Open(ActiveWorkbook.Path & "\FolderA\BookA.xls").Activate 
    Range("A1") = "THIS IS BOOK A" 
    Range("A2") = "ActiveWorkbook.Name = " & ActiveWorkbook.Name 
    Range("A3") = "ActiveWorkbook.Path = " & ActiveWorkbook.Path 
    Range("A4") = "ActiveWorkbook.FullName = " & ActiveWorkbook.FullName 
    ActiveWorkbook.Save 
    ActiveWorkbook.Close 
     
    Application.Workbooks.Open(ActiveWorkbook.Path & "\FolderB\BookB.xls").Activate 
    Range("A1") = "THIS IS BOOK B" 
    Range("A2") = "ActiveWorkbook.Name = " & ActiveWorkbook.Name 
    Range("A3") = "ActiveWorkbook.Path = " & ActiveWorkbook.Path 
    Range("A4") = "ActiveWorkbook.FullName = " & ActiveWorkbook.FullName 
    ActiveWorkbook.Save 
    ActiveWorkbook.Close 
     '//Note: ScreenUpdating reverts to True when the Sub ends so there is no really
     '//need to specify it here - only when you want it to be True before the Sub ends.
End Sub

How to use:

Not applicable - needs to used within a complete project containing multiple workbooks
(Download the attachment for a simple example)

Test the code:

Open the attachment and place contents on the desktop
open the Project workbook that's in the Project folder
click the top button on the worksheet
now click the bottom button...
note the paths/addresses shown in the open workbooks.
Close all the books and exit the folder.
Now place the folder somewhere else
(e.g. put it in 'My Documents' or in 'Program Files').
Now repeat the first part of the above procedure and note the new paths/addresses

Sample File:

Project Folder.zip 20.17KB

Approved by mdmackillop

This entry has been viewed 194 times.