Open An Excel Workbook From Within A VBA Module Using Globals Instead of Parameters
It is frequently necessary to open another workbook from within an Excel application. The code illustrated below will present the user with an open file dialogue box (customized with the correct prompt and file name filters) and actually open the file. Once it is opened, the workbook and worksheet names are saved.
This code was set up so that the programmer can call the routine that opens the file. Instead of passing parameters back and forth, this model uses global variables, which makes the coding simpler.
Program Code
' $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
' Method 1
' $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Option Explicit
' ************************************************
' Variables For File Open Dialogue Box
' ************************************************
Public strDialogueFileTitle As String
Public strFilt As String
Public intFilterIndex As Integer
Public strCancel As String
Public strWorkbookNameAndPath As String
Public strWorkbookName As String
Public strWorksheetName As String
Public Sub OpenAFile()
Dim strWorkbookNameAndPathDemoOnly As String
' ****************************************************************************
' Set Up Filters For Which Files Should Show In The Open File Dialog Box
' ****************************************************************************
strFilt = "Excel Files (*.xls),*.xls," & _
"CSV Files (*.csv),*.csv,"
' ****************************************************************************
' Set Up The Prompt In The Dialogue Box
' ****************************************************************************
intFilterIndex = 1
strDialogueFileTitle = "Select Your Input File of Choice"
' ****************************************************************************
' Present the Open File Dialogue To The User
' ****************************************************************************
Call OpenFileDialogue
' ****************************************************************************
' Notify The User If No File Was Successfully Opened
' ****************************************************************************
If strCancel = "Y" Then
MsgBox ("An Open Error Occurred Importing Your File Selection")
Exit Sub
End If
' ****************************************************************************
' Put Your Own Code Here To Check The Newly Opened Workbook
' A Common Practice Is To Check For A Known Header Value
' To Ensure That The Correct Workbook Was Opened
' ****************************************************************************
' ********************************************************
' Save The New Workbook And Worksheet Names
' ********************************************************
strWorkbookName = ActiveWorkbook.Name
strWorksheetName = ActiveSheet.Name
' ********************************************************
' Here's Another Way To Get the Name And Path
' ********************************************************
strWorkbookNameAndPathDemoOnly = ActiveWorkbook.FullName
End Sub
Sub OpenFileDialogue()
' ************************************************
' Display a File Open Dialogue Box For The User
' ************************************************
strCancel = "N"
strWorkbookNameAndPath = Application.GetOpenFilename _
(FileFilter:=strFilt, _
FilterIndex:=intFilterIndex, _
Title:=strDialogueFileTitle)
' ************************************************
' Exit If No File Selected
' ************************************************
If strWorkbookNameAndPath = "" Then
MsgBox ("No Filename Selected")
strCancel = "Y"
Exit Sub
ElseIf strWorkbookNameAndPath = "False" Then
MsgBox ("You Clicked The Cancel Button")
strCancel = "Y"
Exit Sub
End If
' ******************************************************
' Now That You Have The User Selected File Name, Open It
' ******************************************************
Workbooks.Open strWorkbookNameAndPath
End Sub
' $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
' Method 2
' $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
' ******************************************************
' Here's An Alternative Way (Not A Part of Previous
' Example) to Get A Simple Open File Dialog Box and
' A File Save Dialog Box.
' The .AllowMultiSelect Is Set To False To Only Allow
' One File To Be Selected
' ******************************************************
Option Explicit
Dim lngCount As Variant
Dim strFilePathToRawData As String
Dim strFilePathToSave As String
Public Sub TestFileDialog()
Call UseFileDialogOpen
If strFilePathToRawData = "" Then
MsgBox ("No File Selected")
Exit Sub
End If
MsgBox (strFilePathToRawData)
Call UseFileDialogSave
If strFilePathToSave = "" Then
MsgBox ("No Save File Name Given")
Exit Sub
End If
MsgBox (strFilePathToSave)
End Sub
Private Sub UseFileDialogOpen()
Dim dlgOpenFile As FileDialog
strFilePathToRawData = ""
Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
With dlgOpenFile
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel 2003 Workbook", "*.xls"
.Filters.Add "Excel 2007-2013 Workbook", "*.xlsx"
.FilterIndex = 1
.Show
If .SelectedItems.Count < 1 Then
Exit Sub
End If
strFilePathToRawData = .SelectedItems(1)
End With
End Sub
Private Sub UseFileDialogSave()
Dim dlgSaveAs As FileDialog
strFilePathToSave = ""
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
With dlgSaveAs
.Show
If .SelectedItems.Count < 1 Then
Exit Sub
End If
strFilePathToSave = .SelectedItems(1)
End With
End Sub
' ******************************************************
' Another Example With Multiple Files Selected
' The .AllowMultiSelect Can Be Set To False For Only
' One File (You can also use Wildcards in the Input)
' ******************************************************
Option Explicit
Sub UseFileDialogOpen()
Dim lngCount As Long
' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount
End With
End Sub
' ******************************************************
' In this example, the file filter can be set up
' even using wildcards as shown below
' ******************************************************
Sub UseFileDialogOpen()
Dim fd As FileDialog
Dim lngCount As Long
Set fd = Application.FileDialog(msoFileDialogFilePicker)
' ***************************************************
' Open the file dialog
' ***************************************************
With fd
.AllowMultiSelect = False
.InitialFileName = "*Combo*.xls"
.Show
' ***************************************************
' Process The Result Of The Dialog Box
' ***************************************************
If .SelectedItems.Count < 1 Then
MsgBox ("No File Selected")
Else
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount
End If
End With
End Sub
' $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
' Method 1 Additional Example - This Does NOT Open the File -- Just Gets the Path
' $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Option Explicit
Dim varFileName As Variant
Public Sub FileOpenAndSave()
' ************************************************************************
' Using The Application.GetOpenFilename Function
' ************************************************************************
varFileName = Application.GetOpenFilename(FileFilter:="Excel File (*.xls),*.xls", FilterIndex:=1, Title:="Choose Excel File To Open")
Call DisplayFileResults
varFileName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt, " & _
"Add-In Files (*.xla), *.xla, " & _
"Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", FilterIndex:=2, Title:="Enter A FileName To Open")
Call DisplayFileResults
' ************************************************************************
' Using The Application.GetSaveAsFilename Function
' ************************************************************************
varFileName = Application.GetSaveAsFilename(FileFilter:="Excel File (*.xls),*.xls", FilterIndex:=1, Title:="Enter A FileName To Save")
Call DisplayFileResults
varFileName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", FilterIndex:=1, Title:="Enter A FileName To Save")
Call DisplayFileResults
varFileName = Application.GetSaveAsFilename(FileFilter:="Visual Basic Files (*.bas; *.txt),*.bas;*.txt", FilterIndex:=1, Title:="Enter A FileName To Save")
Call DisplayFileResults
varFileName = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla", FilterIndex:=2, Title:="Enter A FileName To Save")
Call DisplayFileResults
varFileName = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt, " & _
"Add-In Files (*.xla), *.xla, " & _
"Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", FilterIndex:=2, Title:="Enter A FileName To Save")
Call DisplayFileResults
End Sub
Private Sub DisplayFileResults()
If varFileName <> False Then
MsgBox "Save As Filename Is " & varFileName
Else
MsgBox ("You Did Not Provide A Valid Name")
End If
End Sub