closing filedialogue box

[Ronaldo]

I am using the code below to open up a file dialogue box, to allow user's to locate and select a single file. The VBA code then goes on top perform a fair amout of processing. My issue is that the dialogue box remains on screen. I want to close it immediately. Any idea's


Dim fd As FileDialog

Dim ffs As FileDialogFilters

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd

Set ffs = .Filters 'clear filters

With ffs

.Clear

.Add "Files", "*.*"

End With

.AllowMultiSelect = False

If .Show = False Then Exit Sub

Infile = .SelectedItems(1)

End With

 

[Hui]

Ronaldo

When I run your code in XL 2007 the dialogue closes correctly and returns the file name correctly

 

[Ronaldo]

Hello Hui,

It does everything OK except close (immediately). After the user selects a file, the code goes on to do a lot of laborious time-consuming processing, during which the selection box stays plumb in the middle of the screen. What I want to do is to display some processing progress counters, but they don't display until its finished (and the filedialog screen disappears)

 

[Ronaldo]

the problem doesn't seem to be with the filedialog box. I by-passed this by hardcoding the input filename to be used. Still, the field in the form that I update each loop (reacord read) of the code, fails to update until it finishes. I thought I could get the form field to scroll through numbers from 1 upwards ??

 

[Hui]

Ronaldo


I tried the following in 2003, 2007 & 2010 and the dialogue has gone in all versions by the time the Msgbox executes ?


Sub h()


Dim fd As FileDialog

Dim ffs As FileDialogFilters

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd

Set ffs = .Filters 'clear filters

With ffs

.Clear

.Add "Files", "*.*"

End With

.AllowMultiSelect = False

If .Show = False Then Exit Sub

Infile = .SelectedItems(1)

End With


MsgBox Infile


End Sub

 

[Ronaldo]

Ah. Now that makes a difference in that it also serves to update the field I have on my form. However, I don't want a msgbox popping up all the while.


Please allow me to re-state the problem knowing what I now know. I don't believe the filedialog box has anything to do with it.


I have a Form that has a button to invoke a procedure that reads and processes records from an external tab delimited file. Also on the form, I have a "text box" control with the name "RecsRead".


When the procedure is executed, and the records are read from the file (in a loop) when each record is read I have the statement . . .


RecsRead.Value = iRec


where iRec is the loop counter. The text box (on the form) does not update until the process has completely finished - rather than after each record is read - which is what I want ??


It's as though VBA is so intent on running the code, it can't be bothered to update the form ?


Any help on this gratefully received

 

[Hui]

to update the text box (on the form) put a line after RecsRead.Value = iRec


RecsRead.Value = iRec

Textbox.Value = iRec 'change the textbox name to match the text boxes name


You may also want to check that the original code you have shown is not inside any loops

 

[Ronaldo]

Sorry Hui, the text box name is "RecsRead" ?


The code pasted is inside a loop. If I put a msgbox (as you suggested earlier) it updates fine. Just doesn't do it otherwise.

 

[Hui]

Ronaldo

I wasn't suggesting that you put a Msgbox line in.

My code shows that your code is working correctly and not the cause of the dialogue staying on screen

If you are only loading 1 file, you probably don't need the entire file loading section in a loop. It looks like what ever you are doing in the loop is not exiting the loop properly but returning back to reload a file

 

[Ronaldo]

No. I know you wasn't, but it did help as it suggested that the processor was in fact too busy to post my variable to the form. I have it sorted now by inserting "DoEvents" as below


RecsRead.Value = iRec

DoEvents


many thanks for your help

 

[Hui]

great news