I have the following code which gets all the files in a director. The problem is if a user hits cancel then it exits the subroutine but my subsequent code does not know that cancel was hit.
Any ideas on how to identify if cancel was hit...........
'***********************************************************************
'***********************************************************************
' Procedure obtained from LeithRoss at "Excel Forum"
' http://www.excelforum.com/excel-programming/776444-reading-in-the-
' files-in-a-directory-into-an-array-directory-chosen-by-user.html
'
'***********************************************************************
'***********************************************************************
Sub get_list_of_files_and_path(file_array() As Variant, Path1 As String)
Dim Cnt As Long
Dim ExcelFiles() As Variant
Dim FilePath As String
Dim FileName As String
Dim objShell As Object
Set objShell = CreateObject("Shell.Application")
Set oFolder = objShell.BrowseForFolder(0, "Pick a Folder to Open", 0)
If oFolder Is Nothing Then Exit Sub
FilePath = oFolder.Self.Path & "\"
'MsgBox (FilePath)
Path1 = FilePath
'FileName = Dir(FilePath & "*.xls")
FileName = Dir(FilePath & "*.*")
If FileName = "" Then
MsgBox "No Excel Files were Found in this Folder.", vbCritical
Exit Sub
End If
Cnt = 1
Do While FileName <> ""
ReDim Preserve ExcelFiles(Cnt)
ReDim Preserve file_array(Cnt)
ExcelFiles(Cnt) = FilePath & FileName
file_array(Cnt) = FileName
'MsgBox (FileName)
Cnt = Cnt + 1
FileName = Dir()
Loop
End Sub
Bookmarks