Closing msoFileDialogFilePicker

I use the following code to put a file path to an Excel file into an Access field.

The users then make changes to the Access data, and click a button to update the Excel file (which works fine).  They then want to move the file to another location on the network.

The problem seems to be that the following code leaves the file locked/in use, so they are unable to move the file without closing Access.  I'm sure it's this code, because it locks the file even if you don't run the update.

Private Sub cmdUpdateLink_Click()

Dim dlgPicker As FileDialog
Dim stFile As String
Dim stName As String
Dim bytLen As Byte
Dim bytPosition As Byte

Set dlgPicker = Application.FileDialog(msoFileDialogFilePicker)

With dlgPicker
    .Title = "Select Client Book"
End With

    stFile = dlgPicker.SelectedItems.Item(1)
bytLen = Len(stFile)

bytpos = InStr(stFile, " ")

stName = stFile

Do While InStr(stName, "\") > 0
    bytpos = InStr(stName, "\")
    stName = Right(stName, bytLen - bytpos)
    bytLen = Len(stName)

Me.fld15gLinktoClientBook = stName & "#" & stFile

Set dlgPicker = Nothing

Open in new window

Does the dialog box still have the file selected in the background?
How do I deselect the file?
Why doesn't Set dlgPicker = nothing help?
LJKMartinTraining DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The problem probably does not lie here because you are not reading the file

Could you post the part where you make the changes to the file
Robberbaron (robr)Commented:
not an access person, but
Me.fld15gLinktoClientBook = stName & "#" & stFile 

Open in new window

look link linking the file

probably need to remove the link when done.
Dale FyeOwner, Developing Solutions LLCCommented:
Agree with Rgonzo, the code you posted is not causing the problem.

The problem is with the code that you are using to edit Excel.  Excel is very finicky about the way you open and close objects.  Any object that you instantiate (XL, Wbk, Wsht, ...) must be set to nothing.  If you can post the code you are using to edit the Excel data, I'm sure we can provide a more detailed solution.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

LJKMartinTraining DirectorAuthor Commented:

Sorry not to have responded sooner.  The code doesn't leave the client site and I'm not back there until tomorrow (Wednesday)

In the other set of code I am definitely setting all my objects to nothing, and using Task Manager I can see that the instance of Excel closes.

I will check that there are no lines of code that don't use the Application, Workbook and Worksheet objects that I create.  I'll also check that their scope is correct because the code does call procedures from other modules.

The one object that I'm not sure about is the ActiveCell.  Which object is the correct one for that?

I am setting the objects by saying:

myXL = Application
myWkb =

That's generally correct, isn't it?

I'll also test again, my theory that it is the posted code that is the problem, and let you know how I get on.


LJKMartinTraining DirectorAuthor Commented:

It's definately this code (the problem still happens even if I go nowhere near the code that edits the excel file) but my assumption that the file was being locked was incorrect - it's the folder.

What the user wants to do is move the folder that contains the file and it won't let you.

I have tried creating another dialog box but even if I set the .initialfile to something else the dialog box is still opening where the selected file was.

So, it must be the access application's active folder that is the problem.  I tested this by manually giving the file open command and it took me to the folder specified in the dialog filepicker.

How do I change the application's focus away from that folder?
LJKMartinTraining DirectorAuthor Commented:
Well that ain't it either!

using Chdir doesn't help!

Any ideas please?
LJKMartinTraining DirectorAuthor Commented:

That was indeed the problem.  I had to close Access to clear the lock it had on the folder before running the new code.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LJKMartinTraining DirectorAuthor Commented:
It was hard to identify what was triggering the problem.  I neglected the question for a few days, and was able to solve the problem myself once I had correctly diagnosed it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.