fred3
Member
I have a set of workbooks that are identical in form and differ only in the data they contain. These were working 10 years ago.
I have a Summary workbook that grabs data from the others. This was also working 10 years ago.
Now, when the Summary goes to get data from one of the source workbooks, I'm getting popups.
The popups interrupt the VBA code that is doing the data access work. So, this process fails.
The popups can have a few forms such as linking to a questionable source, etc. and I've been able to fix most of them.
However, I'm now getting an unexpected popup that says there is "something wrong" and cryptic XML tells me it's on one Sheet. But, that's not nearly good enough to find and fix.
A formula scan comes back clean.
I'd like to know of other tools that may help me find the remaining "problems".... I guess they could be in the worksheets or also in the VBA.
I finally realized that it was a bad idea to have separate, unique VBA in each of the source workbooks in that they are supposed to be identical. So, I created a Common Workbook with all the VBA macros or subs that is registered in each of the source workbooks. That way, VBA changes apply to all.
It's a simple matter to start with a "known good" source workbook, to change its data source and to save the file with a new appropriate name. That way the workbooks / worksheet structure can be made uniform whenever needed. And, this can be done programmatically in VBA so it's easy.
I should think that a problem in Excel that is FOUND at startup could be revealed re: WHAT and WHERE. That would be very helpful and I don't know how to do that.
I have a Summary workbook that grabs data from the others. This was also working 10 years ago.
Now, when the Summary goes to get data from one of the source workbooks, I'm getting popups.
The popups interrupt the VBA code that is doing the data access work. So, this process fails.
The popups can have a few forms such as linking to a questionable source, etc. and I've been able to fix most of them.
However, I'm now getting an unexpected popup that says there is "something wrong" and cryptic XML tells me it's on one Sheet. But, that's not nearly good enough to find and fix.
A formula scan comes back clean.
I'd like to know of other tools that may help me find the remaining "problems".... I guess they could be in the worksheets or also in the VBA.
I finally realized that it was a bad idea to have separate, unique VBA in each of the source workbooks in that they are supposed to be identical. So, I created a Common Workbook with all the VBA macros or subs that is registered in each of the source workbooks. That way, VBA changes apply to all.
It's a simple matter to start with a "known good" source workbook, to change its data source and to save the file with a new appropriate name. That way the workbooks / worksheet structure can be made uniform whenever needed. And, this can be done programmatically in VBA so it's easy.
I should think that a problem in Excel that is FOUND at startup could be revealed re: WHAT and WHERE. That would be very helpful and I don't know how to do that.