How to Avoid Errors with Excel VBA “ThisWorkbook.Path”
Since desktop and document folders are now being synchronized with OneDrive, specifying the current folder using VBA will result in an error message saying “Path not found." The reason is that the path returned by “ThisWorkbook.Path", which used to be a local file path like “C:\Users…", is now returned as a URL like “https://domain-my.sharepoint.com/…". This sudden change can cause macros that previously worked fine to stop functioning, leading to an increase in user inquiries. In this article, we will introduce solutions to handle the errors that occur with “ThisWorkbook.Path".
※ The same error will occur in cases like the following
With ActiveWorkbook
xPath = .Path & “" 'Specify the path from the folder where the file is located
End With
Solution 1
click on the OneDrive icon , as shown in the image below. Select the “Settings" option from the menu that appears.
When the following screen is displayed, please set “File Collaboration" to “Off" in the “Advanced Settings".
With the above settings, “ChDir ThisWorkbook.Path" will work without any issues.
Solution 2
I have created a function that converts the URL to a local path. Please refer to the sample code below for usage. However, please note that although I have performed basic testing, there may be cases where it does not work depending on the environment. Please test it in your specific environment before using it.
When using it, we will utilize the following OneDrive environment variables to create and return a local path from the URL:
Environ(“OneDriveConsumer") → “C:\Users\name\SkyDrive"
Environ(“OneDriveCommercial") → “C:\Users\UserId\OneDrive – ABC Corporation"
Environ(“OneDrive") → Either of the above will be returned depending on whether it is a personal PC or a company PC.
In this case, we will use the “OneDrive" environment variable to convert the URL to a local path.
Processing details of the sample application
Clicking the “Avoidance of Current Folder Specification Error" button within the red frame below will display a file selection dialog box. If the error is not avoided, the error message mentioned at the beginning, “Path not found," will be displayed.
Sample Application Code 1 Here are the steps to create the app
The code for the “Avoidance of Current Folder Specification Error" button is as follows.
If the following code did not work properly, please try it with “Sample Application Code 2".
Option Explicit
Sub ErrorAvoidance()
'Specify the current drive where the file exists
ChDir UrlToLocal(ThisWorkbook.Path)
'Open the folder on the current drive
Dim varFileName As Variant
varFileName = Application.GetOpenFilename(FileFilter:="CSV files (*.csv),*.csv", _
Title:="Select a CSV file")
'If the cancel button is selected
If varFileName = False Then
Exit Sub
End If
End Sub
' --- The following code defines the "UrlToLocal" function ---
Public Function UrlToLocal(ByVal Url As String) As String
' Define variables to store OneDrive environment variables
Dim OneDrive As String
' Get the value of the OneDrive environment variable
OneDrive = Environ("OneDrive")
' Define a variable to store the number of characters up to "https://.../Documents"
Dim CharPosi As String
' Convert the URL to a local path
If Url Like "https://*" Then ' Check if it is a OneDrive path
'Get the number of characters up to "https://･･･････/Documents".
CharPosi = InStr(1, Url, "/Documents") + 10
' Create the local path
Url = OneDrive & Replace(Mid(Url, CharPosi), "/", Application.PathSeparator)
Else
' If it is not a OneDrive path, specify the current drive
ChDrive Left(Url, 1)
End If
' Return the created local path
UrlToLocal = Url
End FunctionSample Application Code 2
Option Explicit
Sub ErrorAvoidance()
'Specify the current drive where the file exists
ChDir UrlToLocal(ThisWorkbook.Path)
'Open the folder on the current drive
Dim varFileName As Variant
varFileName = Application.GetOpenFilename(FileFilter:="CSV files (*.csv),*.csv", _
Title:="Select a CSV file")
'If the cancel button is selected
If varFileName = False Then
Exit Sub
End If
End Sub
' --- The following code defines the "UrlToLocal" function ---
Public Function UrlToLocal(ByVal Url As String) As String
' Define variables to store OneDrive environment variables
Dim OneDrive As String
' Get the value of the OneDrive environment variable
OneDrive = Environ("OneDrive")
' Convert the URL to a local path
If Url Like "https://*" Then ' Check if it is a OneDrive path
' Create the local path
Url = OneDrive & Replace(Mid(Url, 41), "/", Application.PathSeparator)
Else
' If it is not a OneDrive path, specify the current drive
ChDrive Left(Url, 1)
End If
' Return the created local path
UrlToLocal = Url
End FunctionSample Code 3
In Sample Code 2, the local path was determined based on the number of characters, but in Sample Code 3, the local path is created by finding the position of “/".
Option Explicit ' Force variable declaration
Sub ErrorAvoidance()
'Specify the current drive where the file exists
ChDir UrlToLocal(ThisWorkbook.Path)
'Open the folder on the current drive
Dim varFileName As Variant
varFileName = Application.GetOpenFilename(FileFilter:="CSV files (*.csv),*.csv", _
Title:="Select a CSV file")
'If the cancel button is selected
If varFileName = False Then
Exit Sub
End If
End Sub
'--------Description of the "UrlToLocal" function below--------------
Public Function UrlToLocal(ByRef Url As String) As String
'Definition of variables to store OneDrive environment variables
Dim OneDrive As String
'Getting the OneDrive environment variable
OneDrive = Environ("OneDrive")
' Creating a local path from the URL
If Url Like "https://*" Then 'Determining if it is a OneDrive path
'Getting the position of the 4th "/"
Dim FourthSlashPosition As Long
FourthSlashPosition = FindFourthSlashPosition(Url)
'Creating the path of the current directory
Url = OneDrive & Replace(Mid(Url, FourthSlashPosition), "/", Application.PathSeparator)
Else
'If it's not a OneDrive path, specify the current drive
ChDrive Left(Url, 1)
End If
'Returning the created local path
UrlToLocal = Url
End Function
'--------------------------------------------------------------------------
'Function to find the position of the 4th "/"
Function FindFourthSlashPosition(ByVal text As String) As Long
Dim firstSlashPos As Long
Dim secondSlashPos As Long
Dim thirdSlashPos As Long
Dim fourthSlashPos As Long
firstSlashPos = InStr(1, text, "/")
If firstSlashPos > 0 Then
secondSlashPos = InStr(firstSlashPos + 1, text, "/")
If secondSlashPos > 0 Then
thirdSlashPos = InStr(secondSlashPos + 1, text, "/")
If thirdSlashPos > 0 Then
fourthSlashPos = InStr(thirdSlashPos + 1, text, "/")
If fourthSlashPos > 0 Then
FindFourthSlashPosition = fourthSlashPos
Else
'Handling if the 4th "/" is not found
FindFourthSlashPosition = -1
End If
Else
'Handling if the 3rd "/" is not found
FindFourthSlashPosition = -1
End If
Else
'Handling if the 2nd "/" is not found
FindFourthSlashPosition = -1
End If
Else
'Handling if the first "/" is not found
FindFourthSlashPosition = -1
End If
End FunctionI hope this helps!