[RESOLVED] Opening excel from specified location

[paralinx]

Hello everyone,

I've been trying to figure this out myself, but have just run into an issue I can't resolve with searching and examples. I'm trying to open an excel file to read from it, and I've figured out how to open a NEW excel file. I can't, however, seem to figure out how to open an excel file that already exists.

Here is what I have so far:

Dim objApp As Excel.Application
Dim objBook As Excel._Workbook
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range

        ' Create a new instance of Excel and start a new workbook.
        objApp = New Excel.Application()
        objBooks = objApp.Workbooks
        objBook = objBooks.Add
        objSheets = objBook.Worksheets
        objSheet = objSheets(1)
        objApp.Visible = True

    End Sub

[toecutter]

http://www.vbforums.com/showthread.php?t=358585

post #2 should help

[paralinx]

Okay so now I have:

Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range

        objBooks = objApp.Workbooks.Open(Filename:="C:/Documents/FIJI/SoberMonitorSelection/roster.xls")
        objApp.Visible = True

And it's giving me the error ("Object reference not set to an instance of an object")

[Kochanski]

If it's just to open a file, depending if you are using VB.NET (I'm on 2008) there is a simple code used in various flavours.

Process.Start("http://www.google.co.uk")

Process.Start("NotePad.exe", (myFolderName & "\Paidrate.doc"))

Process.Start(myFolderName & "\Circulate.xls")

[paralinx]

I'm trying to open the exisiting instance of the excel file to read/write to it

I figured out that I was using the wrong slashes: (/) instead of (\). But I am still getting that error.

[Kochanski]

This is the right code, but you'll just have to keep trying to find the correct file path. I've been in this situation before myself and know how frustrating it can be. Good luck. You can try various options with the filename in a textbox as in:
objWorkbook= objExcel.Workbooks.Open(Me.TextBox1.Text)

[Kochanski]

You can also try this.

Dim XLApp As Object
        Dim WkBk As Object
        XLApp = CreateObject("Excel.Application")

        XLApp.Visible = False

        WkBk = XLApp.Workbooks.Open(Filename:=(Application.StartupPath) & "\Myfile.xls")
        WkBk.SaveAs(Application.StartupPath & "\Myfile.xls")

        ' Close Excel with the Quit method on the Application object.
        XLApp.Application.Quit()
        ' Release the object variable.
        XLApp = Nothing

[paralinx]

I definitely have the correct path to the file, and have data in the file. I did a check to see if it was "nothing"

Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range

        'objBooks = objApp.Workbooks.Open(Filename:="C:\Documents\FIJI\SoberMonitorSelection\roster.xls")
        'objApp.Visible = True

        ' Create a new instance of Excel and start a new workbook.
        If "C:\Documents\Visual Basic.NET\SoberMonitor\fijiroster.xls" IsNot Nothing Then
            objBooks = objApp.Workbooks.Open(Filename:="C:\Documents\Visual Basic.NET\SoberMonitor\fijiroster.xls")
            objBook = objBooks.Add
            objSheets = objBook.Worksheets
            objSheet = objSheets(1)
            objApp.Visible = True
        End If

It runs through the If statement, so that pathway and file does exist, but still gets to the filename:="C:...." part and says "Object reference not set to an instance of an object"

[keystone_paul]

This line

If "C:\Documents\Visual Basic.NET\SoberMonitor\fijiroster.xls" IsNot Nothing Then

Does not check to see if the file exists, it checks to see if the string literal "C:\Documents\Visual Basic.NET\SoberMonitor\fijiroster.xls" is nothing, which it obviously isn't, regardless of whether or not there is a file with that name. You could put any string you like there and it would return true!

[paralinx]

You can also try this.

Dim XLApp As Object
        Dim WkBk As Object
        XLApp = CreateObject("Excel.Application")

        XLApp.Visible = False

        WkBk = XLApp.Workbooks.Open(Filename:=(Application.StartupPath) & "\Myfile.xls")
        WkBk.SaveAs(Application.StartupPath & "\Myfile.xls")

        ' Close Excel with the Quit method on the Application object.
        XLApp.Application.Quit()
        ' Release the object variable.
        XLApp = Nothing

Ah! Bravo! This is what ended up working for me:

objApp = CreateObject("Excel.Application")
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range

        WkBk = objApp.workbooks.open(Filename:="C:\Documents\Visual Basic.NET\SoberMonitor\fijiroster.xls")
        objApp.visible = True

Thank you, Kochanski.

[Kochanski]

Well that makes me feel better. There's also a variation for file exists. Imagine your filename is in a textbox.

If Not (System.I.O.FileExists(TextBox1.text)) Then