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

GaryG9595

Board Regular
Joined
Jun 13, 2014
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top