Empty Excel file when opening it from OneDrive

Question

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.

score 2 · Answer 1 · answered Apr 26 '22 at 15:39

2

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.

answered Apr 26 '22 at 15:39

Tim Williams

The problem is not "not seeing the file", the problem is that I open it and is empty. I want to open an excel file from another and get some data from it, this way I open it but I get nothing :S – Jordi Apr 27 '22 at 06:23
I've never had any problems opening Excel files from SharePoint/OneDrive using `Workbooks.Open` and passing in a URL. – Tim Williams Apr 27 '22 at 06:44
Try pasting the URL into the Excel file open dialog and see if it opens from there – Tim Williams Apr 27 '22 at 16:16
As I said in the original post "When I try the URL on the browser the file looks fine" But nevermind, I found another way to do this; I don't see how to close / cancel the question though. – Jordi Apr 29 '22 at 06:59
@Jordi so how you did it? – pRo Feb 24 '23 at 17:00
@Jordi Did you have any success in solving this issue? – Thoughtful_monkey Jun 27 '23 at 19:21
In my case, it was a URL problem. I had to delete everything after "*.xlsx" then it worked. Initially, it was "*.xlsx?blahblah" – Thoughtful_monkey Jun 27 '23 at 19:28
@Thoughtful_monkey ; I couldn't solve the issue so I re-though the whole process and did it in a totally different (and more convoluted ) way :/ – Jordi Jun 30 '23 at 09:47

1 Answers1