Open An Excel Workbook From Within A VBA Module Using 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. Parameters are used to communicate between the calling code an the called subroutine.
Program Code
Option Explicit
Option Base 1
' ************************************************
' Public Variables For File Open Dialogue Box
' ************************************************
Public strDialogueFileTitle As String
Public strFilt As String
Public intFilterIndex As Integer
Public strCancel As String
Public Sub OpenAFile()
Dim strUserSelectedWorkbook As String
Dim strUserSelectedWorksheet As String
Dim strUserSelectedWorkbookAndPath As String
' ****************************************************************************
' Present the Open File Dialogue To The User
' ****************************************************************************
strFilt = "Excel Files (*.xls),*.xls," & _
"CSV Files (*.csv),*.csv,"
intFilterIndex = 1
strDialogueFileTitle = "Select Your Input File of Choice"
' ****************************************************************************
' Call The Open Routine And Return The Workbook and Worksheet Information
' In The Parameters of the Call Statement
' ****************************************************************************
Call OpenFileDialogue(strUserSelectedWorkbook, strUserSelectedWorksheet, strUserSelectedWorkbookAndPath)
' ****************************************************************************
' If No File Was Opened, Alert The User
' ****************************************************************************
If strCancel = "Y" Then
MsgBox ("An Open Error Occurred Opening Your File Selection")
Exit Sub
End If
' ****************************************************************************
' Validate The Spreadsheet Here
' Enter Code To Check For Known Column Headings For Verification
' ****************************************************************************
' ********************************************************
' Display The Names
' ********************************************************
MsgBox ("You Opened Workbook '" & strUserSelectedWorkbook & "'" & vbCrLf & vbCrLf & _
"And Worksheet '" & strUserSelectedWorksheet & "'" & vbCrLf & vbCrLf & _
"The Full Path To The Workbook is '" & "'" & strUserSelectedWorkbookAndPath)
End Sub
Sub OpenFileDialogue(strUserSelectedWorkbookParam As String, strUserSelectedWorksheetParam As String, _
strUserSelectedWorkbookAndPathParam As String)
Dim strWorkbookFullPathAndNameDemoOnly As String
' ************************************************
' Display a File Open Dialogue Box For The User
' ************************************************
strCancel = "N"
strUserSelectedWorkbookAndPathParam = Application.GetOpenFilename _
(FileFilter:=strFilt, _
FilterIndex:=intFilterIndex, _
Title:=strDialogueFileTitle)
' ************************************************
' Exit If File Not Selected
' ************************************************
If strUserSelectedWorkbookAndPathParam = "" Then
MsgBox ("No Filename Selected")
strCancel = "Y"
Exit Sub
ElseIf strUserSelectedWorkbookAndPathParam = "False" Then
MsgBox ("You Clicked The Cancel Button")
strCancel = "Y"
Exit Sub
End If
' ************************************************
' Open The Selected File
' ************************************************
Workbooks.Open strUserSelectedWorkbookAndPathParam
' ************************************************
' An Alternate Way To Get The Full Path Name
' ************************************************
strWorkbookFullPathAndNameDemoOnly = ActiveWorkbook.FullName
' ************************************************
' Get Just The Workbook Name Without the Path
' And The Worksheet Name And Pass Back To The
' Calling Program
' ************************************************
strUserSelectedWorkbookParam = ActiveWorkbook.Name
strUserSelectedWorksheetParam = ActiveSheet.Name
End Sub