The below method allows you to open an existing Excel file from a users hard drive.
Since it is using late binding you have to be a bit careful :0), however VBScript is not able to properly declare names anyway.
It is very important to regard that all references to Excel objects have to be released properly before quitting the Excel Application running as a background process.
This method automatically opens an instance of Excel and leaves it open after returning an opened workbook.
A function or sub calling this method therefore has to reference the parent Excel.Application object of a returned Workbook in order to handle an applications disposal.
After referencing the Excel.Application object you must not call its method quit until you are done processing all files opened by this instance.
Before calling the method Quit of an Excel.Application object you also have to ensure to close or save all Excel.Workbooks currently opened by an Excel.Application.
' @Author - Alexander Bolte ' @ChangeDate - 2014-06-17 ' @Description - Returning an Excel.Workbook object ' referencing an Excel file available under provided file path. ' @Param filePath - A String pointing to an Excel file, ' which is processable by Excel version currently installed on a users machine. ' @Returns an Excel.Workbook object ' referencing a file available under given file path. Function openExcelFile(filePath) Dim xlApp ' As Excel.Application Dim xlBook ' As Excel.Workbook On Error Resume Next ' Open a new instance of Excel on client machine. Set xlApp = CreateObject("Excel.Application") ' Try to open an Excel file. Set xlBook = xlApp.Workbooks.Open(filePath) If Err.Number <> 0 Then MsgBox Err.Description If Not (xlApp Is Nothing) Then ' Prevent Excel from asking, if changes to files should be saved. xlApp.DisplayAlerts = False ' Quit the application - all workbooks will be closed. xlApp.Quit Set xlBook = Nothing End If Err.Clear End If ' Clean up references, which are not needed anymore. Set xlApp = Nothing Set openExcelFile = xlBook End Function
The following example shows how to call above method and properly dispose of a running application and its resources. It is very important to dispose of Excel objects according to the Excel object tree. This means you should never dispose of a parent object before you dispose of its child objects. This would lead to runtime errors and possibly to unhandled, unwanted running instances of Excel in the background.
' Open Excel instance in background and reference application object. Set wrk = openExcelFile(filePath) Set app = wrk.Application ' ... do something with a file. ' Close referenced source workbook without saving changes before quitting the application. wrk.Close False set wrk = Nothing ' Quit the background Excel process. app.Quit Set app = Nothing