[vba] Getting a filename?

[Steve Lefevre]

Hey folks -

Working with vba in Access 2k, I'm making a form to automatically import a
spreadsheet. Everything works great when I hardcode the name of the
spreadsheet file; however, I want the user to be able to select the file.

How to I give the user a "select file" dialogue box? All I need to get the
is drive:\path\filename string for the TransferSpreadsheet command.

Thanks!

Steve Lefevre

[tym]

On Thu, 4 Dec 2003 12:33:30 -0500, "Steve Lefevre"
<le********@osu.edu> wrote:

How to I give the user a "select file" dialogue box? All I need to get the
is drive:\path\filename string for the TransferSpreadsheet command.

Put the commonDialogue control on your form and use the .ShowOpen
method.
Tym

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
See http://www.ictis.net/no_spam.html for unsolicited email warning
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~

[Deano]

Steve Lefevre wrote:

Hey folks -

Working with vba in Access 2k, I'm making a form to automatically
import a spreadsheet. Everything works great when I hardcode the name
of the spreadsheet file; however, I want the user to be able to
select the file.

How to I give the user a "select file" dialogue box? All I need to
get the is drive:\path\filename string for the TransferSpreadsheet
command.

Thanks!

Steve Lefevre

Do you want to offer them a control that allows them to traverse the file
system? I decided against it given how bad the average user is with
Windows. Also it saved me the hassle of debugging that. I give them a
textbox and tell them to type it in. I save the path in a listbox from
which they can subsequently select.

[Trevor Best]

On Thu, 4 Dec 2003 12:33:30 -0500 in comp.databases.ms-access, "Steve
Lefevre" <le********@osu.edu> wrote:

Hey folks -

Working with vba in Access 2k, I'm making a form to automatically import a
spreadsheet. Everything works great when I hardcode the name of the
spreadsheet file; however, I want the user to be able to select the file.

How to I give the user a "select file" dialogue box? All I need to get the
is drive:\path\filename string for the TransferSpreadsheet command.

http://www.trevor.easynet.co.uk/down...penFileDlg.zip

--
A)bort, R)etry, I)nfluence with large hammer.

[Larry Linson]

"Deano" wrote

Do you want to offer them a control
that allows them to traverse the file
system? I decided against it given how
bad the average user is with
Windows.
Interesting that our experience in this area should differ so vastly. I've
never had users who had any trouble at all using the Windows Common Dialog,
just as in any other application.
Also it saved me the hassle of debugging that.
The code, freely downloadable, from
http://www.mvps.org/access/api/api0001.htm has been used sufficiently that
we know it works. The only debugging needed would be the code that calls it,
and it even includes an example of that. It uses the Windows Common Dialog
API directly, so no ActiveX, etc., has to be distributed.

I give them a textbox and tell them to
type it in. I save the path in a listbox from
which they can subsequently select.

I don't follow how this could be easier for an inexperienced user than
selecting from the Common Dialog; in fact, it seems they'd have to be more
knowledgeable to search for the path and file name, and more prone to
typographical errors entering it.

[Deano]

Larry Linson wrote:

"Deano" wrote

> Do you want to offer them a control
> that allows them to traverse the file
> system? I decided against it given how
> bad the average user is with
> Windows.
Interesting that our experience in this area should differ so vastly.
I've never had users who had any trouble at all using the Windows
Common Dialog, just as in any other application.

I've read one or two articles recently that have cast doubt on any notions
that Windows is actually a positive experience for the end user in terms of
it's basic functions and windowing concepts. i.e users don't really value,
for example, being able to resize and position windows. And that they don't
really find it easy to locate files and data. Heck, even I can't easily
find stuff most of the time. The interface to the filesystem is NOT
intuitive and I make this bold claim since I have had to spend many hours
talking to end users over the phone and visualise them navigating down
dead-ends. This is regardless of whether they were trying to open a file
within an app or just using Windows Explorer. A few people have been
totally amazed when I typed a URL in and a website appeared....("hmm, well
what do you think that court case was about then?").

> Also it saved me the hassle of debugging that.

The code, freely downloadable, from
http://www.mvps.org/access/api/api0001.htm has been used sufficiently
that we know it works. The only debugging needed would be the code
that calls it, and it even includes an example of that. It uses the
Windows Common Dialog API directly, so no ActiveX, etc., has to be
distributed.

Ah, this might make me a convert. Thanks.

> I give them a textbox and tell them to
> type it in. I save the path in a listbox from
> which they can subsequently select.

I don't follow how this could be easier for an inexperienced user than
selecting from the Common Dialog; in fact, it seems they'd have to be
more knowledgeable to search for the path and file name, and more
prone to typographical errors entering it.

That's just how it works for my app; it may indeed be better to have
something more sophisticated at other times.

[Steve Lefevre]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:ju****************@nwrddc01.gnilink.net...

The code, freely downloadable, from
http://www.mvps.org/access/api/api0001.htm has been used sufficiently that
we know it works. The only debugging needed would be the code that calls it, and it even includes an example of that. It uses the Windows Common Dialog
API directly, so no ActiveX, etc., has to be distributed.

Larry --

Thanks much for your time and expertise.

When I use the example, I get errors on ahtAddFilterItem and
ahtCommonFileOpenSave. Specifically "Compile Error: Sub or Function not
defined". Am I missing a reference or something?

This is the code I've included:

Dim strFilter As String
Dim 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)