如何获取存储在本地OneDrive文件夹中的Excel工作簿的路径+文件名?(与其URL相反!)

gorkyyrv  于 2023-01-06  发布在  其他
关注(0)|答案(7)|浏览(498)

我有一个Excel工作簿存储在我的PC上本地,但在我的 OneDrive 同步文件夹中。每当我尝试(从即时窗口和编程方式):

? Excel.Application.ThisWorkbook.FullName

我得到的结果是:
https://d.docs.live.net/c31ceb5b47a36fa2/VBA/learnVBAmacros.xlsb
而我的文件的真实的本地路径是:
C:\用户\访问集\OneDrive\VBA\学习VB宏.xlsb
如何检索工作簿的后一个LOCAL路径,而不是它在OneDrive上的URL?

sxissh06

sxissh061#

只需转到您的一个驱动器设置,并取消勾选“使用办公应用程序同步...”
enter image description here

ssm49v7z

ssm49v7z2#

拆分文件名并使用本地OneDrive文件夹的环境变量。

dim fn as string

fn = ThisWorkbook.FullName
'split off the workbook name if fullname is a url
fn = split(fn, "/")(ubound(split(fn, "/")))
'split off the workbook name if fullname is a local path
fn = Split(fn, "\")(UBound(Split(fn, "\")))
'add the environment var
fn = environ("onedrive") & "\" & fn

'check to see if it exists
if len(dir(fn)) > 0 then
    debug.print fn
end if

请注意,有OneDrive和OneDriveConsumer环境变量。我自己的环境变量是相同的,但每个都必须有原因。

myss37ts

myss37ts3#

这个可以用,对不起,我不记得在哪里找到的

Sub GetDocLocalPath_Test()

  MsgBox GetDocLocalPath(ThisWorkbook.FullName)

End Sub

Function GetDocLocalPath(docPath As String) As String
'Gel Local Path NOT URL to 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

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
jfewjypa

jfewjypa4#

我也遇到了同样的问题,解决起来很容易。
如果您的文件在OneDrive上等待同步,则URL地址的值为ThisWorkBook.Path
除非文件同步,否则ThisWorkBook.Path的值将包含文件的本地地址。

dldeef67

dldeef675#

结合前面的几个答案,我选择了这个函数。它对OneDrive URL的格式做了更少的假设,并且它使用环境而不是注册表。
注意:它 * 仍然 * 对OneDrive URL做出假设。具体来说:

  • 假设以“https:“开头
  • 假设主机名后面只有一个URL路径组件
  • 假设以下内容与本地文件系统匹配
Function GetWorkbookDirectory() As String

    Dim sPath As String
    Dim sOneDrive As String
    Dim iPos As Integer
    
    sPath = Application.ActiveWorkbook.Path
    
    ' Is this a OneDrive path?
    If Left(sPath, 6) = "https:" Then
        ' Find the start of the "local part" of the name
        iPos = InStr(sPath, "//")           ' Find start of URL hostname
        iPos = InStr(iPos + 2, sPath, "/")  ' Find end of URL hostname
        iPos = InStr(iPos + 1, sPath, "/")  ' Find start of local part
        
        ' Join that with the local location for OneDrive files
        sPath = Environ("OneDrive") & Mid(sPath, iPos)
        sPath = Replace(sPath, "/", Application.PathSeparator)
    End If
    
    GetWorkbookDirectory = sPath

End Function
eivnm1vs

eivnm1vs6#

我使用FileSystemObject解决了这个问题。

Dim fso as FileSystemObject, localPath as String, localFullFileName as String
localPath = fso.GetParentFolderName(fso.GetAbsolutePathName(Application.ActiveWorkbook.Name))
localFullFileName = fso.GetAbsolutePathName(Application.ActiveWorkbook.Name)
ulydmbyx

ulydmbyx7#

我用CurDir解决了这个问题

Function GetFilePath() As String
    GetFilePath = CurDir
End Function

相关问题