iT邦幫忙

0

Excel VBA ThisWorkbook.Path 路徑 可否傳回真實路徑? 非url (有用onedrive)

請問 vba 中 ThisWorkbook.Path
若將檔案存放的位置會有onedrive 同步
使用ThisWorkbook.Path 都會得到 https://~~~~~my-sharepoint ~~/

請問有什麼方法 將檔案放在 onddrive資料夾也可得到真實位置?
ex:
C:\Users\User\OneDrive ~~~

1 個回答

0
海綿寶寶
iT邦大神 1 級 ‧ 2021-04-01 08:58:49

2017年的答案
不確定還能不能 work
加減參考看看囉...

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function



' This is needed to get the local path, not the one drive path
Private Function GetDocLocalPath(docPath As String) As String
'return the local path for doc, which is either already a local document or a document on OneDrive
Const strcOneDrivePart As String = "https://d.docs.live.net/"
Dim strRetVal As String, bytSlashPos As Byte
    
    strRetVal = docPath & "\"
    If Left(LCase(docPath), Len(strcOneDrivePart)) = strcOneDrivePart Then 'yep, it's the OneDrive path
        'locate and remove the "remote part"
        bytSlashPos = InStr(Len(strcOneDrivePart) + 1, strRetVal, "/")
        strRetVal = Mid(docPath, bytSlashPos)
        'read the "local part" from the registry and concatenate
        strRetVal = RegKeyRead("HKEY_CURRENT_USER\Environment\OneDrive") & strRetVal
        strRetVal = Replace(strRetVal, "/", "\") 'slashes in the right direction
        strRetVal = Replace(strRetVal, "%20", " ") 'a space is a space once more
    End If
    GetDocLocalPath = strRetVal
    
End Function 
achan iT邦新手 3 級 ‧ 2021-04-01 15:57:34 檢舉

謝謝 我試過 發現 他尋找的string

Const strcOneDrivePart As String = "https://d.docs.live.net/"

跟我 系統內的不一樣 所以就被認定 非onedrive 跳過不處理
不知道 類似狀況會有多少種...

該 string 只是用來判斷是 onedrive 或是 local path
你可以自己修改
改成
https://~~~~~my-sharepoint ~~/
裡最前面那段(https:// 一直到第一個 / 為止)

我要發表回答

立即登入回答