File Path Browse on Export

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

For an "Export to Excel" command button, I'd like to let
my users browse to identify their chosen path, like
Windows applications handle it.

Is their a public domain or Jet function which would
accomplish this?

Thanks.
 
Kevin said:
For an "Export to Excel" command button, I'd like to let
my users browse to identify their chosen path, like
Windows applications handle it.

Is their a public domain or Jet function which would
accomplish this?

Thanks.
Click to expand...

You'll code to call the Windows File Open/Save dialog directly at:

www.mvps.org/access/api/api0001.htm

If you have Access 2002 (or, presumably, later) you can call the
Application.FileDialog method.
 
Thanks for the suggestion, Dirk. We are running Access
2002. I've added the needed library reference, and it
works. I will be supplying the filename to export to a
spreadsheet, the user will select the path.

How do I refer to this path once the user has chosen it?
If you have Access 2002 (or, presumably, later) you can call the
Application.FileDialog method.
Click to expand...
 
Kevin said:
Thanks for the suggestion, Dirk. We are running Access
2002. I've added the needed library reference, and it
works. I will be supplying the filename to export to a
spreadsheet, the user will select the path.

How do I refer to this path once the user has chosen it?
[I had written:]
If you have Access 2002 (or, presumably, later) you can call the
Application.FileDialog method.
Click to expand...
Click to expand...

I've never used that new feature myself -- developing for multiple
Access versions, I've stuck with the API call. But as far as I can tell
from the help, the FileDialog object has a SelectedItems property that
returns a collection of selected items (folders or files). Because the
help isn't that easy to find, I'll post the gist of the example from the
help entry:

'------------ quote from help file -------------
'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box and
return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems
collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of
each selected item.
'You can use any file I/O functions that you want to
work with this path.
'This example simply displays the path in a message box.
MsgBox "The path is: " & vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing
'------------ end quote from help file -------------

Does that help?
 
Kevin said:
I got it to work; thanks, Dirk. Since I used a "Folder
Picker", it seemed to me the .SelectedItems collection
should have only one member. I tried referring to it by

Dim dlgPath As FileDialog
Dim varItem as Variant
Dim fname as String

... show method...

fname = <filename>
fname = dlgPath.SelectedItems(0)

This wouldn't work, but a for loop similar to the example
which executes once did.

Unfortunately, I realized with your message that I'm stuck
with multiple versions also. Would you mind posting an
API example?

Best regards.

Kevin Sprinkel
Click to expand...

Complete code and examples of calling it are available at:

www.mvps.org/access/api/api0001.htm (for the File Open/Save dialog)

and

www.mvps.org/access/api/api0002.htm (for the Browse Folder dialog)

I've used these to good effect, occasionally making minor adaptations
for my specific purposes.
 
Complete code and examples of calling it are available at:
www.mvps.org/access/api/api0001.htm (for the File
Click to expand...
Open/Save dialog)

I've installed the code in a library, and it works. The
new FileDialog method, however, is "sticky"--the dialog
opens up in the last selected folder--while the API
routine starts at the top of the hierarchy each time.

I'd like to run the new method if the user is running
2002. I haven't been able to find how to access the
version programmatically. Have you done this with your
diverse user group?

Thanks. Best regards.
 
Kevin said:
I've installed the code in a library, and it works. The
new FileDialog method, however, is "sticky"--the dialog
opens up in the last selected folder--while the API
routine starts at the top of the hierarchy each time.

I'd like to run the new method if the user is running
2002. I haven't been able to find how to access the
version programmatically. Have you done this with your
diverse user group?

Thanks. Best regards.
Click to expand...

The Open/Save dialog will normally open in the "current" directory, and
unless told otherwise (as the code provided does by specifying the
option ahtOFN_NOCHANGEDIR) will change the current directory to the one
containing the selected file. So by leaving out that option you can
make the Open/Save dialog "sticky".

I may be wrong, but I think the BrowseFolder dialog can only be made
"sticky" by specifying a callback function, which I seem to recall an
example for doing on one of the MVP VB pages; maybe VBNet (
http://www.mvps.org/vbnet/ ) ... ah, yes:

http://www.mvps.org/vbnet/code/callback/browsecallbackoverview.htm

and

http://www.mvps.org/vbnet/code/callback/browsecallback.htm

So that may be an option if you want to keep your code
version-nonspecific.

You can tell which version of Access you're running by checking the
value returned by SysCmd(acSysCmdAccessVer). Access 2002 is version
"10.0", 2000 is version "9.0", and 97 is "8.0". Note that the value
returned is a string representation of a number, so if you want to
compare values you have to convert them to integers first.

However, I don't think knowing this is going to help you much in
deciding at run time whether or not to call Application.FileDialog,
because you'll have compilation and reference errors if you're running
the wrong Access version. What I do is have separate versions of a
module named basVersionDependent, and put all version-dependent code in
there. All the public functions defined in this module have the same
names, but the implementation is different depending on which version of
Access is being targeted. That way, I just swap in the appropriate
module when building the application for a specific Access version. The
Access-97 version of this module also contains my own implementation of
the VBA functions that were introduced in Access 2000, so that I can use
them in all versions of may application. The A2K version of this module
doesn't include my user-defined implementations, so the builtin
functions get called.
 
What I do is have separate versions of a
module named basVersionDependent, and put all version-
Click to expand...
dependent code in there.
module when building the application for a specific
Click to expand...
Access version.
Dirk Goldgar, MS Access MVP
www.datagnostics.com
Click to expand...

Thanks for the references and tips; I think this strategy
makes a lot of sense. But what did you mean by "swap in"
the appropriate module?

I have all generic code in a database, and included it in
the References for this application. It sounded, though,
like you were referring to a specific module within a
library. How do you do that? Or is that what you mean?
 
Kevin said:
dependent code in there.



Thanks for the references and tips; I think this strategy
makes a lot of sense. But what did you mean by "swap in"
the appropriate module?

I have all generic code in a database, and included it in
the References for this application. It sounded, though,
like you were referring to a specific module within a
library. How do you do that? Or is that what you mean?
Click to expand...

As it happens, I haven't yet felt it necessary to separate commonly-used
code into a library database or add-in, though I agree that it's a good
technique. So my applications have kept all their code modules
internally. What I've done is just have separate databases named
"VersionFunctions97.mdb" (in A97 format) and "VersionFunctions2K.mdb",
with each database containing a single module named
"basVersionFunctions". Then when I build the Access-97 version of an
application, I import the module from VersionFunctions97.mdb, and when I
build the A2K+ version, I import the module from VersionFunctions2K.mdb
(I haven't yet had a need to develop a separate version for A2K2). No,
it's not really a structured, modular approach, but it's been good
enough to serve my needs so far.

Actually, I rather like the idea of using these databases as add-ins,
and not importing the modules at all. Then all I'd have to do is make
sure the appropriate "version" database is copied to the application
folder, under the name "VersionFunctions.mda", and reference that as an
add-in. That's tidier than what I'm doing now, but I've never gotten
around to it.
 
That's tidier than what I'm doing now, but I've never
gotten around to it.

Cool; thanks again, Dirk.

BTW, it seems to me, though, that "tidy" might be
secondary to "consistent", "maintainable", "functional",
and "inspired"!
 
Back
Top