Opening an excel doc from button

[pabby]

Hi,

I have tried this code from a button click but I am getting an error

Dim wb As Excel.Workbook
Dim thisapplication As Excel.Workbooks
thisapplication.Open("F:\called.xls")

the error is : Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.


What is missing ? What should I be referencing ?

any help would be good,

thanks,
Pabby

 

[BadgerByte]

Hey Pabby,

I think you're missing the New statement. ie you should try changing the above to:-

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] wb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] New Excel.Workbook
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] thisapplication [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] New Excel.Workbooks
thisapplication.Open("F:\called.xls")[/SIZE]

Just to explain further, when you make the declaration "Dim wb as Excel.Workbook" You're telling the system that wb will be of type "Excel.Workbook", in order to actually create an instance of the Excel.Workbook object you need to use New. So you could also write the above like this:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] wb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Workbook
wb = New Excel.Workbook
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] thisapplication [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Excel.Workbooks
thisapplication = New Excel.Workbooks
thisapplication.Open("F:\called.xls")[/SIZE]

 

[pabby]

HI and thanks,

I see what you are saying but when I attempted to use that code, my IDE did not like "workbooks" in the second line that refers to "ThisApplication"

thisapplication = New Excel.Workbooks (It put a wavy line underneath Excel.Workbooks)

The dot notation in my IDE would , however, accept "WorkBook" without the 's' at the end but when I built and ran it I got this error

Exception Details: System.Runtime.InteropServices.COMException: ?

Source Error:

Line 34: Line 35: Dim wb As Excel.WorkbookLine 36: wb = New Excel.WorkbookLine 37: Dim thisapplication As Excel.WorkbooksLine 38: thisapplication = New Excel.Workbook

I don't know what it is looking for to resolve this - can you assist any further?

thanks

 

[BadgerByte]

Hey pabby,

I'm afraid I've not used interop with excel before but here is some code I found which opens and closes a workbook which may help (I take no credit).

Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range

        ' Start Excel and get Application object.
        oXL = New Excel.Application
        oXL.Visible = True

        ' Open an existing workbook.
        oWB = oXL.Workbooks.Open("d:\test.xls")  
        oSheet = oWB.ActiveSheet

        ' Make sure that you release object references.
        oXL.Visible = False
        oRng = Nothing
        oSheet = Nothing
        oWB.Close()
        oWB = Nothing
        oXL.Application.Quit()
        oXL = Nothing

        'Clean up garbage
        GC.Collect()

 

[pabby]

Running code but getting Access Denied.

Exception Details: System.UnauthorizedAccessException: Access is denied.


Line 50:
Line 51: ' Start Excel and get Application object.
Line 52: oXL = New Excel.Application
Line 53: oXL.Visible = True
Line 54:


thanks,

Paul

 

[BadgerByte]

The following threads should be helpful..

http://www.vbcity.com/forums/topic.asp?tid=56519

http://forums.aspfree.com/archive/t-28327/Create-Excel-Application-in-ASPNET-using-C

Looks like you may need to add rights to ASPNET user.

 

[pabby]

Thanks

HI Again,

thanks for all of your suggestions - I ended up going down the same route by adding ASPNET user full control permissions to the excel file that I was trying to open - but believe it or not I am still getting Access Denied !

I logged off then back on as Administrator and started the solution, but this time the button click event did nothing. I am starting to think I have a pc issue.

thanks again,

Paul