Is there a way to avoid conflicts syncing wit...

[Rob_atYahoo]

Is there a way to avoid conflicts syncing with Excel on Google Drive?

I’m trying to keep a delivery list up to date during the day. I’ve written VBA code that reads emails as they arrive, dissects relevant details, runs a few functions that I can’t do with Google Script, then updates or adds the record in an Excel spreadsheet.

I’ve also written an AppSheet App to help me plan the deliveries, record the details and take some photos.

However I have a real problem using Excel. If Outlook is processing the Excel spreadsheet while the App is updating it, the Spreadsheet wins and my App update is lost.

I know it would work much better with Google Sheets but I don’t have the skill to read and write to a Google Sheet with VBA.

Has anyone found a solution that I could use to overcome my problem? I’d rather not try to keep an Excel and Google Sheet in sync as I fear problems

[James_W_Rezin]

The problem is that Appsheet when it updates the excel leaves it in a state that other MS Office applications do not recognise. Excel itself is fine.

I have created a system that needs to work with MS Access.

I

overcame the issue by using csv files as the data stores.

You can still have a master excel workbook that just contains the VBA modules.

[Rob_atYahoo]

Thanks James. I’m a little confused. How do you link to the csv in Access? I thought Access links to csv are read only like they are to Excel.

How do you update the csv?

[James_W_Rezin]

I do not believe that there will be a problem as appsheet is a multi user interface.

I could be wrong. Another precaution I took was to schedule the vba code to run out of hours using Windows scheduler.

[Phil]

@Rob_atYahoo

AppSheet is able to read and write CSV files. We use a third party CSV Helper library to do this.

I am not sure how concurrency control would work with VBA scripts. Do you know what VBA is doing with respect to concurrency control?

[James_W_Rezin]

@Rob_atYahoo @Philip_Garrett_Appsh Hence my out of hours scheduling suggestion.

[Phil]

@James_W_Rezin

Yes, that sounds prudent.

[Rob_atYahoo]

And therein lies my problem.

I need the vba to do scheduling progressively during the day every 30 minutes or so. I do deliveries once a week to each area.if i am in an area and an order comes through i want it added to my list asap to avoid

potential 1 week delivery delay.

At any rate all these comments and some research i did got me thinking and i may have an idea to resolve this and a few other issues i have in one fix.

Thanks to all of you for your kind assistance.

[Rob_atYahoo]

Hi James, Do you read it into MS Access, make your changes and re-write it as a replacement csv? Can Appsheet work with a csv file? I am trying to understand what you mean.

In my case I am actually using MS Outlook to open the Excel spreadsheet. The sheet opens fine, before and after Appsheet has finished with it, but Appsheet tries to sync WHILE Outlook has it, that’s when the data is lost.

[Aleksi_Alkio]

I would use gSheet as a backend. It sounds you know how to write scripts but have you thought to use IFTTT or some other platforms with the gSheet? It can save your email directly to the gSheet without VBA or scripting. IFTTT will only write to the gSheet and there won’t be any syncing problems.

[Rob_atYahoo]

Thanks for the suggestion.

Unfortunately, although I am fairly confident with VBA, I am not skilled at gSheet script and I have never used IFTTT so I guess I have a whole new learning curve to pursue. I had hoped my challenge would end once I figured out how to use AppSheet to achieve my goals.

One added complication using your suggested approach is that I have a function that calls a DLL written for use with VBA so I am not confident that I will be able to use that with Google.

I was hoping for an easier solution but it looks like there is none for now. Thanks again

[James_W_Rezin]

@Rob_atYahoo

You do not have to link to Access. I mentionrd it as an example. Your master Excel workbook can read update a csv. It can also call outlook for you to send recieve emails.

Your system can be exactly the same except the resultant data is in the csv in dropbox or some other cloud repository.

[Rob_atYahoo]

But James, how does AppSheet interact with the csv file. Storing and updating data in a csv with vba is easy. I am missing something.

Sorry

[James_W_Rezin]

In exactly the same way as it does with Excel. Open your existing data file in excel then Save as csv. Point appsheet at the csv now residing on google drive.

[Rob_atYahoo]

Oh I understand now. I didn’t realize that AppSheet could work with a csv file. I assume there is some way AppSheet realises the file is locked while vba is rewriting.

Otherwise the problem remains