Application-defined or object-defined error

[The_Doc_Man]

I apologize for the size of this post, but it is giving me fits as I try to wrap up a project and suddenly have hit a massive roadblock. It is a complex problem and I'm a hair's breadth away from finishing it, only to find ... :banghead:

Looking for some advice on how to proceed after getting this error:

#1004, Application-defined or object-defined error

Here's the context:

I've been working on a genealogy project. The overview is that I download and parse out some GEDCOM formatted data, analyze it, and identify relationships among various persons who are direct ancestors or family members of direct ancestors. At the moment, my database for this lists over 950 persons. The parsing and relationship analysiss parts work, and any of my headaches there are simply because the older the records, the less exact they are and thus are harder to analyze and get consistency among those old records. That is NOT my complaint for this post, but it is necessary to know for the context.

Where I come into grief is in building a presentation of the results of that analysis.

I am using Access VBA to drive the generation of a spreadsheet that will show what I have found using a Family-Tree type of diagram, which technically is a SmartArt "Horizontal Hierarchy" in style. I am dealing with an Excel Application Object, workbooks, and worksheets being driving from Access VBA. I do that because the analysis of the GEDCOM data is done in Access and doing this manually for a ten-generation family tree is, how shall I say it? Prohibitively tedious.

In my approach, I have this workbook that contains a template worksheet that contains a bit of "Smart Art" graphics - more specifically the Horizontal Hierarchy in which I have a SmartArt node for the "anchor" of the family tree plus two SmartArt nodes for the parents plus four SmartArt nodes for the grandparents. The only worksheet in the template file contains the SmartArt object on a single worksheet named "Template".

The intent of the design is that when I create this diagram, I start by copying the template file and then open the copy. I next find the sheet named "Template" and copy it. Then I rename the copy to "Page n" where "n" is determined by a simple counter. After that, I start filling in the data for the starting person, that person's parents, and then the grandparents. Seven persons per page covering three generations. And the name, date of birth, and date of death DO get filled in.

If I know anything about my great-grandparents (and I often DO know something, actually), then I create a second sheet starting from the grandparent and show THAT ancestor's family tree. I add some notes in various cells on the first sheet to say "more about grandpa on sheet 2" and a similar node pointing backwards saying "more about great-grandpa's kids on page 1."

The routine that does this is recursive. I give it a starting point and it generates pages to show (person, parents, grandparents) on a page and then finds out if I know more about the grandparents' ancestry from THAT page. If so, I recursively call the page generator.

This actually generates five worksheets this way. But where I hit the wall - and the subject error - is that when the recursion returns to its caller, I start getting that error 1004. After the recursion return, something so simple as a

xWrkSht.Range("A13") = "some text"

stops working, even though BEFORE the recursion that same syntax worked for a different selected cell, both on the same sheet and on its predecessor. After the error, I can't manipulate ANYTHING. It just reaches some point and stops working entirely.

Once the code exits and all the recursion is resolved (through error returns, unfortunately), the code closes the workbook. At that point I can open the workbook directly from Excel and see that it really DID build a bunch of stuff. The names and dates are there.

I have searched the web for that error and found some notes that suggest this is a known problem related to the idea that I am copying the template worksheet and then working on the copy. One suggested workaround is to every so often just close & save the workbook, reset the object variable to Nothing, and then re-open the workbook. That doesn't appear to work. I can do this in the recursion routines because I am using "ByRef" argument passage for the Excel-based objects.

Has anyone ever run across the "Error 1004" problem working with Excel? Were you able to find a working solution? If I can get it to keep on working then I can finish the presentation part and focus on the few errors I still have in the parsing and analysis parts.

Apologies again for the size of the post, but it isn't a trivial project.

 

[Ranman256]

it means 1 of your objects is empty (not defined)
if the error is at: xWrkSht
then xWrkSht was not created.

dim xWrkSheet as worksheet

Set XL = CreateObject("excel.application")
set xWrkSheet = xl.activesheet

 

[The_Doc_Man]

I've been using the "Locals Window" to look at things. That usually says "Nothing" for objects that are dereferenced. But I could have missed it.

I'll go through and double-check with some defensive "If x Is Nothing Then ... (make it something)" code. But I've been trying to watch that. And it only occurs after recursion returns. The pages ARE created, it is only when I reach the end of the ancestry chain (in this case, patrilineal ancestry) and then start to return back down the tree that the problem happens.

If I understand ByRef vs. ByVal and stack-based calls correctly, the local objects at lower levels of recursion haven't lost their content, and because I'm using ByRef argument passage for the Excel App object and Workbook, they never lost content. Further, they are declared as Public in the declaration area of a general module I use for Excel support code. So I'm not sure how they became de-referenced. However, I will certainly look into that suggestion. The recursion code is limited to only three routines, so it will be easy enough to track.

Thanks for the suggestion. If it works, I'll come back and offer comments on what actually worked.

 

[MajP]

If it works the first time but fails the subsequent times then it may be related to this
https://support.microsoft.com/EN-US/help/178510

 

[MajP]

Happens in Word as well and it is considered "by design" and not a bug. The solution is usually to fully qualify all of your code
https://support.microsoft.com/en-us/help/189618

 

[The_Doc_Man]

Interesting, MajP. It will perhaps be a slight pain in the toches to go back and fully qualify all references, but on the other hand, it makes sense that there is an implicit reference that once can override by qualifying it. The implicit reference would then be a big issue because nobody knows where implicit references are kept. (Or HOW they are kept.) The two articles mirror what I saw online last night after midnight local time. I'm fortunate that the recursion in question is limited to three or at most four segments. They are complex but limited in size and scope so maybe it won't be so bad.

I'll give that a try this afternoon.

it is considered "by design" and not a bug

Yeah, I used to hear this all the time: It's not a bug, it's a feature.
<Snort!>

 

[MajP]

I am not sure it is what I was thinking because that is not exactly the type of code I would think would fail

xWrkSht.Range("A13") = "some text"

When the code returns from recursion can you just reassign xWrkSht? Is there a way to point to that worksheet.

 

[The_Doc_Man]

The following attempts have so far failed:

1. Copy & rename the template file, work on that copy. For each new sheet, copy the "Template" worksheet and then fill in the SmartArt items. The failure is the 1004 error noted above, it occurs only after about five such worksheet copies were made. It should also be noted that the copies that DID get made were updated with names that made sense in context. If not for the failure that caused things to stop working, it might well have done exactly what I wanted.

2. Rather than copy the template, create a new "virgin" workbook. Add pages by copying from the Template file's "Template" worksheet to the new file. Same general effect, same error.

3. Back to method 1 but EVERY TIME that I create a new sheet, I then CLOSE the whole workbook and reopen it. Since I know which worksheet I was using, I can get back there. Same error, same symptoms.

4. Using #3 approach, changed references so that I am using something like

oXLApp.Workbooks.Worksheets("Name")...

for EVERY worksheet reference.

5. Rather than go completely insane here, I know for an absolute fact that I can add a LOT of blank sheets to the workbook. So I tried to add a blank sheet and then COPY the SmartArt object to the new sheet. But THAT falls down flat because the copy on the new sheet is a PICTURE, not a Shape.SmartArt object. I can't manipulate it. Doesn't matter whether I use .Paste or .PasteSpecial, it still copies as a picture.

I have cross-posted a version of this problem on the ExcelForums site, just so we have no problems about disclosure of cross-posting. I haven't gotten a reply yet.

If the problem goes much longer, I might take a totally bizarre tactic. I might just choose to update the TEMPLATE, then copy it and not CARE that it comes across as a picture. If I can just reload the template each time, it doesn't MATTER that the final document is not modifiable once generated. I was going to encapsulate it in an PDF anyway. But I was HOPING to find a way to copy/paste the Shape.SmartArt object and keep it modifiable. It would be easier to manipulate.

For what it is worth, I have found some notes in various places that suggest that somehow Microsoft neglected to fully expose SmartArt innards to the Component Object Model, which is why the Copy/Paste operation is so difficult. It figures that they have this really NEAT feature but it is so buggy that it doesn't work as well as one would like. Yet another Microsoft disappointment.

 

[The_Doc_Man]

I'm going to close this thread. The short answer is "don't try to copy graphics. They will break your heart." Although copying a worksheet seems problematic as well.

In essence, I gave up on the bloody SmartArt and just used some graphics characters from the WingDing3 font for pointers. Then I put names in boxes, tinted them according to pink for the gals and blue for the guys. Did everything with text and never copied anything. It looks more primitive than I really wanted, but it is accurate. And the problem wasn't recursion. It was copied graphics.

No matter how many times I tried to reactivate sheets and use explicit long references starting from the Excel Application object, nothing helped. Nothing, that is, until I just flat gave up on the darned copy process. Even closing and re-opening between every new sheet, it wouldn't work. The first time I tried the version without the graphics? It failed because of something else, but it built 15 sheets in a matter of seconds. All I can say is, I'm going to consider SmartArt as not living up to its name.