If file Exists on Sharepoint ... how to in VBA?

You can't use Dir for sharepointfolders.
Try to sync your sharepoint folder to your explorer. Then you can.

Another option is Power Query

Hello @JEC. and thank you for the suggestion. Sharepoint is currently synched on my machine, but the users who will be using this workbook will not have it synched so I am looking for a solution through VBA to just check for the file before it continues with the SUB.

Gray.

The users should sync it too. After they did, it's getting easy.

I had to get a lot of data from Sharepoint into Excel. I ended up using C# and CSOM (Client Side Object Model). I created desktop command line program that would get data and put in a database. Excel would read db. You could easily create a simple C# command line program, call it from VBA and test if the file is in Sharepoint. Google/Bing C# CSOM.

Hi Cheese,

Thank you for your response.
This method intrigues me.
I have been trying for over a week trying to get this to work. Considering my code creates a new file every day it is used I thought that checking if the file already exists would be a very simple line of code. Apparently not.

I tried your test code a few different times and regardless if the filename exists in my sharepoint or not, I get "MsgBox "File not found".
I appreciate your response, thank you. I'll keep googling.

Gray

Originally Posted by mogul

I had to get a lot of data from Sharepoint into Excel. I ended up using C# and CSOM (Client Side Object Model). I created desktop command line program that would get data and put in a database. Excel would read db. You could easily create a simple C# command line program, call it from VBA and test if the file is in Sharepoint. Google/Bing C# CSOM.

Hi Mogul,
Thank you for the response.
I was really looking for a simpler solution. I literally just need to see if the a file name exists on sharepoint if it does quit the sub, if not save the file.
There are a number of issues with this solution for me.
1. I don't know C# at all
2. The IT department is loathe to give me or anyone admin rights on the PC's here so this will be quite difficult to create. We can't even open the command prompt. Haha. heck at one point they locked out the "task manager".

Anyway, I appreciate the out of the box thinking on this.
Gray

Hi CheeseSandwich,

I am logged into sharepoint at the moment.
Debug.Print was one of the first things I tried in my tests. I did a debug print and pasted it into a browser. If the file existed it does not open it, just downloaded it right away. If it did not exist I got a " This Site Can't be reached" error.
Gray

Originally Posted by CheeseSandwich

Just noticed you have two lots of '.xlsm'

One on 'bmvarFilename'
and another on the end of:
"https://lehub.sharepoint.com/sites/CA-VTS-Ontario-Schools-London/Shared Documents/CA-VTS-MobileClear/CA-VTS-2021-2022-MobileClear/" & bmvarFilename & ".xlsm"

This will make the file name end with '.xlsm.xlsm'

Maybe correct that and retry the code.

OK this got it .. thank you .. not sure why I did not see that in the first place.

Now I am thinking, is there something else I can try instead of " ... Workbooks.Open"?
Systems are very slow here and that .open .close took almost 90 seconds ... ha ha ha . this place is hilarious.

If you can't think of anything else. no worries. I'll resolve this. I have a pop up that tells then to 'WAIT UNTIL THIS IS FINISHED', anyway, so not a huge deal.
I've Rep'd you.
Thank you so much.
Gray

Are you able to comment this to explain what exactly is goiung on .. i've not had any experience with On Error at all.

Please Login or Register  to view this content.

Amazing. thank you very much.

Gray

Thank you for this. I've been racking my brain for a week now trying to figure this out.
I need to loop through files on sharepoint and update a pivot only if the file existed (and no, syncing with explorer was not an option).

It's not the cleanest, but here's where I'm at

Please Login or Register  to view this content.