View Full Version : Loop until found / user cancels
theta
05-16-2012, 04:11 AM
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
05-16-2012, 06:39 AM
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
05-16-2012, 07:12 AM
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
05-16-2012, 08:43 AM
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
05-16-2012, 10:29 AM
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
05-16-2012, 10:30 AM
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
05-17-2012, 02:08 AM
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
05-17-2012, 02:42 AM
AFAIAA, you cannot click OK if you haven't selected a ddirectory.
Error where?
theta
05-17-2012, 02:46 AM
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
05-17-2012, 02:48 AM
You could post the workbook, we are working in the dark otherwise.
theta
05-17-2012, 02:59 AM
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
05-17-2012, 03:23 AM
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
05-17-2012, 03:54 AM
Add a global boolean variable, and set/unset that as appropriate, and check as required.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.