asked on

How to capture Word document name to store in Access table

I have an Access application and would like users to be able to associate one or more Word documents with a database record, and then be able to open a selected document. The user would create and initially save the document outside of the Access application. There could be several document types but each type will be stored in a consistent Windows directory/path.

After reading various posts, I'm thinking I should create a "child" document table related to the main table and store the document names in this table. This table would also include document type and I would store the paths associated with document types in a separate control type of table. I could then display the associated documents in a form and call ShellExecute to open the document, using document name and path.

My question(s): Assuming this approach makes sense, how would I allow user, within Accesss, to browse the Windows directory and select the file name to store in the Access document table (i.e, don't want them to have to type it into the table). I'm also open to suggestions on better way to do this.

rockiroads

So far your ideas seem spot on. I would do the same. Create a one to many relationship and store the paths, then use ShellExecute to open (and print if u want)

One method is to use the BrowseDirectory view

Its the simpler one, u can select files, it then returns the filename
just place this method in a module

Private Type BROWSEINFO
hOwner As Long 'handle to window opening dialog
pidlRoot As Long 'A pointer to an ITEMIDLIST structure (a.k.a. a PIDL) which identifies the root folder for the dialog box. The user's selection is limited to this folder and any subfolders under it
pszDisplayName As String 'Receives the null-terminated display name of the folder the user selects. This must be initialized to an empty string of at least 260 characters
lpszTitle As String 'The title of the dialog box, which will appear above the folder tree
ulFlags As Long 'See BIF flags above
lpfn As Long 'A pointer to the BrowseCallbackProc callback function used to process the dialog box's messages. To use the default behavior, set this to 0
lParam As Long 'An application-defined value to pass to the callback function, if needed
iImage As Long 'Receives the index of the system image associated with the user's selection
End Type

Private Declare Function SHBrowseForFolder Lib "shell32" Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pIdl As Long, ByVal pszPath As String) As Long

Public Function BrowseDirectory() As String

Dim tBI As BROWSEINFO
Dim pIdl As Long
Dim sPath As String

tBI.lpszTitle = "Browse XLS"
tBI.ulFlags = &H4000
pIdl = SHBrowseForFolder(tBI)

'Check for cancel
If pIdl = 0 Then Exit Function

'Get selected path from the id list, will rtn False if the id list can't be converted
sPath = String$(260, 0)
SHGetPathFromIDList ByVal pIdl, ByVal sPath

' Display the path and the name of the selected folder
BrowseDirectory = Left(sPath, InStr(sPath, vbNullChar) - 1)

End Function

rockiroads

The other example is to use the standard fileopen dialog

Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strfile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Declare Function pksGetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (OFN As OPENFILENAME) As Boolean
Declare Function pksGetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (OFN As OPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const OFN_READONLY = &H1
Global Const OFN_OVERWRITEPROMPT = &H2
Global Const OFN_HIDEREADONLY = &H4
Global Const OFN_NOCHANGEDIR = &H8
Global Const OFN_SHOWHELP = &H10
Global Const OFN_ENABLEHOOK = &H20
Global Const OFN_ENABLETEMPLATE = &H40
Global Const OFN_ENABLETEMPLATEHANDLE = &H80
Global Const OFN_NOVALIDATE = &H100
Global Const OFN_ALLOWMULTISELECT = &H200
Global Const OFN_EXTENSIONDIFFERENT = &H400
Global Const OFN_PATHMUSTEXIST = &H800
Global Const OFN_FILEMUSTEXIST = &H1000
Global Const OFN_CREATEPROMPT = &H2000
Global Const OFN_SHAREAWARE = &H4000
Global Const OFN_NOREADONLYRETURN = &H8000
Global Const OFN_NOTESTFILECREATE = &H10000
Global Const OFN_NONETWORKBUTTON = &H20000
Global Const OFN_NOLONGNAMES = &H40000
Global Const OFN_EXPLORER = &H80000
Global Const OFN_NODEREFERENCELINKS = &H100000
Global Const OFN_LONGNAMES = &H200000

Private Sub PromptForFile()

Dim sFileArray As String
Dim sPath As String

Dim sFilter As String
Dim lFlags As Long

On Error Resume Next

sFilter = AddFilterItem(sFilter, "Text Files(*.tx)", "*.txt")

sFileArray = CreateDialog(InitialDir:="sFilter", _
filter:=sFilter, FilterIndex:=1, Flags:=OFN_ALLOWMULTISELECT + OFN_EXPLORER, _
DialogTitle:="Save File As", _
OpenFile:=True)
MsgBox Err.Number

MsgBox sFileArray, , "FULL PATH AND FILENAME"
End Sub

Function GetOpenFile(Optional varDirectory As Variant, Optional varTitleForDialog As Variant) As Variant

Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant

lngFlags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or OFN_NOCHANGEDIR

If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then varTitleForDialog = ""

strFilter = AddFilterItem(strFilter, "Access (*.mdb)", "*.MDB;*.MDA")
varFileName = CreateDialog(OpenFile:=True, InitialDir:=varDirectory, filter:=strFilter, Flags:=lngFlags, DialogTitle:=varTitleForDialog)

If Not IsNull(varFileName) Then varFileName = TrimNull(varFileName)

GetOpenFile = varFileName
End Function

Function CreateDialog( _
Optional ByRef Flags As Variant, _
Optional ByVal InitialDir As Variant, _
Optional ByVal filter As Variant, _
Optional ByVal FilterIndex As Variant, _
Optional ByVal DefaultExt As Variant, _
Optional ByVal filename As Variant, _
Optional ByVal DialogTitle As Variant, _
Optional ByVal hWnd As Variant, _
Optional ByVal OpenFile As Variant) As Variant

Dim OFN As OPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
Dim sFiles() As String
Dim i As Integer
Dim sSelectedItem As String
Dim sDrive As String

If IsMissing(InitialDir) Then InitialDir = CurDir
If IsMissing(filter) Then filter = ""
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(Flags) Then Flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = ""
If IsMissing(filename) Then filename = ""
If IsMissing(DialogTitle) Then DialogTitle = ""
If IsMissing(hWnd) Then hWnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True

' Allocate string space for the returned strings.
strFileName = Left(filename & String(256, 0), 256)
strFileTitle = String(256, 0)

' Set up the data structure before you call the function
With OFN
.lStructSize = Len(OFN)
.hwndOwner = hWnd
.strFilter = filter
.nFilterIndex = FilterIndex
.strfile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = DialogTitle
.Flags = Flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
.hInstance = 0
.strCustomFilter = ""
.nMaxCustFilter = 0
.lpfnHook = 0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With

If OpenFile Then
fResult = pksGetOpenFileName(OFN)
Else
fResult = pksGetSaveFileName(OFN)
End If

If fResult Then
If Not IsMissing(Flags) Then Flags = OFN.Flags

sSelectedItem = Replace(OFN.strfile, vbNullChar, ";")
Debug.Print OFN.strfile
sFiles = Split(sSelectedItem, ";")

If Left(sFiles(0), 2) = "\\" Then
If GetAttr(sFiles(0)) And vbDirectory = True Then
CreateDialog = sFiles(0)
Exit Function
End If

ElseIf Right(sFiles(0), 2) <> ":\" Then
CreateDialog = sFiles(0)
Exit Function
End If

CreateDialog = ""

sDrive = sFiles(0)
If Right$(sDrive, 1) <> "\" Then sDrive = sDrive & "\"

For i = LBound(sFiles) + 1 To UBound(sFiles)
If sFiles(i) <> vbNullChar And Trim$(sFiles(i)) <> "" Then
If CreateDialog <> "" Then CreateDialog = CreateDialog & ";"
CreateDialog = CreateDialog & sDrive & sFiles(i)
End If
Next i

If CreateDialog = "" And OFN.strfile <> "" Then CreateDialog = OFN.strfile
Else
CreateDialog = "NoFile"
End If
End Function

Function AddFilterItem(strFilter As String, strDescription As String, Optional varItem As Variant) As String
If IsMissing(varItem) Then varItem = "*.*"
AddFilterItem = strFilter & strDescription & vbNullChar & varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String

Dim intPos As Integer

intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function

Here is an example of using the above

Function Testit()

Dim sFilter As String
Dim lFlags As Long

sFilter = AddFilterItem(sFilter, "Access Files (*.mda, *.mdb)", _
"*.MDA;*.MDB")
sFilter = AddFilterItem(sFilter, "dBASE Files (*.dbf)", "*.DBF")
sFilter = AddFilterItem(sFilter, "Text Files (*.txt)", "*.TXT")
sFilter = AddFilterItem(sFilter, "All Files (*.*)", "*.*")
lFlags = OFN_ALLOWMULTISELECT + OFN_EXPLORER

MsgBox "You selected: " & CreateDialog(InitialDir:="C:\", _
filter:=sFilter, FilterIndex:=3, Flags:=lFlags, _
DialogTitle:="Hello! Open Me!")
End Function

rockiroads

When u call the function and get the image selected

then u can insert into your table automatically

You can then create a listbox or some list with shows your image
May I suggest u give a title for your images, allow users to enter one, if they dont, just use the filename

dir$(full path of filename) returns just the filename name

Then users can browse by title, no need to show path (well u can if u want)

and a image box control, so as you select a new item, u can load the image, kinda preview

jolodali

ASKER

Hi rockiroads - thanks for your help. Am I understanding correctly that BrowseDirectory and fileopen dialog are alternatives? Is there a benefit to one over the other?
I am planning to try this out in the next day or so -
Thanks

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jolodali

ASKER

Here are your points - if I have any more questions once I try it I'll post a new one.
Thanks again. I still can't believe how helpful this board is for a former COBOL programmer teaching herself VBA!

rockiroads

Its a breeze, trust me. VBA is quite simple. Mind you, I come from a C/C++/Unix background.
I did do COBOL but that was years ago.

a useful site for you http://www.mvps.org/access/

all the best with your self education :)

jolodali

ASKER

I just tried out BrowseDirectory and dir$ - just perfect. Thanks again.