Reoccurring Corrupt files

[MaRissy]

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:

error.PNG


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.

[Aussiebear]

Welcome to VBAX MaRissy. As you suggested a dummy file would be a great start. Then tell us just exactly what it is that you are trying to do please?

[Aflatoon]

Point 5 would be my first suspect. How are the data validation lists set up? If they are using comma delimited values, then you are probably hitting the limit of 255 characters for the DV formula. That will work until you save and reopen the file at which point you will get an error and the repair function will remove the data validation.

[MaRissy]

Hi! Thanks for the responses, AussieBear I was trying to avoid making a dummy file because it would take me an hour+ to replicate but it looks like the issue is indeed the validation lists. I tested making the files this morning step by step and the one that broke the files was indeed step 5 - the files work fine until I run the final macro to refresh the data validation lists. I make the val list data by firstly by creating a dictionary of unique items and then the last step I have is joining the keys from the dictionary to a list using "," as the data behind the validation lists, which as suggested by Aflatoon seems to be more than the 255 character limit allowed.

What's the best way to build these val lists generally to avoid that 255 character limit error? Should I have the dictionaries print to a hidden tab on the files and set the val list from the range?

Thanks so much!!

I pasted the code I've been using below:

Sub refresh_voucher_page()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws1 As Worksheet
Dim Voucher As Worksheet
Dim lastlinews As Long
Dim lastcolumnws As Long
Set Voucher = wb.Sheets("4005 Voucher")
shname = InputBox("What's the data sheet name?")
Set ws1 = wb.Sheets(shname)
lastlinews = ws1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
'find last column
lastcolumnws = ws1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Dim D_Client As Object
Dim D_Date As Object
Set D_Client = CreateObject("scripting.dictionary")
Set D_Date = CreateObject("scripting.dictionary")
Set D_TimeStamp = CreateObject("scripting.dictionary")
For i = 2 To lastlinews
    D_Client(ws1.Range("A" & i).Value) = 1
    D_Date(ws1.Range("B" & i).Value) = 1
    D_TimeStamp(ws1.Range("K" & i).Value) = 1
Next
Dim val_list_client As String
Dim val_list_dates As String
Dim val_list_timestamp As String
val_list_client = Join(D_Client.keys, ",")
val_list_dates = Join(D_Date.keys, ",")
val_list_timestamp = Join(D_TimeStamp.keys, ",")
Voucher.Range("F1").Validation.Delete
Voucher.Range("F1").Validation.Add Type:=xlValidateList, Formula1:=val_list_client
Voucher.Range("J2").Validation.Delete
Voucher.Range("J2").Validation.Add Type:=xlValidateList, Formula1:=val_list_dates
Voucher.Range("F4").Validation.Delete
Voucher.Range("F4").Validation.Add Type:=xlValidateList, Formula1:=val_list_timestamp
End Sub

[Aflatoon]

Yes - putting the data into a range avoids the issue.