Open an existing Excel File

Hot Topic

DirectX Samples complaining
[VB2005]Q of Serialport ----> Need Help
Cannot initialize array
Using TabPage and binding data
can here discuss vb6
Random Numbers
Where are the Navigate Backward and Forward buttons in VS2005 VB Text Editor?
How do I control navigation in datagridview. ie to go sideways on <enter>
Help - Data Source Configuration Wizard OLEDB ODBC CSV text files
Distributing a shareware DLL (Class Library)

Visual Basic

Browse button OpenDialog to TextBox
Strip Excel file of bad rows and leave detailrows then import columns into table
path does not exist or is not a directory
Partial Class for a Form?
Textbox Text
still handling events after dispose
Help with Selection.Find (novice with Visual Basic Editor)
OTP: How to automate "Importing a xsd header structure of an xsd into excel sheet using VB 6.0"
Populating per like statement
Please help with Dialog/PopUp

Open an existing Excel File

Hi, I have recently installed Microsoft Visual Basic 2005 Express Edition and would like to open an existing Excel file from a command button within the application I am making.

Having scanned through the help and forum sites i have made the following code which does not provide any errors, however when I click the command button the mouse cursor changes to the 'Busy' cursor then nothing happens.

Here is my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim xlTmp As Microsoft.Office.Interop.Excel.Application

xlTmp = New Microsoft.Office.Interop.Excel.Application

xlTmp.Workbooks.Open("c:\documents and settings\andy\desktop\test.xls")

End Sub

I have added the Microsft Excell Object library reference to my project. Any help in correcting the code would be much appreciated.

Thanks in anticipation,
Andy

Answer this question

Open an existing Excel File

  • TimLB

    Andy,

    In order to see the instance of Excel that you created, you must make it visible. Make the last line of code:

    xlTmp.Visible = True

    Also, you should capture and maintain a reference to the Workbook after opening. If you do not explicitly close the Workbook at some point, that instance of Excel will still be running after your application exits.



  • Ray Dyce

    Thanks alot for your reply. The file now opens correctly. I don't understand how I explicitly close the workbook in the code afterwards tho, sorry but I am quite new at this!

    Regards, Andy

  • dandoyon

    That's ok, we were all new at one point or another

    You should create an instance of an Excel Workbook at the class level (that is, outside of your Button_Click event) and then set that instance equal to the Workbook object you opened. Then, when your application ends, you can also ensure that Excel closes.

    Now, since you are displaying the Excel application it's really not that big of a deal - the user can just close Excel themselves. But just for your own reference, if you ever decide to automate Excel behind the scenes, it is good to know that Excel won't close itself just because the app that started it has ended (you could quickly find many many instances of Excel running in the Task Manager that have no visible UI).

    Here is a simple expansion of your code to give an example:

    Dim wb As Microsoft.Office.Interop.Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim xlTmp As Microsoft.Office.Interop.Excel.Application

    xlTmp = New Microsoft.Office.Interop.Excel.Application

    wb = xlTmp.Workbooks.Open("test.xls")

    xlTmp.Visible = True

    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

    wb.Close()

    End Sub

    Hope that helps!



  • Tom Morris

    Ok, I understand now, many thanks.
  • Open an existing Excel File
Answer this question