Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit open dialog to certain directory
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit open dialog to certain directory
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit open dialog to certain directory
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 brittonsm wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit open dialog to certain directory
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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit open dialog to certain directory
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. Jim Cone wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit open dialog to certain directory
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. Jim Cone wrote: 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. Jim Cone wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit open dialog to certain directory
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open in old directory save in new directory | Excel Programming | |||
Cancelling directory dialog box | Excel Programming | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
control of dialog macro dialog box. on open | Excel Programming | |||
Show Dialog of File/Directory | Excel Programming |