Loop until found / user cancels

[theta]

Hi all...I am looking for the most efficient way to achieve a loop (Do, While, Loop Until ?)

If the macro cannot find a file it prompts the user to select a folder to use. The code should then check and then inform the user if the file is now found - if not repeat unless the user clicks cancel. So these are the steps



Check file exists
File found continue with rest of code
File not found then prompt user
User prompt generated "Please select file folder"
Click ok - show FileOpen dialog to select a folder
Nothing selected and dialog closed then - continue with rest of code and debug print that no file selected
Something selected then Loop back to Check file exists
Click cancel - continue with rest of code and debug print that cancel clicked

[Bob Phillips]

Sub GetFolder()
Const LOOKUP_FILE As String = "Trash01.xlsx"
Dim fldr As String
Dim filename As String
Dim done As Boolean

Do

With Application.FileDialog(msoFileDialogFolderPicker)

If .Show = -1 Then

fldr = .SelectedItems(1)
filename = Dir(fldr & Application.PathSeparator & LOOKUP_FILE)
If filename <> "" Then

done = True
End If
Else

done = True
End If
End With
Loop Until done
End Sub

[theta]

Thanks xld

I have been researching loops (if you have any good resources please share) at the following links :

http://excelvbamacro.com/excel-vba-looping-tutorial.html
http://www.ozgrid.com/VBA/loops.htm

You definitely filled in the blanks regarding the dialog box controls etc.

[theta]

Sub GetFolder()
Const LOOKUP_FILE As String = "Trash01.xlsx"
Dim fldr As String
Dim filename As String
Dim done As Boolean

Do

With Application.FileDialog(msoFileDialogFolderPicker)

If .Show = -1 Then

fldr = .SelectedItems(1)
filename = Dir(fldr & Application.PathSeparator & LOOKUP_FILE)
If filename <> "" Then

done = True
End If
Else

done = True
End If
End With
Loop Until done
End Sub

Can you please explain what If .Show = -1 represents - closing a dialog, taking it out of focus, closing etc

I would like to be able to know if they :

> Selected a file
> Closed it

Many thanks

[Bob Phillips]

The .Show launches the dialog, the value tests what you did, -1 (True in VBA) means you selected a file/folder. If not -1, you have cancelled.

[Bob Phillips]

BTW, this selects a folder and checks if a file exists in that folder, it doesn't open or close any file. That is how I read your requirement.

[theta]

BTW, this selects a folder and checks if a file exists in that folder, it doesn't open or close any file. That is how I read your requirement.

Thinking of using the GetFolder method - so even if they don't make a selection, the directory the dialog is currently showing will be chosen? What if they click ok on the dialog with no selection with the current code?

I have adjusted the code but get an error when i run it saying object does not support this method :



Dim bFound As Boolean
Dim stDefaultFile As String
Dim stFolder As String
Dim stFilename As String




stPayingProject = Workbooks(MAIN_WB).Sheets(stSheet).Range(stColumn & "100").Value

bFound = False
stDefaultFile = ReadRegistry("MAININPUT", "Country\" & ThisCountry) & "\" & stPayingProject & ".xxx"




bFound = FileExists(stDefaultFile)

If bFound Then stFilename = stDefaultFile

Do While bFound = False
With Application.FileDialog(msoFileDialogFolderPicker)
'User selects a folder
If .Show = -1 Then
stFolder = .SelectedItems(1)
stFilename = Dir(stFolder) & Application.PathSeperator & stPayingProject & ".xxx"

If FileExists(stFilename) Then
bFound = True
Else
bFound = False
MsgBox "Could not locate " & stPayingProject & ".xxx" & " in this location"
End If
'User does not select a folder
Else
bFound = False
Set stDefaultFile = Nothing
Exit Do
End If
End With
Loop

'Check File Exists else ERROR report
If FileExists(stFilename) Then go do something....

[Bob Phillips]

AFAIAA, you cannot click OK if you haven't selected a ddirectory.

Error where?

[theta]

Doesn't give me debug highight. Just says error. This is function, called by a function, called by a function, called by clicking a ribbon icon :/

When the dialog box appears. If I am in an empty directory, i can click ok without selectin a folder (as there are no folders to select)

I get error 438 : Object doesn't support this property of method

Might be to do with the Dir ? I am lost on this one. Trying to get the Function GetFolder from MrExcel forum but google is throwing up malware warnings and the site is down at present

[Bob Phillips]

You could post the workbook, we are working in the dark otherwise.

[theta]

Would love to but I cannot - it is part of a massive suite of workbooks. It will not function on it's own, it is loaded as a addin.

Going to keep experimenting with debug.print but don't know why it doesn't highlight the object giving me the error!

[theta]

Ok i found a fix. There is a form that loads. I need to know of the user clicks cancel on this form (without programming the _Click sub)

There is a form called frmSelectDir and a button called cbCancel. The button is already programmed so that cbCancel_Click performs frmSelectDir.Hide

I propose this :




Load frmSelectDir
frmSelectDir.Load

If form cancel clicked then
Do this
Else
Do this
End if


How can I detect what the user did? If frmSelectDir.Hide = True Then ?

[Bob Phillips]

Add a global boolean variable, and set/unset that as appropriate, and check as required.