VBA Code not working on One Drive, but works on C:Drive

[GaryG9595]

Hello, can someone help me out here?
This macro works perfectly when I use the file and files being pulled from the C Drive.
But once I moved the file over to the One drive, the macro is giving me this error.
I did update the One Drive path in A1, but I still get this error below highlighted in red.
Does anyone know of a fix? Do I need a different macro.
I am basically using a master sheet that replicates columns A2 thru AC from 9 (or all if more or less are needed) different files within a folder and merging them together in the master sheet from A8 thru Ac.
Creating one file with all nine files merged together.
Thanks in advance.
Gary

Sub simpleXlsMerger1()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

Set dirObj = mergeObj.Getfolder(Sheets("CG").Range("A1").Value)
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A8:AC" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

 

[6StringJazzer]

What is the value in A1 that you are using for OneDrive?

In my experience, OneDrive files look like they are on your local drive, and it looks like they should have a path like this:

C:\Users\Jeff\OneDrive\Documents

But when accessed through VBA they actually reside on an internet server with the corresponding URL like this:

https:// onedrive.live.com/edit.aspx?resid=B354354291F8FDDF!25951&cid=b354354291f8fddc
(space inserted to prevent hotlinking to fake URL)

I have never found a solution to this and have advised people to move the files out of OneDrive if they need to open them in VBA. There may be a solution...I've never found one.

 

[GaryG9595]

What is the value in A1 that you are using for OneDrive?

In my experience, OneDrive files look like they are on your local drive, and it looks like they should have a path like this:

C:\Users\Jeff\OneDrive\Documents

But when accessed through VBA they actually reside on an internet server with the corresponding URL like this:

https:// onedrive.live.com/edit.aspx?resid=B354354291F8FDDF!25951&cid=b354354291f8fddc
(space inserted to prevent hotlinking to fake URL)

I have never found a solution to this and have advised people to move the files out of OneDrive if they need to open them in VBA. There may be a solution...I've never found one.

Thank you Jeff.
That's exactly what I am running into. We used to run this on a shared drive and it worked fine, but now they want to move it to Teams, and it is not working there either.
I'll do some more experimenting and if I find a solution, I'll post it here.
Thanks again,
Gary

 

[6StringJazzer]

Microsoft is great. I hate Microsoft.

 

[programsam]

Anyone ever find a fix for this one?

 

[RBerke]

Anyone ever find a fix for this one?

i have been using the following function. It allows me to share vba code between a window10 and a window 11 devices under the same onedrive serve, It fixes most of the problem, but application.run rud("c path\bookname.xls'!function requires a different workaround. you need to workbooks.open fullpath followed by application.run "bookname.xls!funtion.
Eventually I will write an article on experts exchange, but it might be months from now

Function rUD(ByVal FILESPEC As String) As String ' AKA expandOneDrive and xOD

' This routine "rehomes A User Drive".
' It changes any FileSpec vvvvvvvvvvv (see case3
' to replace the "C:\Users\anyonesName\"
' with "C:\Users\currentName\"
' vvvvvvvvvvvvvvvvvvVV

'it also replaces '..' "..\subdirectory" (see Case2
' with "C:\Users\currentname\subdirectory"
' with "C:\Users\currentName\"

' It also makes an occasional adjustment to shared OneDrive files (see Case1

' for example FILESPEC rUD returns
' rUD("C:\Users\rberk\AppData\roaming") c:\Users\localBob\roaming"
' rUD("..Onedrive\xxxxxxxx") c:\users\userid\OneDrive\xxxxxxxx
' rUD("..\yyyyyyy") c:\users\userid\xxxxxxxx
' many more examples are in sub testRud


' rUD function will be sprinkled throughout G.xla, Normal.dot, vbaproject.otm, personalBible etc
' and replaces many references to Environ("userprofile")


Dim leading As String ' many apps use a leading quote or double quote to accomdate spaces in file name.

Dim UserName As String, slash2 As Long, slash3_Subdirectory As Long

' step 1 remove leading quote. It will be reinserted later.
leading = Left(FILESPEC, 1)
If leading = "'" Or leading = """" Then
FILESPEC = Mid(FILESPEC, 2)
Else
leading = ""
End If


' step 2 split the first part of filespec based on the slashes
' slashes 1 2 3 4
' C:\Users\rberk\OneDrive\\documents
slash2 = InStr(4, FILESPEC, "\")
slash3_Subdirectory = InStr(slash2 + 1, FILESPEC, "\")
' step 3 rehome to filespec to the current userprofile
rUD = FILESPEC
Select Case True
Case LCase(FILESPEC) Like "https://*/*/*"
' Slashes 'slash 1 2 3
case1: ' Sign in to your Microsoft account
' skip the d.docs.live.net node and the random number node
slash2 = InStr(10, FILESPEC, "/")
slash3_Subdirectory = InStr(slash2 + 1, FILESPEC, "/")
FILESPEC = Environ("onedrive") & Mid(FILESPEC, slash3_Subdirectory)
FILESPEC = Replace(FILESPEC, "/", "\")
rUD = FILESPEC
Case Left(FILESPEC, 2) = ".."
case2: rUD = Environ("userprofile")
If FILESPEC <> ".." Then rUD = rUD & "\" & Mid(FILESPEC, 3)
Case LCase(Mid(FILESPEC, 2, 8)) = ":\users\"
case3: rUD = Environ("userprofile") & Mid(FILESPEC, slash3_Subdirectory)
End Select

'step 4 put leading quote back on
rUD = leading & rUD
rUD = Replace(rUD, "\\", "\")




End Function