Excel won't open when form w/ creating Excel object is open 2

[Becks25Not]

Hi All,

I have a form with an OWC9 Spreadsheet control. I build an Excel Report in the control then export it. I have to open it and do some final formatting that I can't do in the OWC control.

My problem is, if the form with the OWC control & Excel object is open, Excel doesn't work. It sorts of opens but never comes up then gives a refrenced memory exception. If I close the form, I have no problem. It's not the OEC control b/c I commented out the creating of the Excel Object and didn't have the problem. Any help would be greatly appreciated!!

Here is my code to do the final formatting of Excel:

        Dim xlsApp As Excel.Application
        xlsApp = CType(CreateObject("Excel.Application"), Excel.Application)
        Dim wb As Excel.Workbook = xlsApp.Workbooks.Open(Me.strFullName)
        Dim sh As Excel.Worksheet = wb.Worksheets.Item("Sheet1")
        With sh
            Dim r As Excel.Range = sh.Cells.Range("L4")
            With r
                .WrapText = True
            End With
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            .PageSetup.PrintGridlines = False
            .PageSetup.Zoom = False
            .PageSetup.FitToPagesWide = 1
            .PageSetup.FitToPagesTall = 100
            .PageSetup.CenterFooter = "Page : (&P of &N)"
            .PageSetup.LeftMargin = xlsApp.InchesToPoints(0.35)
            .PageSetup.RightMargin = xlsApp.InchesToPoints(0.35)
            .PageSetup.TopMargin = xlsApp.InchesToPoints(0.35)
            .PageSetup.BottomMargin = xlsApp.InchesToPoints(0.5)
            .PageSetup.HeaderMargin = xlsApp.InchesToPoints(0.25)
            .PageSetup.FooterMargin = xlsApp.InchesToPoints(0.25)
        End With
        wb.Save()
        wb.Close()
        xlsApp.Quit()
        sh = Nothing
        wb = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
        xlsApp = Nothing
        GC.Collect()

Thanks!!
Becca

 

[HRoarke]

I agree with you that it's not the control. I have the same problem using Open and OpenText methods of the Excel 9.0 object - the app is clearly created in memory (besides being able to load a CSV text file and save it as XLS, the Excel.exe process is shown in TaskMan) but I cannot get the app to actually display. Can't use App.Window.Add (throws a HRESULT error), though I can enumerate the Windows of the <invisible> Excel object post-Open[Text] and get properties of them.

Stars for anyone that can give us some insight into this problem!

<bw>
"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Kris11]

May be you can try this link from MSDN

-Kris

 

[HRoarke]

Thanks, Kris, but the problemo isn't releasing resources (e.g. closing the app) - it's displaying the darned Excel interface after instantiation from a .NET app.

<bw>
"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Kris11]

Yes, I understand that. If you scroll all the way down on that link you will see how they are implementing NAR. Basically what they are saying is if you do a ReleaseComObject on every single EXCEL object(like sheet, book, range etc.) that you created and then setting all of them to nothing should clear the EXCEL.EXE from the memory. I haven't tried this, because when I had this problem, to my luck users asked to leave the EXCEL open because they wanted to see what the system just created and when the user manually closed it, excel collected EXCEL.EXE from the memory automatically. Thank god, users asked to leave the EXCEL open Anyway here is another link which explains the same thing .

-Kris

 

[Becks25Not]

Ho Kris,

Thanks for the link ... I do the ReleaseComObject(xlsApp) on the Application but not the sheet, workbook, etc so I'll give that a try & let ya know!!

Thanks ~ Becca

 

[Becks25Not]

Hi HRoarke,

I think you just need to do objExcel.Visible = True to display the book after you created the instance of the object if I am understanding you correctly!

~ Becca

 

[HRoarke]

OK Becca, as promised here's your star, given with a slightly embarassed visage - it never occurred to me that the default value of Visible would be FALSE... :-(

Thanks



<bw>
"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Becks25Not]

It's alright HRoarke, the Office objects can be funky!!

Kris,
It worked with releasing ALL objects (I was just doing the application!!) Star for you

~ Becca