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

bassplayer11300

New Member
Joined
Apr 19, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

alexdi

New Member
Joined
Dec 30, 2019
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top