Excel Automation failures....please help!

M

MoveYourShoes

Member
Joined
Oct 2, 2003
Messages
15
Location
Cincinnati, Ohio
I have been fighting with this for awhile now. Im sure theres a reason for it. Every sample I try that automates Excel always works on my development machine but on a test machine it always fails.

With this particular sample it fails on this line:
WSheet = EXL.Workbooks.Add.Worksheets.Add

Other sample lines that also fail are:
xlbook = xlapp.Workbooks.Add

and

xlsheet = xlapp.Sheets(1)

This line always executes fine:
xlapp = New Excel.Application

but any subsequent code that actually uses the xlapp object always fails....

and I ALWAYS get this same error message:
queryinterface for interface excel._application failed


I hope SOMEONE can help figure out how to get around this, if not then Ill chalk it up to just one more thing that VS.NET doesnt do that its supposed to do unless Im doing it on my development machine!! (grrr)
 

Attachments

Heres some actual code (This code runs without a hitch on my development machine, after building, packaging and installing on a test machine, windows xp pro, I get the failure error that I mentioned above)

Code: 
Public Class CSendToExcel

    Private ds As DataSet

    Sub New(ByRef m_Ds As DataSet)

        ds = m_Ds

    End Sub

    Sub Dispose()

        GC.Collect()

    End Sub

    Public Sub SendDataToExcel()

        MsgBox("success .1")
        Dim xlapp As Excel.Application
        MsgBox("success .2")
        Dim xlbook As Excel.Workbook
        MsgBox("success .3")
        Dim xlsheet As Excel.Worksheet


        MsgBox("success .5")
        Try
            xlapp = New Excel.Application
            MsgBox("success .6")
            xlbook = xlapp.Workbooks.Add  THIS LINE FAILS ON THE XP MACHINE BUT THE WHOLE CLASS RUNS PERFECT ON MY DEVELOPMENT MACHINE
            MsgBox("success .7")
            xlsheet = xlapp.Sheets(1)

            MsgBox("success 1")

            With xlsheet
                .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
                .PageSetup.LeftMargin = 0.23
                .PageSetup.LeftMargin = 0.5
                .Range("A1", "J35").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft

                .Name = "Labor Model"
                .Cells(1, 1).Value = "Labor Model"
                .Cells(1, 1).Font.Size = 14

                .Cells(3, 1).Value = "Department"
                .Cells(3, 3).Value = ds.Tables("Header").Rows(0).Item(1)

                .Cells(4, 1).Value = "Section"
                .Cells(4, 3).Value = ds.Tables("Header").Rows(0).Item(2)

                .Cells(5, 1).value = "Shift"
                .Cells(5, 3).Value = ds.Tables("Header").Rows(0).Item(3)

                .Cells(6, 1).value = "Regular Hours"
                .Cells(6, 3).Value = ds.Tables("Header").Rows(0).Item(4)

                .Cells(7, 1).value = "OT Hours"
                .Cells(7, 3).Value = ds.Tables("Header").Rows(0).Item(5)

                .Cells(8, 1).Value = "Total Est Vol"
                .Cells(8, 3).Value = Format(ds.Tables("Header").Rows(0).Item(8), "###,###,###.##")

                .Cells(9, 1).value = "Total Est Salv"
                .Cells(9, 3).Value = Format(ds.Tables("Header").Rows(0).Item(9), "$###,###,###.##")

                .Cells(10, 1).value = "Total Exp Salv"
                .Cells(10, 3).Value = Format(ds.Tables("Header").Rows(0).Item(10), "$###,###,###.##")

                .Cells(11, 1).value = "Total Exp Units"
                .Cells(11, 3).Value = Format(ds.Tables("Header").Rows(0).Item(11), "###,###,###.##")

                .Range("A1", "A35").ColumnWidth = 12.71
                .Range("A1", "J35").ColumnWidth = 12.0
                .Range("C1", "C35").ColumnWidth = 14.0
                .Range("A3", "J35").Font.Size = 10
            End With

            MsgBox("Success 2")
            xlsheet.PrintOut()

            xlapp.Quit()
        Catch ex As Exception
            MsgBox(Err.Description)
        Finally
            xlsheet = Nothing
            xlapp = Nothing
        End Try

    End Sub

End Class
 
Last edited by a moderator:
Ive been fighting with this type of thing for way too long now. What led me to use the Excel Automation is because I cant get the Crystal Reports to work either.. same type of thing, I can get it to run fine on my development PC but fails on test PCs. In the Crystal case I get keycode errors but have no idea why, its a registered version of CR for .NET and I have the Crystal reg key they sent me.
Basically my problem is getting data to a vehicle that will format and print it....
And Im just not having any luck.

Thanks for your help,

C. David Smith, MCSD
Avon Products, Inc.
david.smith@avon.com
 
Not sure if itll help, but do you have MDACv2.7 on the machines on which you are deploying? VS.Net gave me a similar error when I was using ADO...they could somehow be related (I wouldnt put it past MS). Good luck!
 
Back
Top