[FAQ's: OD] How do I open an existing Excel Workbook?

[RobDog888]

If you need to programmatically open an existing workbook from within Excel itself you would use the Application.Workbooks.Open function. Now if you are automating Excel from another program language like VB 6 or a .NET language then you would create the application instance, select the Excel workbook and call the .Open function on the selected workbook.


Excel 97 - 2003 and VB.NET 2003 - 2008
Option Explicit On
Option Strict On
'Copyright © 2009 by RobDog888 (VB/Office Guru™). All Rights reserved.
'
' You may not reproduce or publish this
' code on any web site, online service,
' or distribute as source on any media
' without express permission.

'Add a reference to Microsoft Excel xx.0 Object Library
Imports Microsoft.Office.Interop

Public Class Form1
'Declare our Excel Application object and Workbook object
Private moApp As Excel.Application
Private moWB As Excel.Workbook

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Open a selected workbook (*.xls)
Dim OpenDlg As New OpenFileDialog
Using OpenDlg
With OpenDlg
.CheckFileExists = True
.CheckPathExists = True
.Filter = "ExcelWorkbooks Only (*.xls)|*.xls"
.FilterIndex = 1
.Multiselect = False
.ShowHelp = False
.ShowReadOnly = False
.Title = "Select an Excel Workbook file"
'If a file is selected then lets open it
If .ShowDialog = Windows.Forms.DialogResult.OK Then
'Call the .Open function and assign the return to the workbook variable for use
'in other functions/areas in our app
moWB = moApp.Workbooks.Open(Filename:=.FileName)
'Show the opened workbook/application
moApp.Visible = True
End If
End With
End Using
End Sub

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
'Close and dispose our objects
If TypeName(moWB) <> "Nothing" Then
'Can change to autosave or not
moWB.Close() 'SaveChanges:=True
End If
moWB = Nothing
If TypeName(moApp) <> "Nothing" Then
moApp.Quit()
End If
moApp = Nothing
End Sub

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Error handling on creating an Excel application as the user may not have it installed
Try
moApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

End Class