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
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
' ******************************************************
' Here's An Alternative Way (Not A Part of Previous
' Example) to Get A Simple Open File Dialog Box.
' The .AllowMultiSelect Can Be Set To False For Only
' One File (You can 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