[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\fil ename string for the TransferSpreads heet command.

Thanks!

Steve Lefevre

[tym]

Re: [vba] Getting a filename?

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

[color=blue]
>How to I give the user a "select file" dialogue box? All I need to get the
>is drive:\path\fil ename string for the TransferSpreads heet command.[/color]

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]

Re: [vba] Getting a filename?

Steve Lefevre wrote:[color=blue]
> 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\fil ename string for the TransferSpreads heet
> command.
>
> Thanks!
>
> Steve Lefevre[/color]

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]

Re: [vba] Getting a filename?

On Thu, 4 Dec 2003 12:33:30 -0500 in comp.databases. ms-access, "Steve
Lefevre" <lefevre.10@osu .edu> wrote:
[color=blue]
>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\fil ename string for the TransferSpreads heet command.[/color]

http://www.trevor.easynet.co.uk/downloads/zsbasOpenFileDlg.zip

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

[Larry Linson]

Re: [vba] Getting a filename?

"Deano" wrote
[color=blue]
> 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.[/color]

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.
[color=blue]
> Also it saved me the hassle of debugging that.[/color]

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.

[color=blue]
> 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.[/color]

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]

Re: [vba] Getting a filename?

Larry Linson wrote:[color=blue]
> "Deano" wrote
>[color=green]
> > 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.[/color]
>
> 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.[/color]

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....("h mm, well
what do you think that court case was about then?").

[color=blue][color=green]
> > Also it saved me the hassle of debugging that.[/color]
>
> 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.[/color]

Ah, this might make me a convert. Thanks.
[color=blue]
>
>[color=green]
> > 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.[/color]
>
> 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.[/color]

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

[Steve Lefevre]

Re: [vba] Getting a filename?


"Larry Linson" <bouncer@localh ost.not> wrote in message
news:juyAb.1285 $nz.1267@nwrddc 01.gnilink.net. ..[color=blue]
>
> 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[/color]
it,[color=blue]
> and it even includes an example of that. It uses the Windows Common Dialog
> API directly, so no ActiveX, etc., has to be distributed.[/color]


Larry --

Thanks much for your time and expertise.

When I use the example, I get errors on ahtAddFilterIte m and
ahtCommonFileOp enSave. 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 strInputFileNam e as string

strFilter = ahtAddFilterIte m(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileNam e = ahtCommonFileOp enSave( _
Filter:=strFilt er, OpenFile:=True, _
DialogTitle:="P lease select an input file...", _
Flags:=ahtOFN_H IDEREADONLY)