[RESOLVED] Programmatically add an excel table...

[Zmcpherson]

Hey guys,

Im having trouble with a piece of code. From MSDN's site it looks like it should work - plus I made a macro in excel and they're almost identical.

        Dim rng As Excel.Range = xlSheet.Range("D65536").End(Excel.XlDirection.xlUp)
        xlSheet.ListObjects.Add(Excel.XlSourceType.xlSourceRange, xlSheet.Range("A2:D" & rng.Address.ToString), , Excel.XlYesNoGuess.xlYes).Name = "Table1"
        xlSheet.Range("Table1[#All]").Select()
        xlSheet.ListObjects("Table1").TableStyle = "Table Style 2"

It's getting hung up on the listobjects.add line and it's just throwing an unhandled exception error.

Any help is always appreciated. Thanks Guys.

Zach

[dday9]

Is this VB.Net or VBA? If it's VBA let me know so that I may move it for you.

I'm assuming you are getting that error because you have:

xlSheet.ListObjects.Add(Excel.XlSourceType.xlSourceRange, xlSheet.Range("A2:D" & rng.Address.ToString), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

I don't know much about Excel, but I do know that in most(if not all) cases you cannot have a blank parameter with other parameters following.

[Zmcpherson]

This is for vb.net

From everything I've read - this should work:

xlSheet.ListObjects.Add(SourceType:=Excel.XlSourceType.xlSourceRange, XlListObjectHasHeaders:=Excel.XlYesNoGuess.xlYes, Destination:=xlSheet.Range("A2:D" & rng.Address.ToString)).Name = "Table1"

[kareninstructor]

Interesting, I only have AddEx

Dim oRang1 As Excel.Range = xlWorkSheet.Range("A1")
Dim oRang2 As Excel.Range = xlWorkSheet.Range("A1").End(Excel.XlDirection.xlToRight).End(Excel.XlDirection.xlDown)
Dim FinalRange As Excel.Range = xlWorkSheet.Range(oRang1, oRang2)
FinalRange.Select()
xlWorkSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, FinalRange, , Excel.XlYesNoGuess.xlGuess).Name = "Kevin"

[Zmcpherson]

Turns out the problem was two fold.

Excel.XlSourceType.xlSourceRange, should of been Excel.XlListObjectSourceType.xlSrcRange ... crazy they would have two libraries so similar

And .Add should of been .AddEx

Thank you very much - you have helped me again Kevin.