Open or Close Files

Opening Files


Following are various ways in which to open Excel files using VBA


To get a File/Open window use the following code:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Application.Dialogs(xlDialogOpen).Show
[/sourcecode]


Hard Code open files:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Workbooks.Open "MyFile.xls"
[/sourcecode]


Open File Based on Cell Contents:

To dynamically code you enter the name of the file in a cell (A1) of your Excel sheet and you write.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
varWorkbook=Range("A1").Value ‘Enter the name of the workbook to open in cell A1
Workbooks.Open varWorkbook
[/sourcecode]


Open with wildcards:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub OpenWithWildcards()
Dim sFileOpen As String

sFile = Dir("C:\DL\*TEST.xlsx") ‘Change file name and wildcards to suit lookup
If sFileOpen <> "" then
Workbooks.Open FileName:= sFileOpen
Else
MsgBox "Can’t Find File: C:\DL\*TEST.xlsx"
End If
End Sub
[/sourcecode]


Open based on cell value (in this case B2):

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
If Range("B2").Value = "111111" Then
Workbooks.Open "c:\Program Files\Excel\yourworkbookname.xls"
ElseIf Range("B2").Value = "222222" Then
Workbooks.Open "c:\Program Files\Excel\yourworkbookname2.xls"
End If
End If
End Sub
[/sourcecode]

OR:

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim varCellvalue as Long
If Target.Address = "$B$2" Then
varCellvalue = Range("B2").Value
Workbooks.Open "c:\Program Files\Excel\" & varCellvalue & ".xls"
End If
End Sub
[/sourcecode]