Object lifecycle for COM objects passed from .NET to VBA
My organization sometimes has to use Excel to create a bunch of formatted statements (in the sense of a document that says "your account balance is $ X"), print them out in PDF format, and then merge them into one large PDF file. The commonly used method involves one sheet driven by an index cell and a list of people / data in another sheet. The VBA macro iterates through the index cell from 1 to N, then uses the Adobe Distiller API to print the formatted worksheet each time and merges the results.
For various reasons, I would like to implement most of this macro logic in C # in our Excel VSTO add-in, so that the VBA side of the process will be reduced to a few lines.
I decided to expose an API that looks something like this:
AcroPDDoc PdfBegin(Worksheet worksheet, string filename);
void PdfAddPage(AcroPDDoc pdf, Worksheet worksheet);
void PdfComplete(AcroPDDoc pdf);
The idea is that you write VBA forms:
Sub PrintToPdf()
Dim obj As IMySharedObject
Set obj = Application.COMAddIns("MyAddIn").Object
Dim pdf As Acrobat.AcroPDDoc
Dim i As Long
For i = 1 To 10
Range("counter").Value = i
If i = 1 Then
Set pdf = obj.PdfBegin(Sheets("Statement"), "C:\myFile.pdf")
Else
PdfAddPage pdf, Sheets("Statement")
End If
Next i
PdfComplete pdf
End Sub
I got curious / worried about object lifecycle AcroPDDoc
and about open file handles, Acrobat.exe processes, etc. in the event that the macro got into an error or was interrupted in the middle of execution. Don't worry too much because "close Excel and resume it" is an acceptable solution if needed. I wrote the following code in C #:
internal static class Printing
{
private static WeakReference weakref;
public static AcroPDDoc PdfBegin(Worksheet worksheet, string filename)
{
SetAdobeOutputFile(filename);
worksheet.PrintOut(ActivePrinter: "Adobe PDF");
AcroPDDoc pdf = new AcroPDDoc();
pdf.Open(filename);
weakref = new WeakReference(pdf);
return pdf;
}
public static void GC()
{
System.GC.Collect();
}
public static void test(AcroPDDoc pdf)
{
if (weakref != null) {
System.Diagnostics.Debug.WriteLine("IsAlive pre: " + weakref.IsAlive);
if (weakref.IsAlive) System.Diagnostics.Debug.WriteLine("ReferenceEquals: " + Object.ReferenceEquals(pdf, weakref.Target));
}
GC.Collect();
if (weakref != null) System.Diagnostics.Debug.WriteLine("IsAlive post: " + weakref.IsAlive);
}
}
I have released a bunch of additional Debug.WriteLine
and some other extraneous codes. I tested it with the following VBA:
Sub foo()
Dim obj As IUDFSharedObject
Set obj = Application.COMAddIns("MyAddIn").Object
Dim pdf As Acrobat.AcroPDDoc
Set pdf = obj.PdfBegin(Sheets("Statement"), "C:\myFile.pdf")
'obj.GC
'obj.test pdf
End Sub
What I found in general was that .NET does not include the link posted to VBA-land in its garbage collection reference count.
For example, if I uncomment only obj.GC
and obj.test pdf
, I am informed that I am weakref
not alive.
However, if I uncomment only obj.test pdf
, weakref
live both before and after (and I emit "ReferenceEquals: true").
Note that it pdf
is in the VBA scope all the time. I originally tested to see what happens if you let pdf
VBA out of scope too, but that doesn't matter.
For me this is much more of a problem than linking to a resource. Is there any solution that does not allow every AcroPDDoc
object generated in List
somewhere to be permanently stored in order to keep the reference count above zero?
source to share
Thanks to @yms above, I figured out what was going on and came up with a solution that I'm happy with. First, a small API modification:
void PdfBegin(AcroPDDoc pdf, Worksheet worksheet, string filename);
void PdfAddPage(AcroPDDoc pdf, Worksheet worksheet);
void PdfComplete(AcroPDDoc pdf);
Every C # method will call Mashal.ReleaseComObject(pdf)
before returning. I read what is Marshal.ReleaseComObject
considered dangerous , but I tested it in a specific failure mode and it found that it doesn't appear in practice.
Now VBA should provide the AcroPDDoc object from the beginning. So a typical usage would look like this:
Sub PrintToPdf()
Dim obj As IMySharedObject
Set obj = Application.COMAddIns("MyAddIn").Object
Dim pdf As New AcroPDDoc
Dim i As Long
For i = 1 To 10
Range("counter").Value = i
If i = 1 Then
obj.PdfBegin pdf, Sheets("Statement"), "C:\myFile.pdf"
Else
obj.PdfAddPage pdf, Sheets("Statement")
End If
Next i
obj.PdfComplete pdf
End Sub
In fact, just the announcement is now As New AcroPDDoc
and not As AcroPDDoc
with a later one Set
.
Testing has shown that VBA is very quick to talk about decreasing the recalculation of AcroPDDoc as soon as it falls out of scope or the link is set to a value Nothing
. This includes cases where an error occurs in the middle of a subroutine and the user ends execution.
Finally, the Acrobat.exe process also prompts to disable itself as soon as its refcount reaches zero, even if the file is open.
source to share
Please note that your references to AcroPDDoc in .Net are actually wrappers outside the COM object of the process you walk through different ecosystems .NET Framework does not fully control the lifetime of the underlying object, reference counting is controlled by the COM server, and as long as the link exists COM to one object, either from VBA or from .Net, the object will stay alive.
I think you will find this question and its answers interesting: RCW and reference counting when using COM interop in C #
source to share