Dir Function

[Paolo]

Friends,

I have created a form with a list box that shows all my Word files.
I have then added the following code (found on comp.databases
newsgroup) to the Open event of my form and it works fine:

Private Sub Form_Open(Cancel As Integer)
Dim file As String
Dim files As String
files = ""
file = Dir$("C:\My Document\Letters\*.*")
While Len(file) > 0
files = files & file & ";"
file = Dir$
Wend
List1.RowSource = files
End Sub

What I would now like is to be able to open the selected file by
clicking on it from the listbox. Is this possible?

Thanks.

[Peter Doering]

> ...

file = Dir$("C:\My Document\Letters\*.*")
...
List1.RowSource = files
...
What I would now like is to be able to open the selected file by
clicking on it from the listbox. Is this possible?

Open in Word? Then this is the easiest method:

Application.FollowHyperlink "C:\My Documents\Letters\" & Me!List1.Value

HTH - Peter

--
No mails please.

[Paolo]

Thanks, I have done a Paste of your code and added to the click event
of my list box but an error show up:
File name or class name not found during Automation operation (Error
432).

Any idea? Sorry I am not too clear but do not know much of VBA.

Thanks.

Peter Doering <no****@doering.org> wrote in message news:<bm************@ID-204768.news.uni-berlin.de>...

...
file = Dir$("C:\My Document\Letters\*.*")
...
List1.RowSource = files
...
What I would now like is to be able to open the selected file by
clicking on it from the listbox. Is this possible?

Open in Word? Then this is the easiest method:

Application.FollowHyperlink "C:\My Documents\Letters\" & Me!List1.Value

HTH - Peter

[Peter Doering]

>> Application.FollowHyperlink "C:\My Documents\Letters\" & Me!List1.Value

Thanks, I have done a Paste of your code and added to the click event
of my list box but an error show up:
File name or class name not found during Automation operation (Error
432).

Well, it works for me.

Another method (by Jonathan West, found in qoogle). As I don't get your
error I don't know whether it will cure the problem on your side, just give
it a try:

1. Copy this code to a new module.

Private Declare Function ShellExecute Lib _
"shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Public Function HyperJump(ByVal URL As String) As Long
HyperJump = ShellExecute(0&, vbNullString, URL, vbNullString, _
vbNullString, vbNormalFocus)
End Function

2. Copy this code to your listbox procedure:

HyperJump "C:\My Documents\Letters\" & Me!List1.Value
HTH - Peter

--
No mails please.

[Paolo]

Thanks Peter it works great.

Peter Doering <no****@doering.org> wrote in message news:<bn************@ID-204768.news.uni-berlin.de>...

Application.FollowHyperlink "C:\My Documents\Letters\" & Me!List1.Value

Thanks, I have done a Paste of your code and added to the click event
of my list box but an error show up:
File name or class name not found during Automation operation (Error
432).

Well, it works for me.

Another method (by Jonathan West, found in qoogle). As I don't get your
error I don't know whether it will cure the problem on your side, just give
it a try:

1. Copy this code to a new module.

Private Declare Function ShellExecute Lib _
"shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Public Function HyperJump(ByVal URL As String) As Long
HyperJump = ShellExecute(0&, vbNullString, URL, vbNullString, _
vbNullString, vbNormalFocus)
End Function

2. Copy this code to your listbox procedure:

HyperJump "C:\My Documents\Letters\" & Me!List1.Value
HTH - Peter

[Paolo]

Thanks, your code works fine. Just one more thing.
The list box I have created display the file names and extensions (ex.
Letter.doc). I would like not to diplay the extension and just see the
file name. Is there a way? Thanks.
Peter Doering <no****@doering.org> wrote in message news:<bn************@ID-204768.news.uni-berlin.de>...

Application.FollowHyperlink "C:\My Documents\Letters\" & Me!List1.Value

Thanks, I have done a Paste of your code and added to the click event
of my list box but an error show up:
File name or class name not found during Automation operation (Error
432).

Well, it works for me.

Another method (by Jonathan West, found in qoogle). As I don't get your
error I don't know whether it will cure the problem on your side, just give
it a try:

1. Copy this code to a new module.

Private Declare Function ShellExecute Lib _
"shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Public Function HyperJump(ByVal URL As String) As Long
HyperJump = ShellExecute(0&, vbNullString, URL, vbNullString, _
vbNullString, vbNormalFocus)
End Function

2. Copy this code to your listbox procedure:

HyperJump "C:\My Documents\Letters\" & Me!List1.Value
HTH - Peter

[Peter Doering]

> The list box I have created display the file names and extensions (ex.

Letter.doc). I would like not to diplay the extension and just see the
file name. Is there a way? Thanks.

Simple way:

files = files & Left(file, Len(file)-4) & ";"

This is hardcoded for 3 char's extension, i.e. not very clean. When you
open the document you have to add ".doc" again.

Peter

--
No mails please.

[Paolo]

Thanks for your patiente Peter, but I have changed the code as
follows:
Private Sub Form_Open(Cancel As Integer)
Dim file As String
Dim files As String

files = ""
file = Dir$("\\S0A3AA0\Shared\FBU Tools\Test Forms\*.dot")
While Len(file) > 0
files = files & Left(file, Len(file) - 4) & ";"
file = Dir$
Wend
List1.RowSource = files
End Sub

I now see just the files names but am not able to open them.

Any idea? and what about the .doc ext you where talking about. Where
do I type that? Thanks.
Peter Doering <no****@doering.org> wrote in message news:<bn************@ID-204768.news.uni-berlin.de>...

The list box I have created display the file names and extensions (ex.
Letter.doc). I would like not to diplay the extension and just see the
file name. Is there a way? Thanks.

Simple way:

files = files & Left(file, Len(file)-4) & ";"

This is hardcoded for 3 char's extension, i.e. not very clean. When you
open the document you have to add ".doc" again.

Peter

[Peter Doering]

> ...

file = Dir$("\\S0A3AA0\Shared\FBU Tools\Test Forms\*.dot")
...
I now see just the files names but am not able to open them.
This is the reason why I wrote ...
... When you open the document you have to add ".doc" again.

You are looking for *.dot (= document templates), not *.doc (= document
files) as in my example. You have to add this extension (*.dot) to the open
statement, because you don't have it in Me!List1.Value anymore:

HyperJump "C:\My Documents\Letters\" & Me!List1.Value & ".dot"

HTH - Peter

--
No mails please.

[Paolo]

Thanks. Works great.

Peter Doering <no****@doering.org> wrote in message news:<bn************@ID-204768.news.uni-berlin.de>...

...
file = Dir$("\\S0A3AA0\Shared\FBU Tools\Test Forms\*.dot")
...
I now see just the files names but am not able to open them.

This is the reason why I wrote ...

... When you open the document you have to add ".doc" again.

You are looking for *.dot (= document templates), not *.doc (= document
files) as in my example. You have to add this extension (*.dot) to the open
statement, because you don't have it in Me!List1.Value anymore:

HyperJump "C:\My Documents\Letters\" & Me!List1.Value & ".dot"

HTH - Peter