"FileDialogOpen" - Initial directory

[RobWN]

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?

 

[Dave Peterson]

(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

 

[RobWN]

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.

 

[Bob Phillips]

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)


--
---
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
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.

 

[Dave Peterson]

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).

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.

 

[Dave Peterson]

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

 

[RobWN]

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.
--
Regards
Rob

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)


--
---
HTH

Bob


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

 

[Dave Peterson]

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

 

[RobWN]

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.

 

[RobWN]

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.