Sub ListeEnviron()
' Liste des variables d'environnement
' permet de récupérer par exemple la variable correspondant au répertoire OneDrive
Dim i As Integer, TabVar
Dim Fin As Boolean
i = 1
Fin = False
While Not Fin
If Environ(i) <> "" Then
TabVar = Split(Environ(i), "=")
Debug.Print TabVar(0) & " = " & TabVar(1)
i = i + 1
Else
Fin = True
End If
Wend
End Sub
Sub TestCheminLocal()
Dim AncienChemin As String
Dim NouveauChemin As String
AncienChemin = ActiveWorkbook.Path
'AncienChemin = "https://my.sharepoint.com/personal/Documents/MonRepertoire"
NouveauChemin = CheminLocal(AncienChemin, "OneDrive")
End Sub
Public Function BonChemin() As String
Dim NouveauChemin As String
Dim AncienChemin As String
AncienChemin = ActiveWorkbook.Path
'AncienChemin = "https://my.sharepoint.com/personal/Documents/MonRepertoire"
BonChemin = CheminLocal(AncienChemin, "OneDrive")
End Function
Function CheminLocal(pCheminIn As String, pEnv As String) As String
' exemples selon la valeur de pCheminIn et pEnv = "OneDrive" et Environ(pEnv)="C:\Users\Calizarine\OneDrive"
' https://my.sharepoint.com/personal/Documents/MonRepertoire -> C:\Users\Calizarine\OneDrive\MonRepertoire
' C:\Users\Calizarine\MonRepertoire -> C:\Users\Calizarine\OneDrive
Const RepDocument = "Documents"
Select Case True
Case InStr(1, pCheminIn, "http") <> 0
' Fichier non Local -> On remplace la partie après (RepDocument) du chemin par celui dans l'emplacement local (pEnv)
New_Chemin = Environ(pEnv) & "\" & Mid(pCheminIn, InStrRev(pCheminIn, RepDocument) + Len(RepDocument) + 1, Len(pCheminIn))
New_Chemin = Replace(New_Chemin, "/", "\")
CheminLocal = New_Chemin
Case Else
' Fichier Local -> on remplace le chemin par l'emplacement local (pEnv)
New_Chemin = Environ(pEnv)
CheminLocal = New_Chemin
End Select
Debug.Print pCheminIn & vbCrLf & New_Chemin
End Function