Is there any way of updating a Cloud stored Excel spreadsheet with VBA

I am trying to find a way to 'collaborate' with Excel and still use VBA. I have written some VBA that loads/updates data in a spreadsheet. The spreadsheet is saved in Google Drive so it can be accessed on the net. I need to effectively collaborate using an app called AppSheet that enables me to also edit/update the same spreadsheet. The problem is I keep losing data without warning.

For example this afternoon the spreadsheet was updated half a dozen times, one row at a time, over a two hour period. When I got home I looked at the spreadsheet on my PC and all the records were correctly displayed. 10 minutes later I reopened the spreadsheet on the same PC and somehow it had lost all of those updates. Fortunately Appsheet sends me an email every time it updates a record so I was able to re-enter the updates but it means the update process is unreliable and the repairs are time consuming.

I have raised a separate question on EE to see if I can work out how to use VBA to update a Google Sheet as that would fix the problem because it is saved direct to the cloud file. However I am also looking for an alternative solution using Excel, since I am more familiar with it. Is there any way of getting VBA to update the cloud version of a spreadsheet instead of updating the PC based copy then trying to sync which seems to cause the problem?
Rob4077Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
You can't collaborate on  GOOGLE Drive. If anyone else opens the file at the same time then a conflicted copy is produced. A better option is OneDrive

Work together on Office documents in OneDrive
0
Rob4077Author Commented:
Thanks for that. Can I collaborate with users who are not using my Microsoft Account with OneDrive? I thought the drawback of OneDrive is that you can only collaborate with users who share your account
0
Roy CoxGroup Finance ManagerCommented:
I've not worked using the same workbook but it is supposed to be workable. I generally provide links to folders for colleagues to use specific workbooks that I can access as needed. You need to share folders with Google Drive in much the same way. Google drive has recently changed as well, I posted several links about this for someone a few weeks ago.

Overall, I think OneDrive is better.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob4077Author Commented:
Thanks Roy. I will switch to OneDrive. I tried it once before but after several crashed I abandoned it. Perhaps they've improved it now.
0
Rob4077Author Commented:
Thanks again for your help
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.