Databases Reference
In-Depth Information
2.4
AUTOMATICALLY OPENING FILES AND TEMPLATES
Although using a dialog box gives the user the ultimate in flexibility in terms of choosing a file, the
price to be paid for such flexibility is speed. The user must manually specify each file to be loaded
or saved by the macro. In the instance where there is no object consistency in the file names and
locations to be utilized, there is no alternative to such an approach. However, when file names and
locations are predictable by means of an algorithm, it makes sense to have Excel open or save them
without any intervention. The next function will open a file automatically, provided a valid file path
is passed to the function.
Function AutoLoadFile(ByVal fullpath$) As String
'Subroutine to Automatically Load a File into a Excel
On Error GoTo FileOpenErr
'Check for Existence of File
If FileExists(fullpath$) = False Then
'File Passed Does Not Exist - Log Error and Exit
Debug.Print fullpath$; " Does Not Exist!"
Exit Function
End If
'If the File Exists then open it
Workbooks.Open Filename:=fullpath$
'Return Workbook Name to Public Variable via Function
AutoLoadFile = ActiveWorkbook.Name
'File was chosen and loaded into Worksheet - now exit
Exit Function
FileOpenErr:
'In the event of an error execution jumps here
'Write Error to Log File
End
End Function
Notice that the function first checks to see if the file passed to the function really exists,
and thus will be able to be opened. If the file does exist, Excel makes an attempt to open it. If
Excel is successful at opening the file, the Workbook name is returned by the function. If Excel
cannot open the file, the function jumps to the FileOpenErr: location. It is after this label that
an appropriate error handling mechanism could be put into place, such as generating an error
log file.
The function is able to check if a file exists by means of the
function. This
FileExists
function makes use of the fact that the
function will return a zero length string (“”) if the path
Dir
specified in the function cannot be found.
Function FileExists(full_path As String) As Boolean
'Note: full_path must include suffix !!! (*.xls, *.txt, etc
. . .)
If Dir(full_path) <> "" Then
FileExists = True
'Debug.Print "File Exists"
Else
FileExists = False
