Trying to create a pivottable from vb.net

[stgraham75025]

I am trying to create a pivottable from vs vb.net with no success. I continue to get the same error (Exception from HRESULT: 0x800A03EC). I have a recordset read in and ready to use, but this is where I get the error.

objpcache = wkbk.PivotCaches.Add _(SourceType:=Excel.XlPivotTableSourceType.xlExter nal)
objpcache.Recordset = obRecordset (error is here)

I do not want to write out the recordset and then use a range for the pivottable because I may end up with more than 65K lines of data. I know I have the recordset read in because I wrote it out and it was fine. I can save the file in the directory (so maybe not permissions)

Any help here would be much appreciated.


If you need more info let me know.

[stgraham75025]

I think I have this figured out. However, I will present my solution for comments. I know I have searched for this answer before and many times the person would respond back and say "I found my answer" and not present their solution.

Basically the problem was an issue with scope. The recordset belonged to the .net program not excel. So I turned the tables on it. If you set your preferences in Excel to allow access to the vbprojects and set your security level to low you can do this too. (I know this is a security problem but this will be used well within our IT system)

Just start an excel session from .net and add what ever references you need via :

vbecomp = wkbk.VBProject.VBComponents.Add _(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ ct_StdModule)

If File.Exists(refpath & "msador15.dll") Then
wkbk.VBProject.VBE.ActiveVBProject.References.AddF romFile(refpath _ & "msador15.dll")
End If

Where refpath is the path to the needed DLL or tlb file. An improvement would be to search for the file but that is not a big deal to do.

vbecomp is defined as
Dim vbecomp As Microsoft.Vbe.Interop.VBComponent

Then you create a string that contains the code that you need to "paste" into the module (I am leaving this out because the code contains clients names and such too much of a pain to change). Then add the code to the module via:

vbecomp.CodeModule.AddFromString(modstr)

modstr is my string variable containing the code.

Then run the macro:

xlapp.run("cr_pvt")

Now I know this is not the most elegant solution and Office Web Components would probably be the best solution but I do not want to go down that road. (call me lazy if you will).

If you have a better idea let me know (I am not being sarcastic here, knowledge is good, more knowledge is better). If you would like to see all of the code I could clean it up, but I will only do so if there is interest.

Thanks