Limit open dialog to certain directory

[brittonsm]

I'm trying to understand how to use the open windows dialog command to
open a certain directory - BUT limit the user to only selecting files
to open out of that directory. Possible?

 

[Jim Cone]

Sub GoodEnough()
Dim sGood As String
Dim sFile As String
Dim vSelect As Variant
Dim bVerify As Boolean
sGood = "C:\Documents and Settings\user\My Documents\Excel Files\"

vSelect = Application.GetOpenFilename
If vSelect = False Then Exit Sub
sFile = Dir(vSelect)
sGood = sGood & sFile
bVerify = Len(Dir(sGood))
MsgBox bVerify
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"brittonsm"
wrote in message
I'm trying to understand how to use the open windows dialog command to
open a certain directory - BUT limit the user to only selecting files
to open out of that directory. Possible?

 

[Dave Peterson]

Another one:

Option Explicit
Sub testme()
Dim myPath As String
Dim myFileName As Variant
Dim myFilePath As String
Dim CurPath As String
Dim wkbk As Workbook

'make sure it exists
myPath = "C:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

'save the existing current directory
CurPath = CurDir

'change to the one you want
ChDrive myPath
ChDir myPath

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

'change back to the old directory
ChDrive CurPath
ChDir CurPath

If myFileName = False Then
Exit Sub
End If

'strip off the filename, keep the drive/path
myFilePath = Left(myFileName, InStrRev(myFileName, "\"))

If LCase(myFilePath) = LCase(myPath) Then
'it's ok, do nothing
Else
MsgBox "That file is not in the correct folder"
Exit Sub
End If

'do what you want
Set wkbk = Workbooks.Open(Filename:=myFileName)

End Sub

 

[Dave Peterson]

This may give unintended results.

If there's a file named book1.xls in the sGood folder and I choose book1.xls in
a different folder, then bVerify will be true.

And if the developer opens sgood & sfile, then the program wouldn't be opening
the file that the user actually chose. And if the developer opens vSelect, it
wouldn't be in the correct folder.

 

[Jim Cone]

Hi Dave,
It will depend on what "brittonsm" wants. My design was intentional...
only allow files to be opened that are in the correct folder.
If the user is told the file is in the wrong folder, he could "correct" the
matter by moving the file to the other folder.
"brittonsm will probably change it all anyway. <g>
Jim Cone



"Dave Peterson"
wrote in message
This may give unintended results.
If there's a file named book1.xls in the sGood folder and I choose book1.xls in
a different folder, then bVerify will be true.

And if the developer opens sgood & sfile, then the program wouldn't be opening
the file that the user actually chose. And if the developer opens vSelect, it
wouldn't be in the correct folder.

 

[Dave Peterson]

But depending on what the OP opens, it could be the wrong one.

The wrong one in the sense that it isn't in the correct folder.

Or the wrong one in the sense that it's not the one the user pointed to.

I really want to see the end of your code where you'd specify the workbooks.open
line <vvbg>.

 

[brittonsm]

What I intending on doing is have custom RFQs (Request for Quotes)
that I've created in word and have certain bookmarks in them that I
can reference to put info from the excel sheet into this word file.

Now if the user directed to there local drive and attempted to open a
custom RFQ they've created it may not have the correct bookmarks. So
what I want is to open the directory of "approved" custom RFQs and let
them pick one. If it doesn't exist well they have to submit a request
to have another made to the steering committee.

Make sense? I'll fiddle with your code on Monday an see how it goes.
Thanks for the replies.