Empty Excel file when opening it from OneDrive-CodePudding

Code I've tried to open the file:

sfilename = "https://url"
Set xl = CreateObject("Excel.Application")
Set xlsheet = xl.Application.Workbooks.Open(Filename:=sfilename, ReadOnly:=True)

and with

Set xlsheet = xl.Workbooks.Open(Filename:=sfilename, ReadOnly:=True)

This opens a file (tested the URL a few times) but is empty, completely blank.

Code I've also tried:

sfilename = "https://url"
Set xl = CreateObject("Excel.Sheet")
Set xlsheet = xl.Application.Workbooks.Open(Filename:=sfilename, ReadOnly:=True)

This way the document doesn't open (as far as I can see, maybe in the background) but getting Range("A1").Value returns 'empty'.

When I try the URL on the browser the file looks fine.

What am I doing wrong?

P.S. A weird issue appears after testing when re-opening excel, it says it has an unsaved document dated 1/1/1601 at 2:00.

CodePudding user response：

Set xl = CreateObject("Excel.Application")

creates a new instance of Excel, but that instance will not be visible until you run:

xl.Visible = True

So the file likely did open (unless you got an error?), but you can't see it.

If you check in Task Manager you may see the instances you already opened.

CodePudding user response：

you can open the excel file which is located on the OneDrive by using the following code and function.

Public Sub OpenOneDriveWbk()

Dim File_name As String

File_name = "https://SharePointURLhere"
File_name = OneDriveGetLocalFile(File_name)
Workbooks.Open fileName:=File_name

End Sub

Public Function OneDriveGetLocalFile(fileName As String) As String
    ' Set default return
    OneDriveGetLocalFile = fileName
    
    Const HKEY_CURRENT_USER = &H80000001

    Dim strValue As String
    
    Dim objReg As Object: Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    Dim strRegPath As String: strRegPath = "Software\SyncEngines\Providers\OneDrive\"
    Dim arrSubKeys() As Variant
    objReg.EnumKey HKEY_CURRENT_USER, strRegPath, arrSubKeys
    
    Dim varKey As Variant
    For Each varKey In arrSubKeys
        ' check if this key has a value named "UrlNamespace", and save the value to strValue
        objReg.getStringValue HKEY_CURRENT_USER, strRegPath & varKey, "UrlNamespace", strValue
    
        ' If the namespace is in FullName, then we know we have a URL and need to get the path on disk
        If InStr(fileName, strValue) > 0 Then
            Dim strTemp As String
            Dim strCID As String
            Dim strMountpoint As String
            
            ' Get the mount point for OneDrive
            objReg.getStringValue HKEY_CURRENT_USER, strRegPath & varKey, "MountPoint", strMountpoint
            
            ' Get the CID
            objReg.getStringValue HKEY_CURRENT_USER, strRegPath & varKey, "CID", strCID
            
            ' Add a slash, if the CID returned something
            If strCID <> vbNullString Then
                strCID = "/" & strCID
            End If

            ' strip off the namespace and CID
            strTemp = Right(fileName, Len(fileName) - Len(strValue & strCID))
            
            ' replace all forward slashes with backslashes
            OneDriveGetLocalFile = strMountpoint & Application.PathSeparator & Replace(strTemp, "/", "\")
            Exit Function
        End If
    Next
    
End Function

If this answered your question please accept it as solution and vote up ;)