FileDialog browse button: how do you specify the default directory

[Guest]

Code------------------->
Private Sub btnBrowse_Click()

' This requires a reference to the Microsoft Office 11.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim fs As Office.FileSearch
Dim cboFolder As String

' Clear the list box contents.
Me.FileList.RowSource = ""

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
Set fs = Application.FileSearch
With fs
.LookIn = "S:\TracerNotes_OBR\OBR_RESOURCES_DB_PROJECT\"
End With
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = False



' Set the title of the dialog box.
.Title = "Select One or More Files"



' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Excel Spreadsheets", "*.XLS"
.Filters.Add "All Files", "*.*"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the
list box.
For Each varFile In .SelectedItems
Me.FileList.AddItem varFile
Next
Else
'MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub
Code-------------------->

this is the code I found on the microsoft website for making a browse button
work in Access 2003. What I would like to know is what code do you use to
select the default directory that the open window appears in? As you can see,
I tried the .lookin statement but it said that that isn't in the fdialog
"scope"

tia.

 

[Allen Browne]

THe FileDialog object is worse than useless. Its problems include:
- Breaks the entire application as you switch version (references issue).
- Does not work in runtime apps.
- The SaveAs option is offered, but does not work at all.

A better solution is to use the API calls in this link:
http://www.mvps.org/access/api/api0001.htm
You can specify the initial directory.

 

[Amy Blankenship]

the problem is that you've got nested withs, and you have it inside the fs
with rather than the fDialog with. Not sure what you're even using the fs
for.

 

[Amy Blankenship]

THe FileDialog object is worse than useless. Its problems include:
- Breaks the entire application as you switch version (references issue).

I think that's just a problem with the constants. Not real hard to replace
the constants with actual numbers if it gives you problems.

- Does not work in runtime apps.

Isn't that throwing the baby out with the bath water?

- The SaveAs option is offered, but does not work at all.

Does that mean that it doesn't return to you what name the user chose to
save something under? If not, it's just a dialogue, not a saving
application...you have to work out the saving yourself, just like when you
use the dialogue to pick a file or folder.

A better solution is to use the API calls in this link:
http://www.mvps.org/access/api/api0001.htm

That's really bloated and confusing code. Not surprising many people choose
to avoid it.

Might I suggest something more like this...?

Sub GetFile(Control)
Dim retFile As String, dlg As Variant, s As String
Set dlg = Application.FileDialog(msoFileDialogFilePicker) 'could use 3
instead of the constant
With dlg
.InitialFileName = CodeProject.Path
If .Show = -1 Then s = .SelectedItems(1)
End With
If s <> "" Then
retFile = Right(s, Len(s) - InStrRev(s, "\"))
Control.Value = retFile
End If
End Sub

 

[Guest]

The problem is I tried using that and it gives me and error with the aht
commands like "ahtAddFilterItem". It keeps giving me an error when I try and
use it. So if it's a better solution, explain how it works

 

[Guest]

I was thinking that the .lookin command could be used but not if you had to
do it inside the fdialog. I have the code working, I was just wondering if
there's a command that I'm missing that could be used so when the browse
button is pressed, it opens the open file window in the stated directory. I'm
just learning this stuff and I have the code worked out other than that and
I'm not trying to redo all of the code if there's just something I can add to
set the default directory.

 

[Allen Browne]

The API code can look frightening if you are not used to it, but all you
have to do is to paste it all into a standard module, i.e. one created
through the modules tab of the database window (not into the module of one
form.)

ahtAddFilterItem is a function, right at the end of the code. Provided this
is in a standard module, it is called by the code itself as needed.

For an example of what you need to do to get this working, see the TestIt()
function in that link.

For a detailed explanation of how this works (and lots more stuff), you
could buy the Microsoft Access xxxx Developers Handbook, by Ken Getz et al,
published by Sybex. (xxxx is a number representing a version of Access.)

For more generic info on how to make API calls from Visual Basic, see:
http://vbnet.mvps.org/api/_api/index.html

HTH.

 

[Allen Browne]

Amy, if you can explain how to use the SaveAs option of the FileDialog, I
would be interested to know how to get it working.

 

[Rob Oldfield]

For reference, the code from the page that Allen links to works perfectly.
It works exactly as stated. You've done something wrong in implementing it.
It would therefore be useful if you said what the error was, rather than
just hoping people can figure it out.

 

[Guest]

When I put this into a module:

Public strFilter As String
Public strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True,
DialogTitle:="Please select an input file...", Flags:=ahtOFN_HIDEREADONLY)


it gives me this error: Compile Error: Invalid outside procedure. It then
points to the strFilter portion of the code.

 

[Allen Browne]

Those last 3 lines need to go into a Sub or a Function.

You can use the TestIt() as an example of how to make a procedure.

 

[Rohan via AccessMonster.com]

That's really bloated and confusing code. Not surprising many people choose
to avoid it.

Might I suggest something more like this...?

Sub GetFile(Control)
Dim retFile As String, dlg As Variant, s As String
Set dlg = Application.FileDialog(msoFileDialogFilePicker) 'could use 3
instead of the constant
With dlg
.InitialFileName = CodeProject.Path
If .Show = -1 Then s = .SelectedItems(1)
End With
If s <> "" Then
retFile = Right(s, Len(s) - InStrRev(s, "\"))
Control.Value = retFile
End If
End Sub

Can you give an example of calling this code - I tired it and get an object
error.

Rohan.

 

[Amy Blankenship]

GetFile Me![RTFFile]

HTH;

Amy

Can you give an example of calling this code - I tired it and get an
object
error.

Rohan.

 

[Amy Blankenship]

Haven't tried it. But I imagine you'd use the path the user entered
(possibly stored in Item or SelectedItems) as the destination in a
FileSystem.SaveAs.

If I wind up having a compelling need to produce such code for my own
reasons, I'll be sure and post it, but that's the direction I'd move in if I
needed to do this.

HTH;

Amy

 

[Amy Blankenship]

Look at .initialFileName.

HTH;

Amy

I was thinking that the .lookin command could be used but not if you had to
do it inside the fdialog. I have the code working, I was just wondering if
there's a command that I'm missing that could be used so when the browse
button is pressed, it opens the open file window in the stated directory.
I'm
just learning this stuff and I have the code worked out other than that
and
I'm not trying to redo all of the code if there's just something I can add
to
set the default directory.

 

[Amy Blankenship]

The problem is that the code you've copied is way more complex than it needs
to be, and jumps out to different blocks of code that are probably separate
blocks because in the application the code writer originally wrote them for
they were hopefully used by other procedures within the application. Either
that or the coder was either showing off on how "object oriented and
modular" the code was or was deliberately trying to make it hard for people
to decipher. Seems odd not making sample example code as simple as
possible, but there you have it.

Hope this clarifies;

Amy

 

[Allen Browne]

Guess I'll have to let you go your own direction, Amy.

If you do get it working in Access, please tell Microsoft how to do it as
well:
http://support.microsoft.com/default.aspx?scid=kb;en-us;282335

 

[Stephen Lebans]

It's not like you didn't try as politely as you could Allen.
;-)

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Amy Blankenship]

So just use 2. Duh...

-Amy

Guess I'll have to let you go your own direction, Amy.

If you do get it working in Access, please tell Microsoft how to do it as
well:
http://support.microsoft.com/default.aspx?scid=kb;en-us;282335

 

[Allen Browne]

Plonk.