Unable to get Property of Workbook Class

[Charles Hall]

Hi,

I'm the last bits of porting a vb6 application to vb.net and ExcelDNA

most things are working well, but I am stumped by a couple of errors

In one I am trying to change the links in an open workbook

Just to try things I am using the Immediate Window to make the request more explicit

?ExcelDnaUtil.Application.ActiveWorkbook.ChangeLink(curLinks(i), ThisWorkbook.FullName, XlLinkType.xlLinkTypeExcelLinks)
Exception thrown: 'System.Reflection.TargetInvocationException' in mscorlib.dll
Exception thrown: 'System.Runtime.InteropServices.COMException' in Microsoft.VisualBasic.dll

System.Runtime.InteropServices.COMException: 'Unable to get the ChangeLink property of the Workbook class'

This is not a UDF - just a vba to vb.net call using Application.Run

It must be something I am doing, since other changes to workbook or range properties work

Please help

thanks

[Govert van Drimmelen]

In the line you show, I think the “ThisWorkbook” looks suspect.

Inside a VBA macro it might have a sensible value, but inside the Excel-DNA add-in context it won’t.

I can’t see what “curLinks” is, but I’m gussing it is ActiveWorkbook.LinkSources, and things are wrapper in some loop, so that should be OK.

I’m not quite able to reproduce the message you get, though.

 

I would, however, recommend that you move the code in the Excel-DNA add-in to early-binding mode for the COM object interop.

This means having a reference to the Primary Interop Assembly (PIA) for the Excel COM object model , and declaring the types of the COM variables.

One easy way to get a reference to the interop assemblies for Excel 2010 is to install the NuGet package “ExcelDna.Interop”.

(This should work for all newer versions too, though features from new versions won’t show up).

You don’t need to distribute anything extra.

 

Then you need to set the type of the Application object somewhere, e.g.

 

Imports ExcelDna.Integration

Imports Microsoft.Office.Interop.Excel

 

Public Module MyStuff

 

Sub DoMyWork()

    Dim xlApp As Application = ExcelDnaUtil.Application

 

    xlApp.ActiveWorkbook…….. etc

 

End Sub

 

End Module

 

This will give you IntelliSense and error checking while compiling, but it also has an effect on how the VB.NET figures out which method to call.

So your error would at least change, and you’d have to change “ThisWorkbook” to “xlApp.ThisWorkbook”, which would give you a clue.

 

 

Anyway – that’s some ideas, the reason for this glitch is small, and then it should work fine.

Please write back if you’re still stuck, or run into anything else that’s interesting.

 

I’d also be very interested in anything else you can say about your experience in moving this code from VB6 to VB.NET and Excel-DNA.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/f0f9cad4-4368-4549-adbc-e84603a6e1cco%40googlegroups.com.