MaRissy
10-16-2023, 03:15 PM
Hi,
I have a reoccurring problem with a certain set of files. There's one main template that I use to make eight other files (usually I cut & paste as values from the main template, I haven't figured out an effective way to automate that yet). My files ALWAYS get corrupted, it's only a matter of time before I get the warning that the file is messed up from Excel. I can be using it totally fine one minute before, then as soon as I close & reopen the file it's broken. I've googled extensively and don't know where I'm going wrong. I'll walk you through what I've been doing to build these files and maybe someone more experienced than I can help point out where my problem is, I'm using a combination of VBA, Power Query, Power Pivot, and formulas to build the files.
Here's the process:
1. Several different queries linked to outside files stored in the same folder as the template
2. One big data table in the template file (data originates from pdf files that gets converted to excel format, a macro that gets run previously cleans the data into the right form) - this table gets added as a query
3. Queries are linked via power pivot relationships (I have a couple of bridge tables in here)
4. Data is loaded into 3 different pivot tables
5. VBA macro is run on one tab a few different sumif formulas referencing the data table to refresh two data validation lists
(sumif formula tab is mimicing an internal document - I didn't come up with this one myself and am not able to change the format )
For the eight other files - I'm copying and pasting the pivot tables as values (stupid I know) and copying over the formats. The relevant data gets copied over for the big data table, and then I run a macro to refresh the validation lists on the formula page. This time around I did this - ALL of my eight files got corrupted, it looked like the files had updated perfectly before I saved and closed them. It was only on re-opening them to do one last double check I received the corrupted error. The data is all still present, but the validation lists are gone and I'm not able to run any macros - I get the error "429": ActiveX component can't create object, and when I go to debug the error on the macro it's literally pointing to the second line in the code where I try and set the workbook, see screen shot below:
31117
Does anyone have an idea what's going wrong? Am I trying to combine too much technology here? Did I mess up the order you're supposed to use Power Query & Power Pivot? I would say of all the tools I have mashed in here I'm the most novice at Power Query & Power Pivot - so I suspect something went wrong there.
Thanks for all your help - If the above info isn't enough to figure out what my problem is I can try and create a dummy file without any sensitive information on there.
I have a reoccurring problem with a certain set of files. There's one main template that I use to make eight other files (usually I cut & paste as values from the main template, I haven't figured out an effective way to automate that yet). My files ALWAYS get corrupted, it's only a matter of time before I get the warning that the file is messed up from Excel. I can be using it totally fine one minute before, then as soon as I close & reopen the file it's broken. I've googled extensively and don't know where I'm going wrong. I'll walk you through what I've been doing to build these files and maybe someone more experienced than I can help point out where my problem is, I'm using a combination of VBA, Power Query, Power Pivot, and formulas to build the files.
Here's the process:
1. Several different queries linked to outside files stored in the same folder as the template
2. One big data table in the template file (data originates from pdf files that gets converted to excel format, a macro that gets run previously cleans the data into the right form) - this table gets added as a query
3. Queries are linked via power pivot relationships (I have a couple of bridge tables in here)
4. Data is loaded into 3 different pivot tables
5. VBA macro is run on one tab a few different sumif formulas referencing the data table to refresh two data validation lists
(sumif formula tab is mimicing an internal document - I didn't come up with this one myself and am not able to change the format )
For the eight other files - I'm copying and pasting the pivot tables as values (stupid I know) and copying over the formats. The relevant data gets copied over for the big data table, and then I run a macro to refresh the validation lists on the formula page. This time around I did this - ALL of my eight files got corrupted, it looked like the files had updated perfectly before I saved and closed them. It was only on re-opening them to do one last double check I received the corrupted error. The data is all still present, but the validation lists are gone and I'm not able to run any macros - I get the error "429": ActiveX component can't create object, and when I go to debug the error on the macro it's literally pointing to the second line in the code where I try and set the workbook, see screen shot below:
31117
Does anyone have an idea what's going wrong? Am I trying to combine too much technology here? Did I mess up the order you're supposed to use Power Query & Power Pivot? I would say of all the tools I have mashed in here I'm the most novice at Power Query & Power Pivot - so I suspect something went wrong there.
Thanks for all your help - If the above info isn't enough to figure out what my problem is I can try and create a dummy file without any sensitive information on there.