"FileDialogOpen" - Initial directory

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RobWN - 23 Jan 2008 02:16 GMT

xl03

I'm using "Application.FileDialog(msoFileDialogOpen)" to present a list of files to the user so
he/she can select a wb to load.

My question is, can I somehow force the dialog to open with a specific directory?

Signature

Regards
Rob

Reply to this Message

Dave Peterson - 23 Jan 2008 02:25 GMT

(Most of this was stolen from the help for .filedialog)

Option Explicit
Sub testme()

Dim lngCount As Long
Dim curFolder As String
Dim newFolder As String

curFolder = CurDir
newFolder = "C:\test"

ChDrive newFolder
ChDir newFolder

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

ChDrive curFolder
ChDir curFolder

End Sub

> xl03
>
[quoted text clipped - 6 lines]
> Regards
> Rob

Signature

Dave Peterson

Reply to this Message

RobWN - 23 Jan 2008 03:33 GMT

Thanks Dave, but no go.
I have the logic you have in your reply but it refuses to work (to my requirements, at least).

Given the situation where the user changes directories in the dialog then cancels the
transaction;
The next time the transaction (in the same session) is called it defaults to the last directory
used (the one changed to and cancelled)

FWIW, here's my logic
All variables DIM'd as string
I've verified that the "InDir" has the desired target directory
The logic performs as it should with the exception noted above.

CurFolder = CurDir
NewFolder = Left(InDir, Len(InDir) - 1) 'Tried stripping the "\" from the directory name-
no change

ChDrive NewFolder
ChDir NewFolder

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show

If .SelectedItems.Count = 0 Then GoTo ExitGetRec 'User bailed out

InRec = .SelectedItems(1)
DLM = InStr(UCase$(InRec), "\ACC309-R")
RecBk = Right(InRec, Len(InRec) - DLM)
Workbooks.Open InRec
Sheets("Rec").Copy After:=ThisWorkbook.Sheets("Sys")
Workbooks(RecBk).Close SaveChanges:=False
End With

ExitGetRec:

ChDrive CurFolder
ChDir CurFolder

End Sub

I realize that the user can "steer" to the proper directory but I'd prefer that they didn't have
to(!).

This is my first attempt using the dialog, prior to this (xl2k) I built a menu with the required
files but thought this would be easier!

Again, thanks.

Signature

Regards
Rob

> (Most of this was stolen from the help for .filedialog)
>
[quoted text clipped - 39 lines]
>> Regards
>> Rob

Reply to this Message

Bob Phillips - 23 Jan 2008 08:49 GMT

I am not seeing that problem, it opens up every time.

One suggestion, on testing for \, use

NewFolder = IIf(Right(InDir, 1) = "\", Left(InDir, Len(InDir) - 1), InDir)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Thanks Dave, but no go.
> I have the logic you have in your reply but it refuses to work (to my
[quoted text clipped - 89 lines]
>>> Regards
>>> Rob

Reply to this Message

RobWN - 24 Jan 2008 00:15 GMT

Thanks Bob;

Even if you navigate to a different directory then cancel and try it again?
I keep getting the last folder (i.e. the one that I navigated to before cancelling-I got a
feeling I'm missing the obvious!

In the end, my problem turns out to be a larger problem given that I can't use it on a UNC
name-which I have to because the users have the network drive mapped to a different letter.
ChDrive uses the 1st character of a multi chr string - been a long time since I used it.

Thanks for the NewFolder test but I was aware of that-I just threw the logic in to test whether
or not the separator made a difference (it didn't).

Oh well, back to my tried/true method - more complex but it works.

Signature

Regards
Rob

>I am not seeing that problem, it opens up every time.
>
[quoted text clipped - 94 lines]
>>>> Regards
>>>> Rob

Reply to this Message

Dave Peterson - 24 Jan 2008 00:20 GMT

You can use an API call, but I'm repeating myself.

> Thanks Bob;
>
[quoted text clipped - 126 lines]
> >>>
> >>> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 23 Jan 2008 12:08 GMT

Could it be that chdrive won't work on a UNC drive (it will on a mapped drive,
though).

There's an API that you could use instead.

> Thanks Dave, but no go.
> I have the logic you have in your reply but it refuses to work (to my requirements, at least).
[quoted text clipped - 94 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 23 Jan 2008 15:01 GMT

And here's an example of that API call. It uses application.getopenfilename,
but you'll see how to use it.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme01()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim Wkbk as workbook

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number <> 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDirNet myCurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

'do your stuff to open it and process it.
Set wkbk = workbooks.open(filename:=myfilename)

'....

End Sub

> Could it be that chdrive won't work on a UNC drive (it will on a mapped drive,
> though).
[quoted text clipped - 103 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

RobWN - 24 Jan 2008 00:24 GMT

You're right - found that out today - the hard way ("Doh")!
Our users have the network drive mapped differently on their machines so I'm dead in the water,
even if I could get it to work.

I just noticed that you've posted an API solution - I'll look at it when I get a chance and
decide whether to use it or stick with my existing method of creating a userform populated with
the available files.

Thanks for the help.

Signature

Regards
Rob

> Could it be that chdrive won't work on a UNC drive (it will on a mapped drive,
> though).
[quoted text clipped - 105 lines]
>> >
>> > Dave Peterson

Reply to this Message

RobWN - 25 Jan 2008 00:28 GMT

Well, thanks to Google and my stumbling into the proper Help section I found the answer.

Dim FD As FileDialog
Application.FileDialog(msoFileDialogOpen).InitialFileName = InDir & "Whatever-*.xls"
Set FD = Application.FileDialog(msoFileDialogOpen)

With FD
.AllowMultiSelect = False
.Show
etc, etc,

I'm somewhat embarrassed that it was right under my nose but I don't find the help function very
intuitive.

Thanks to both Bob & Dave for their interest/suggestions.

Signature

Regards
Rob

> xl03
>
> I'm using "Application.FileDialog(msoFileDialogOpen)" to present a list of files to the user
> so he/she can select a wb to load.
>
> My question is, can I somehow force the dialog to open with a specific directory?

Reply to this Message