0

I have an Excel xltm template which is used to produce workbooks that must be saved in one of two folders depending on the value the user enters into a specific cell in the workbook. I know how to determine the content of the cell and therefore make a decision as to which folder to save the workbook in and the destination folders will always be in the same location relative to the folder where the template is stored. However, different users may have the template folder and destination folders on different drives so I cannot use a hard coded path for the destination folders in the macro. I know I could open a file dialog and get the user to select the folder but would prefer to have the macro perform the save directly. I've tried using ThisWorkbook.Path but this doesn't work as there is no path associated with the workbook when it is created from the template. Any ideas would be appreciated.

Jeff H
  • 15
  • 5
  • Once you create the new file from the template, it has no location, and there's no way to get the the source template path, so there's no obvious solution here. https://stackoverflow.com/questions/26774890/finding-an-excel-spreadsheets-template-in-vba-script – Tim Williams Feb 28 '19 at 00:54

2 Answers2

0

If the amount of available (or varying) drives is reasonably limited, you can hard code the expected drives into an Array and run a loop checking if each drive/file path combination is available.

When the file path is available, the book will save and the loop will end.

Maybe something like this:

Sub SaveMe()

Dim FilePath As String: FilePath = "\Users\urdearboy\Documents\"
Dim Drive, i As Long

Drive = Array("D:", "Z:", "C:")

For i = LBound(Drive) To UBound(Drive)
    If PathExists(Drive(i) & FilePath) Then
        ThisWorkbook.SaveAs (Drive(i) & FilePath & "Book Name Here" & ".xlsx")
        Exit For
    End If
Next i

End Sub

Function to test if folder path exists

Function PathExists(path As String) As Boolean
    If Dir(path) <> vbNullString Then
        PathExists = True
    End If
End Function

If you do decide to use this, I would just keep this procedure separate from your other code and call this sub when it is time to save a new book. Of course you will need to pass your variable Folder path (and probably a book name) into the above macro. Then, this should be able to save with dynamic drive & folder locations

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • Thanks for the advice and sample code. Unfortunately I don't think this solves the problem because I assume it would need the macro to be customised for each user (i.e. user 1 would need FilePath set to "\Users\user1\Documents\" whilst user 2 would need FilePath set to "\Users\user2\Documents\"). I can't do this because the template the macro is in is distributed via Sharepoint and has to be the same for all users. For the time being I've coded the macro to open a file dialogue to get the user to select the appropriate folder. – Jeff H Feb 28 '19 at 15:24
  • Oh I see. You can create a wild card beginning to the location then somehow i'm sure.Maybe `"Users\" & Environ & '\Documents\......` – urdearboy Feb 28 '19 at 15:46
  • Thanks. Solved by using Environ("OneDriveCommercial"). This returns the full path of the SharePoint root folder for each user and to that I can append the path to the required folder to save the workbook in. – Jeff H Mar 02 '19 at 15:56
  • Post that as a question – urdearboy Mar 04 '19 at 23:41
0

Update - not quite the solution unfortunately! I assumed Environ("OneDriveCommercial") would return the path of the users Sharepoint folder but it doesn't - it returns the path to the users personal OneDrive folder, the same as Environ("OneDrive"). There appears to be no environment variable for finding the location of the Sharepoint folder. Instead, I used filepath = Left(Environ("OneDrive"), Len(Environ("OneDrive")) -24) & "xxxx" This gets a string equal to the path of where the user's personal OneDrive folder is, removes the characters (24 in my case) equal to the OneDrive folder name (and thus at the start of the sharepoint folder) and then appends the string (shown as xxxx) which is the remainder of the path to the required folder within the sharepoint folder.

So, for example, for User1 in company named Company Name1:

If their Personal One Drive folder is at C:\Users\User1\OneDrive - Company Name1 and their Sharepoint folder is at C:\Users\User1\Company Name1, Environ("OneDrive") returns C:\Users\User1\OneDrive - Company Name1 and filepath = Left(Environ("OneDrive"), Len(Environ("OneDrive")) -24) & "xxxx" removes the characters OneDrive - Company Name1 and the appends the characters xxxx set to equate to the path to the required folder in the sharepoint folder. This starts with Company Name1 followed by the rest of the path.

Jeff H
  • 15
  • 5