Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Windows 7 Explorer A2010

21 views
Skip to first unread message

Phil

unread,
Dec 13, 2009, 4:50:52 AM12/13/09
to
I am convertin from AK2 to A2010.
VBA routines which opened Windows explorer to find files or folders no longer
work (unsurprisingly) Can anyone help with a function to return the name of a
Library, Folder or file please I suspect it will look a bit like this
Public Function ExploreFor(WhatToLookFor As Integer, Optional WhereToLook As
String, WhatToLookFor As String) As String So WhatToLookFor say 1 for
Library, 2 for Folder, 3 for File E.G. MyFile = ExploreFor(3, "C:\My
Documents", "Mdb")

Thanks for any help

Rich P

unread,
Dec 14, 2009, 5:26:06 PM12/14/09
to
What is the code for the function body? Does this function just check
file extensions? Like if a given folder contains 20 files of which 3 of
the are .dll, 2 are .mdb, 6 are .xls, 4 are .pdf, and 5 are .txt

does this function return a count of file types contained in the
selected folder? It sounds like all you need is basic File I/O routines
with a little bit of string manipulation. Need to see the VBA code of
the function's body to be more specific


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Phil

unread,
Dec 14, 2009, 6:12:57 PM12/14/09
to

Thanks Rich for coming back.

That's the problem, I haven't got any code.

In error I suugested I wanted the name of the file. I meant of course the
path.

I'm basically wanting a routine that will open Windows Explorer. The user
then points to a Library, Folder or File and the Function returns the full
path of what has been selected.

The initial line of the air code was just a thought about "prompting"
Explorer start to look in the most likely place and, if we are looking for
files, possible filter for certain types of files eg *.mdb or m*.doc

Phil

Rich P

unread,
Dec 14, 2009, 6:47:06 PM12/14/09
to
Hi Phil,

I only have Access2003 and I played around with the builtin FileDialog
but was not able to retrieve filenames with this (unlike Excel where the
builtin FileOpenDialog works very nicely). I expect this has been
fixed/improved in Acc2010. Try


With Application.FileDialog(msoFileDialogOpen)
.Show
End With

Maybe the intellisense will show Filename in the dropdown. This is
where you would get the FileName.

Or place the cursor on top of FileDialog and press F1 to get help on
that.

If acc2010 still doesn't have a builtin FileOpenDialog then one
alternative would be to try out the code from this site

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

This is Allen Browne's site. He has a bunch of API code which works
quite well, except that there is a bunch more code that you have to deal
with. But this option should work if the simpler option is not
available (still) in Acc2010.

Albert D. Kallal

unread,
Dec 14, 2009, 8:58:42 PM12/14/09
to

Rich is on the right track. You can use:

Dim f As FileDialog

Set f = Application.FileDialog(msoFileDialogOpen)
f.Filters.Add "text documents", "*.txt"
f.Show
If f.SelectedItems.count > 0 Then
MsgBox "file choose was " & f.SelectedItems(1)
Else
MsgBox "no file selected"
End If


The above would set the file filter to *.txt files...you can change that for
whatever you want...

You can also setup the starting dir as:

Dim f As FileDialog
Set f = Application.FileDialog(msoFileDialogOpen)
f.Filters.Clear
f.Filters.Add "Text documents", "*.txt"
f.InitialFileName = "c:\"

f.Show
If f.SelectedItems.count > 0 Then
MsgBox "file choose was " & f.SelectedItems(1)
Else
MsgBox "no file selected"
End If

And, you can also just select a folder by using:

Set f = Application.FileDialog(msoFileDialogFolderPicker)


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com

Phil

unread,
Dec 15, 2009, 3:42:00 AM12/15/09
to

Thanks Rich ,

That is where I started some time ago.
I used that routine in my access 2000 database with windows XP.
Unfortunately for it does not seem to work with access 2010 and windows
seven. Many thanks,
Phil

Phil

unread,
Dec 15, 2009, 5:59:55 AM12/15/09
to
On 15/12/2009 01:58:39, "Albert D. Kallal" wrote:
>

Thanks Albert

Spot on, and so simple.

In the unlikely event that I want to get the name of one of these new fangled
libraries, is there a way of doing that?

There does not appear to be a msoFileDialogLibrartPicker in the Object
Browser.

Not vital, more a matter of interest

Many many thanks

Phil

Albert D. Kallal

unread,
Dec 15, 2009, 7:49:47 AM12/15/09
to
"Phil" <ph...@stantonfamily.co.uk> wrote in message
news:PbSdnU4aCZe38brW...@brightview.co.uk...

> On 15/12/2009 01:58:39, "Albert D. Kallal" wrote:
>>
>
> Thanks Albert
>
> Spot on, and so simple.
>
> In the unlikely event that I want to get the name of one of these new
> fangled
> libraries, is there a way of doing that?
>
> There does not appear to be a msoFileDialogLibrartPicker in the Object
> Browser.
>

It like using dao recordsets.

Inteli-sense should display a drop down list...

Set f = Application.FileDialog(

Once you get to the above (after just typed in the last (, then type in a
ctrl-j

You should see a pop up list of possible options...

Phil

unread,
Dec 15, 2009, 1:24:13 PM12/15/09
to
On 15/12/2009 01:58:39, "Albert D. Kallal" wrote:
>

Thanks Albert

Spot on what I wnted, and so very simple

Phil

David W. Fenton

unread,
Dec 15, 2009, 5:16:41 PM12/15/09
to
"Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
news:q3CVm.80878$W77....@newsfe11.iad:

> Rich is on the right track. You can use:
>
> Dim f As FileDialog

I had originally thought this required a reference to the Office
library, but I see that FileDialog is a member of the
Access.Application object, so it won't require it. I note that this
was not the case in Access 2000 (I don't have A2002 to see if it was
introduced there). I do see that it's still in A2007.

However, given the experience with the FileSearch object (introduced
in Office 95/97, and removed from Office 2007), I worry about
depending on it always being there. Using the Windows API is going
to be future-proof, as opposed to using a wrapper around it provided
by the Access application, which could come and go with different
versions.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Phil

unread,
Dec 16, 2009, 2:33:51 AM12/16/09
to
On 15/12/2009 22:16:45, "David W. Fenton" wrote:
> "Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
> news:q3CVm.80878$W77....@newsfe11.iad:
>
>> Rich is on the right track. You can use:
>>
>> Dim f As FileDialog
>
> I had originally thought this required a reference to the Office
> library, but I see that FileDialog is a member of the
> Access.Application object, so it won't require it. I note that this
> was not the case in Access 2000 (I don't have A2002 to see if it was
> introduced there). I do see that it's still in A2007.
>
> However, given the experience with the FileSearch object (introduced
> in Office 95/97, and removed from Office 2007), I worry about
> depending on it always being there. Using the Windows API is going
> to be future-proof, as opposed to using a wrapper around it provided
> by the Access application, which could come and go with different
> versions.
>

You could be right, Davis
Would you be kind enough to let me have a copy of the functions.
The ones I use were written by Ken Gatz over 12 years ago

Thanks

Phil

David W. Fenton

unread,
Dec 16, 2009, 11:05:33 PM12/16/09
to
"Phil" <ph...@stantonfamily.co.uk> wrote in
news:goednXSUyajAELXW...@brightview.co.uk:

> Would you be kind enough to let me have a copy of the functions.
> The ones I use were written by Ken Gatz over 12 years ago

In my apps I'm using the ADH97 versions. An earlier version of that
is what's on the Access Web
(http://mvps.org/access/api/api0001.htm). It's written against the
Windows API -- it's not going to be broken in future versions of
Windows because MS doesn't break its legacy APIs. Remember, DOS apps
compiled in 1982 or so still run on current versions of Windows, and
I don't expect that the Windows API will be omitted from whatever
replaces Windows some day.

That said, there very well may snazzier functionality in newer APIs,
or in using the wrapper object in Access, but I'd rather stick with
something that works and will continue to work as long as Windows
exists.

Phil

unread,
Dec 17, 2009, 1:19:06 PM12/17/09
to
On 17/12/2009 04:05:31, "David W. Fenton" wrote:
> "Phil" <ph...@stantonfamily.co.uk> wrote in
> news:goednXSUyajAELXW...@brightview.co.uk:
>
>> Would you be kind enough to let me have a copy of the functions.
>> The ones I use were written by Ken Gatz over 12 years ago
>
> In my apps I'm using the ADH97 versions. An earlier version of that
> is what's on the Access Web
> (http://mvps.org/access/api/api0001.htm). It's written against the
> Windows API -- it's not going to be broken in future versions of
> Windows because MS doesn't break its legacy APIs. Remember, DOS apps
> compiled in 1982 or so still run on current versions of Windows, and
> I don't expect that the Windows API will be omitted from whatever
> replaces Windows some day.
>
> That said, there very well may snazzier functionality in newer APIs,
> or in using the wrapper object in Access, but I'd rather stick with
> something that works and will continue to work as long as Windows
> exists.
>

Thanks David

That is what I have used

Phil

0 new messages