Long routines that run fine 95% of the time will error out, normally with an "Object not defined" or when trying to activate chart data worksheets "Object not available" or some other similarly vague message.
Even more frustratingly, if you click continue, it carries on running completes the action it said it couldn't and works until the next random error. Go figure.
It also slows down significantly if you have had things open for a while. One routine that updates 8 different sets of slides, if you reboot before setting it off takes approx 4 minutes per set. The last time I ran it this week (forgetting to reboot), it took 1 hour 20 minutes.
since the days of FoxPro DOS, I have been doing the bulk of reports in HTML format (in the main 20-40 tags) from all ACCESS, EXCEL,....
- code visibility
- convenient to view in the browser
- opens for revision in EXCEL if necessary
- convenient to print in WORD - provides automatic selection of column widths and row heights
After 3 years, I have an update, a subtle "gotcha" that reached up and bit me.
As part of my genealogy database, which I update occasionally, I generate a Word-based document (which I then save as a PDF). This document gathers data regarding people who were named in the section of code that builds a Family Tree diagram. If they are named in any of several tree-like files, they are also named in the list that shows everything I know about the person. The details that I know are included in a bulleted list after the person's full name. The genealogy project started sometime in the mid 2010s and was written using Access 2010. (No surprise there.) I got it to work and got "pretty" word documents. I would say "...with no problems" but you know it would be a lie.
Time passed and my gaming beast - which is also my development beast - died a hard death in late 2023 - after the previous post in this thread. When I replaced the beast with a newer version that included Win 11, I bit the bullet and upgraded to Office 2021. And I recently did other work on the family tree so I ran newer, updated copies with data on a few more people here and there. And the reports were ugly again. The worst part was that the left margin crept to the right with each new person. Pretty soon it became an unreadable mess.
I verified it wasn't an input problem because the data being inserted had no leading tabs or spaces. It finally resolved to a simple problem: The bulleted lists automatically indented one level (to the right), but ending the bullet list didn't revert to the indent level that had been in force before the start of the list.
This is represents an apparent change in behavior because between Office 2010 and Office 2021, VBA-driven indenting behavior changed. I can't tell you details of WHEN or WHY it changed, but in 2010, the indent for a bulleted list either did an implicit outdent OR the indent was part of the bulleted list, and when you ended that list, your next paragraph indented normally. I don't know which, but either would have the observed effect. In 2021, the indent for the bulleted list was apparently the same - but when you ended the list, the indent DIDN'T revert towards the left.
To keep this description from being a major epic, the solution is that now, you have to consider that the left-margin indent is kept separately from whatever was done previously for bulleted lists. Probably with changes to the "style" feature, the new way this is handled is that the indent level for bullet lists is just to update the "automatic" indent level. As to WHEN this occurred, the only two time references are Office 2010 (prior behavior) and Office 2021 (new behavior.) I didn't have intermediate versions of Office on my home system and my genealogy project was never on any of my Navy PCs that DID have the intermediate versions.
Here's what fixed it for me: First, somewhere in the start of creating your Word document, you need to define a range that includes some printed paragraphs for which no special formatting has yet occurred. Specifically, it must be before the first bulleted list. This gives you visibility of the document's default settings.
Code:
WdRng.SetRange _
Start:=WdFil.Paragraphs(lPghS).Range.Start, _
End:=WdFil.Paragraphs(lPghE).Range.End 'not 1st, may have to assert bullets
WdRng.Paragraphs.LineSpacing = 12 'spacing matches print size
lIndent = WdRng.Paragraphs.LeftIndent 'copy the indentation level from here
With WdRng.Font
.Name = "Times New Roman"
.Size = 12
.Bold = vbFalse
End With
The capture of the .LeftIndent occurs before any bullet lists are declared in this code. WdRng is a range object that contains one or more paragraphs. Some time later in the code, there is loop for each person being documented, and the format will be a person name followed by a bulleted list of what I know about that person. In Office 2010, this next step was NOT required, but for Office 2021, it is. This snippet emphatically resets the range based on a previously recorded starting and ending paragraph number (lPghS and lPghE).
Code:
If lPrs <> 0 Then 'was this the first entry?
WdRng.SetRange _
Start:=WdFil.Paragraphs(lPghS).Range.Start, _
End:=WdFil.Paragraphs(lPghE).Range.End 'not 1st, have to assert bullets
WdRng.ListFormat.ApplyBulletDefault 'this is a bulleted list, simplest bullets
WdRng.Paragraphs.LineSpacing = 10 'minimize spacing
WdRng.Paragraphs.LeftIndent = lIndent 'control left-indent because of bullet side-effects
WdRng.Paragraphs.FirstLineIndent = lIndent 'and bring 1st-line indent into this alignment
End If
As you can guess from this isolated snippet, when I have a new valid "person pointer" (called lPrs), I just reinstate the initial indent level (which, by the way, is a number in points, the printer's measure of 72 points/inch.) This next is a sample of the document formatted via this code.
Sarah Aylet
Father - Unknown
Mother - Unknown
Daughter - Elizabeth Aylet born 05 Jul 1652
date: abt 1693 ; place (*): Great Gaddesden, Hertfordshire, England ; date: 05 Mar 1635
I picked a SMALL sample snippet because some of my ancestors appeared to believe firmly in that Biblical admonition "Go forth and multiply." And another fact that keeps it as a small sample is that it is from so far back that I don't have a lot of details. I've got a couple of things to tweak yet, because after other format changes, somehow I cut off details regarding the death and birth dates' labels.
