Selecting a document from a folder through a VBA form

[Leila]

Hi there,
Could you please let me know how I can select a document
from a folder through a popup window (form) by using VBA?
Any help is greatly appreciated.

Thanks,
Leila

 

[Jezebel]

Simplest is to use the CommonDialog control.

 

[Guest]

Could you please explain how?
thanks,
leila

-----Original Message-----
Simplest is to use the CommonDialog control.





.

 

[Mark Baird]

You need to add the "Microsoft Common Dialog Control" to your form

You can then add the following code to command button

On Error GoTo HandleEr

With CommonDialog
.CancelError = True 'We need to set this to true so that we can capture the error when a user chooses Cancel
.FileName = "*.doc
.Filter = "Word document (*.doc)
.ShowOpe
MsgBox .FileNam
End Wit

HandleErr

If Err.Number > 0 The
Select Case Err.Numbe
Case 32755 ' User cancelled operation (number returned from CommonDialog1
Exit Su
Case Els
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "frmHyperlinks.GetDocument" 'ErrorHandler:$$N=frmHyperlinks.GetDocumen
End Selec
End I

Mark Baird

 

[Guest]

when I try to insert the Common Dialog Control to my form
I get this warning?
"The control could not be created because it is not
properly licenced"
any solution??
Thanks,
Leila

-----Original Message-----
You need to add the "Microsoft Common Dialog Control" to your form.

You can then add the following code to command button.

On Error GoTo HandleErr

With CommonDialog1
.CancelError = True 'We need to set this to true

so that we can capture the error when a user chooses
Cancel.

.FileName = "*.doc"
.Filter = "Word document (*.doc)"
.ShowOpen
MsgBox .FileName
End With


HandleErr:

If Err.Number > 0 Then
Select Case Err.Number
Case 32755 ' User cancelled operation (number returned from CommonDialog1.
Exit Sub
Case Else
MsgBox "Error " & Err.Number & ": " &

Err.Description,
vbCritical, "frmHyperlinks.GetDocument" 'ErrorHandler:$$N=
frmHyperlinks.GetDocument

 

[Let2Editor]

Leila,
I tried to do what it sound like you are trying to do, and ran into the same
error message. In searching the web, I found a program called 'Pathagoras'
(www.pathagoras.com??) which allows you to link a folder with a number, and
recall the folders contents with the number and to select a document using a
user form that displays. It's a nice program. A bit pricey but nice (pricey
because it also does document assembly). The author allows a long trial period
to check it out. I might serve your needs.

 

[Grayson Ferrantex]

Hi Leila,

After much puzzlement on my part I recently solved this issue once and for
all (I believe). The key was finding out Microsoft recommends NOT using an
OCX which is what the common dialog control is. For a series of reasons
having to do with DLL hell it is impossible to guarantee an OCX will
continue to work. The simple solution is to copy some free code from the
following site into a separate module (might sound scary but don't worry
this is simple) and directly use comdlg32.dll which is available on any
windows based computer.

This solution works from MS Access (which I needed) and also from VB6 or
from any other vb aware place. And the code you write is just as simple as
using the common dialog... So it really is good!

The recent revelation to me was why an OCX can't ever be guaranteed to
work.... The OCX requires a license to run and the only way to install the
license is to create a separate setup program. But even then installing any
other program can break your app...This is because a newer version of the
OCX may be installed without changing the license. So the license won't
match the newer OCX. This is why Microsoft recommends bypassing the entire
issue and working directly with the underlying comdlg32.dll which is always
available.

Here is the site with the free code followed by my code to use it. Note
another trick I include to get the path to the My Documents folder.

HTH,

Gray

Site to free code - thanks to Getz et al.
http://www.mvps.org/access/api/api0001.htm

My code to use the above and select a document from a folder. (including a
trick for getting the MyDocuments folder)
I put the Getz code into a module named dlgCommon

Dim wShell As Object 'New WshShell
Dim strPath As String
Dim blnCancel As Boolean
Dim varResult As Variant
Dim strFilter As String
Dim lngFlags As Long

On Error GoTo Err_Proc

Set wShell = CreateObject("WScript.Shell")
strPath = wShell.SpecialFolders("MyDocuments")
Set wShell = Nothing


strFilter = dlgCommon.ahtAddFilterItem(strFilter, "Text Files (*.txt)",
"*.txt")
varResult = ahtCommonFileOpenSave(InitialDir:=strPath, _
Filter:=strFilter, FilterIndex:=0, Flags:=lngFlags, _
DialogTitle:="Load Search")
If IsNull(varResult) Then
'cancel
ElseIf varResult = vbNullString Then
'Cancel
Else 'got a file
strFileNameAndPath = varResult
End If

Tip: Saving a file is just as simple...Change the ahtCommonFileOpenSave line
to include 'OpenFile:=False'

 

[Leila]

Hi Grayson,
Thanks so much for your explanation and the solution.
Leila

-----Original Message-----
Hi Leila,

After much puzzlement on my part I recently solved this issue once and for
all (I believe). The key was finding out Microsoft recommends NOT using an
OCX which is what the common dialog control is. For a series of reasons
having to do with DLL hell it is impossible to guarantee an OCX will
continue to work. The simple solution is to copy some free code from the
following site into a separate module (might sound scary but don't worry
this is simple) and directly use comdlg32.dll which is available on any
windows based computer.

This solution works from MS Access (which I needed) and also from VB6 or
from any other vb aware place. And the code you write is just as simple as
using the common dialog... So it really is good!

The recent revelation to me was why an OCX can't ever be guaranteed to
work.... The OCX requires a license to run and the only way to install the
license is to create a separate setup program. But even then installing any
other program can break your app...This is because a newer version of the
OCX may be installed without changing the license. So the license won't
match the newer OCX. This is why Microsoft recommends bypassing the entire
issue and working directly with the underlying comdlg32.dll which is always
available.

Here is the site with the free code followed by my code to use it. Note
another trick I include to get the path to the My Documents folder.

HTH,

Gray

Site to free code - thanks to Getz et al.
http://www.mvps.org/access/api/api0001.htm

My code to use the above and select a document from a folder. (including a
trick for getting the MyDocuments folder)
I put the Getz code into a module named dlgCommon

Dim wShell As Object 'New WshShell
Dim strPath As String
Dim blnCancel As Boolean
Dim varResult As Variant
Dim strFilter As String
Dim lngFlags As Long

On Error GoTo Err_Proc

Set wShell = CreateObject("WScript.Shell")
strPath = wShell.SpecialFolders("MyDocuments")
Set wShell = Nothing


strFilter = dlgCommon.ahtAddFilterItem

(strFilter, "Text Files (*.txt)",