VBA code to copy worksheet from a closed workbook on Sharepoint to current workbook

Question

I am trying to make a macro that copies a worksheet (wsReport) from another workbook (wbReport), located on Sharepoint, to the workbook that the macro is in (wbMain), putting the copied worksheet after the worksheet named "Main" in wbMain. wsReport is the only worksheet in wbReport. This should be done without visibly 'opening' wbReport - I am doing this by setting ScreenUpdating to False.

The relevant code I have is below:

Application.ScreenUpdating = False

Dim wbReport as Workbook
Dim wsReport as Worksheet
Dim wsMain as Worksheet
Dim ReportPath as String
Dim NewWindow as New Excel.Application

Set wsMain = ThisWorkbook.Worksheets("Main")
ReportPath = "https://company.sharepoint.com/FilePath"
Set wbReport = NewWindow.Workbooks.Open(ReportPath)
Set wsReport = wbReport.Worksheets(1)
wsReport.Copy After:=wsMain

Everything works up until the last line. The path is correct, if I make ScreenUpdating = True it visually opens the correct file. If I step through the code, I can do

Debug.Print wsReport.Range("A1")

and it returns the value from cell A1 of wsReport, so I know that's working. But as soon as it runs the last line of code (the line to actually copy), I get **"Run-time error '1004': No such interface supported". **

I can't figure out why this error is coming up. I can't find a lot of information on this error as it pertains to copying data, but it seems that other people who have had the issue had it by not properly referring to the worksheet in the other file everywhere they should have (eg. "wsReport.Range(Range("A1").Offset..." when it should have been "wsReport.Range(wsReport.Range("A1").Offset...). I think I have avoided this by clearly defining all of my relevant workbooks and worksheets (it shouldn't matter what workbook VBA thinks it's in, wsReport should always refer to 'https://company.sharepoint.com/FilePath'.Worksheets(1), and wsMain should always refer to the "Main" sheet in the file with the macro.)

I can get something similar to work if instead of copying the entire sheet, I copy the cell values (wsReport.Cells.Copy... xlPasteValues) to a new sheet in wbMain. However, I need everything from wsReport, including formatting, notes, etc., so I can't just copy and paste values. When I try to use xlPasteAll, it does a weird thing and pastes a transparent .png of the data in wsReport instead of the actual data - this obviously doesn't work. I figure a solution to this could also work, but just copying the whole sheet as-is seems like it would be easier to work with.

In general, any guidance I can find on how to do this seems to just say "use the Worksheets.Copy method properly," which as far as I can tell I am. Maybe I'm just missing something extremely obvious. It's possible that my code that I showed here isn't perfect (I anonymized it and might have missed something), but this is the first few lines of code in my macro so there's nothing happening before this that might be breaking something.

Answer 1

Figured it out - I got the method to open a file from Sharepoint from another answer, and didn't really critically look at it once I established it worked. Thanks to BigBen for finding something that showed you couldn't .Copy between instances, and Tim Williams for giving me the hint that it was not necessary. There was no need to open a new instance of Excel. The following code does what I wanted it to do:

Application.ScreenUpdating = False

Dim wbReport as Workbook
Dim wsReport as Worksheet
Dim wsMain as Worksheet
Dim ReportPath as String

Set wsMain = ThisWorkbook.Worksheets("Main")
ReportPath = "https://company.sharepoint.com/FilePath"
Set wbReport = Workbooks.Open(ReportPath)
Set wsReport = wbReport.Worksheets(1)
wsReport.Copy After:=wsMain
wbReport.Close

You can just directly open the file using the path, and just need to make sure you close it after you've copied if you don't want the end user to ever see it open (with screen updating off it never visually opens a window unless you debug and stop it between opening and closing.)