VBA and Corrupted Workbooks: How to prevent this from happening?

[bassplayer11300]

Hi everyone,

I apologize for the long post, but I've run been running into an issue that's a complete mystery to me and am asking for some help/advice. Another team at my firm owns a massive workbook saved down in a .xlsb format (it's massive and the size had to be cut down from how it was in .xlsx), and I need to find an automated approach to take data out of the workbook, run some macros on it, and then load the output back into their workbook. Unfortunately I'm prohibited to share any data, but what I have so far is this:

• Manually open up their workbook
• Then I have a macro that takes the data out of their workbook and into my .xlsm workbook
• Run a combination of some Power Query and other macros on it for some calculations
• Then I have a Power Query set up in their workbook to pull my output into it

So all the steps above are generating the right output, but I've been running into an issue where after a couple runs my macro enabled workbook is becoming corrupt. I'll get things like "400" errors or that Excel "can't download project or library", and then my workbook will become completely unusable. I have back ups saved down and I can revert to previous versions when this happens, but ultimately I want the other team to use my tool without me, and I can't give them something that keeps becoming corrupt.

I have two thoughts for why this might be happening:

• My VBA could be bad. I could be writing bad, inefficient code that is too taxing for Excel (we are on Office 365). The workbook was rather VBA heavy before with many different processes, and I've been switching some of the processes over to Power Query to reduce the run time and I feel it's just simply more new and monitored by Microsoft.
• It's caused by Sharepoint issues. My firm recently switched to storing everything in Sharepoint and One Drive, and sometimes I have found that if my coworker opens the macro through Sharepoint, works on the macro, saves his changes, and closes the workbook, sometimes it doesn't save properly, and I'll open it up later through Sharepoint and just not get any of his changes. Excel will start prompting me with merge conflicts and force me to discard my changes and to reload the file. This happens to both of us, and it occurs usually after one of us runs the macro and then tries to save it. In addition to that, another thing that could happen after saving is that when either of us tries to open the macro later, Excel will state that is has a newer version and it needs to reopen, and we will continue to get this message no matter how many times it's reopened. Eventually, the workbook will just break for good. I apologize, I don't know what's happening so it's hard for me to articulate, but maybe this issue with Sharepoint sync is corrupting the file somehow.

My main questions are:

• What's a good automated way to pull data out of .xlsb workbook? I read some articles that said Power Query on .xlsb is really slow, and I tried it and found that to be my case which is why I just use VBA.
• Have you ever had any issues with VBA/macros and Sharepoint with your files becoming corrupted?
• Why do workbooks become corrupt?

Thanks!

 

[alexdi]

You're not the only one. I have a workbook with a similar combination of VBA and PQ. Runs and saves fine if stored in a non-Sharepoint/Onedrive folder. Save it while it's on Sharepoint, though, and it'll work a few times, but eventually corrupt and throw errors when it starts. The VBA becomes unviewable and Excel tries to 'recover' it into a butchered XLSB file.

I think your team sharing the XLSB file needs to use a database.