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(Cance l As Integer)
Dim file As String
Dim files As String
files = ""
file = Dir$("C:\My Document\Letter s\*.*")
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]

Re: Dir Function

> ...[color=blue]
> file = Dir$("C:\My Document\Letter s\*.*")
> ...
> 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?[/color]

Open in Word? Then this is the easiest method:

Application.Fol lowHyperlink "C:\My Documents\Lette rs\" & Me!List1.Value

HTH - Peter

--
No mails please.

[Paolo]

Re: Dir Function

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 <nospam@doering .org> wrote in message news:<bmtr72$qs 9n5$1@ID-204768.news.uni-berlin.de>...[color=blue][color=green]
> > ...
> > file = Dir$("C:\My Document\Letter s\*.*")
> > ...
> > 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?[/color]
>
> Open in Word? Then this is the easiest method:
>
> Application.Fol lowHyperlink "C:\My Documents\Lette rs\" & Me!List1.Value
>
> HTH - Peter[/color]

[Peter Doering]

Re: Dir Function

>> Application.Fol lowHyperlink "C:\My Documents\Lette rs\" & Me!List1.Value
[color=blue]
> 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).[/color]

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.dl l" Alias "ShellExecu teA" _
(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\Lette rs\" & Me!List1.Value


HTH - Peter

--
No mails please.

[Paolo]

Re: Dir Function

Thanks Peter it works great.

Peter Doering <nospam@doering .org> wrote in message news:<bn0sce$rh ife$1@ID-204768.news.uni-berlin.de>...[color=blue][color=green][color=darkred]
> >> Application.Fol lowHyperlink "C:\My Documents\Lette rs\" & Me!List1.Value[/color][/color]
>[color=green]
> > 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).[/color]
>
> 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.dl l" Alias "ShellExecu teA" _
> (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\Lette rs\" & Me!List1.Value
>
>
> HTH - Peter[/color]

[Paolo]

Re: Dir Function

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 <nospam@doering .org> wrote in message news:<bn0sce$rh ife$1@ID-204768.news.uni-berlin.de>...[color=blue][color=green][color=darkred]
> >> Application.Fol lowHyperlink "C:\My Documents\Lette rs\" & Me!List1.Value[/color][/color]
>[color=green]
> > 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).[/color]
>
> 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.dl l" Alias "ShellExecu teA" _
> (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\Lette rs\" & Me!List1.Value
>
>
> HTH - Peter[/color]

[Peter Doering]

Re: Dir Function

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

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]

Re: Dir Function

Thanks for your patiente Peter, but I have changed the code as
follows:
Private Sub Form_Open(Cance l 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 <nospam@doering .org> wrote in message news:<bn2udm$rm p6j$1@ID-204768.news.uni-berlin.de>...[color=blue][color=green]
> > 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.[/color]
>
> 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[/color]

[Peter Doering]

Re: Dir Function

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

This is the reason why I wrote ...
[color=blue]
> ... When you open the document you have to add ".doc" again.[/color]

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\Lette rs\" & Me!List1.Value & ".dot"

HTH - Peter

--
No mails please.

[Paolo]

Re: Dir Function

Thanks. Works great.

Peter Doering <nospam@doering .org> wrote in message news:<bn3o23$t8 kpv$1@ID-204768.news.uni-berlin.de>...[color=blue][color=green]
> > ...
> > file = Dir$("\\S0A3AA0 \Shared\FBU Tools\Test Forms\*.dot")
> > ...
> > I now see just the files names but am not able to open them.[/color]
>
> This is the reason why I wrote ...
>[color=green]
> > ... When you open the document you have to add ".doc" again.[/color]
>
> 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\Lette rs\" & Me!List1.Value & ".dot"
>
> HTH - Peter[/color]