View/edit files of various file types within Access window

[imrosie]

Hello,
I am attempting to build a form that allows for veiwing files (txt, doc, rtf, xls, pdf) within a subform. I need to have ability to browse to any directory, pull up a file in the subform 'Window', edit that file within the window (and save) then also 'add each path into the datatbase for . I don't want to use absolute paths because there will be too many users with various mapped folders

I saw one discussion (from ADezii) with a promising solution except for the "Absolute Path". Because of the absolute path (which I don't require), the button is grayed out. I need to ability to 'select' a file first . Has anyone altered that solution to do this??? If so, could you post that code??

thanks
Rosie

[ADezii]

Hello,
I am attempting to build a form that allows for veiwing files (txt, doc, rtf, xls, pdf) within a subform. I need to have ability to browse to any directory, pull up a file in the subform 'Window', edit that file within the window (and save) then also 'add each path into the datatbase for . I don't want to use absolute paths because there will be too many users with various mapped folders

I saw one discussion (from ADezii) with a promising solution except for the "Absolute Path". Because of the absolute path (which I don't require), the button is grayed out. I need to ability to 'select' a file first . Has anyone altered that solution to do this??? If so, could you post that code??

thanks
Rosie

1. What is the mechanism by which you are Viewing/Editing a File in the Sub-Form?
2. Even if you don't require the Absolute Path to the selected File in your Database, you will surely need it to Open the file.
3. There have been many examples in this Forum relating to the File Dialog Box, and other similar Browsing methods (Windows Dialog, API, etc.), but inevitably they all return a Path to the File.

[imrosie]

1. What is the mechanism by which you are Viewing/Editing a File in the Sub-Form?
2. Even if you don't require the Absolute Path to the selected File in your Database, you will surely need it to Open the file.
3. There have been many examples in this Forum relating to the File Dialog Box, and other similar Browsing methods (Windows Dialog, API, etc.), but inevitably they all return a Path to the File.

Hi ADezii,

Actually I'm trying to add a form to the image database, then have the switchboard open either documents or images. So I already had in there some of your declaration code:
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

There is some 'Open Dialog' code there too (used for the images), sorry it's pretty long:

Option Compare Database
Option Explicit
 
' Code to display standard "Open File" dialog.
 
Type OPENFILENAME
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type
 
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
  "GetOpenFileNameA" (OFN As OPENFILENAME) As Boolean
 
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
  "GetSaveFileNameA" (OFN As OPENFILENAME) As Boolean
 
Private Const ALLFILES = "All files"
 
Function MakeFilterString(ParamArray varFilt() As Variant) As String
' Create filter string.
' Returns "" if there are no arguments.
' Expects an even number of arguments (filter name, extension).
' Adds *.* if the number of arguments is odd.
 
  Dim strFilter As String
  Dim intRes As Integer
  Dim intNum As Integer
 
  intNum = UBound(varFilt)
  If (intNum <> -1) Then
    For intRes = 0 To intNum
      strFilter = strFilter & varFilt(intRes) & vbNullChar
    Next
    If intNum Mod 2 = 0 Then
      strFilter = strFilter & "*.*" & vbNullChar
    End If
 
    strFilter = strFilter & vbNullChar
  End If
 
  MakeFilterString = strFilter
End Function
 
Private Sub InitOFN(OFN As OPENFILENAME)
  With OFN
    ' Initialize fields user doesn't want to know about
    .hwndOwner = hWndAccessApp
    .hInstance = 0
    .lpstrCustomFilter = vbNullString
    .nMaxCustFilter = 0
    .lpfnHook = 0
    .lpTemplateName = 0
    .lCustData = 0
    .nMaxFile = 511
    .lpstrFileTitle = String(512, vbNullChar)
    .nMaxFileTitle = 511
    .lStructSize = Len(OFN)
    ' Use default filter if not specified.
    If .lpstrFilter = "" Then
      .lpstrFilter = MakeFilterString(ALLFILES)
    End If
    ' Pad lpstrFile with null chars.
    .lpstrFile = .lpstrFile & String(512 - Len(.lpstrFile), vbNullChar)
  End With
End Sub
 
Function OpenDialog(OFN As OPENFILENAME) As Boolean
  Dim intRes As Integer
  InitOFN OFN
  intRes = GetOpenFileName(OFN)
  If intRes Then
    ' Remove trailing null chars from lpstrFile.
    With OFN
      .lpstrFile = Left$(.lpstrFile, InStr(.lpstrFile, vbNullChar) - 1)
    End With
  End If
  OpenDialog = intRes
End Function

I was thinking I could add your code "Public Success As Boolean" to the Declaration module; add the function:

Public Function Execute_Program(ByVal strFilePath As String, _
    ByVal strParms As String, ByVal strDir As String) _
    As Boolean

(as you suggested to Steve123) then I would have most of what I needed???


I'm not real clear on which mechanism to use for the viewing. for pdf's, Adobe, for .doc's, Word...etc. is that what you're referring to?
The problem is the graying out of the the cmdbutton you use.....until there's an absolute path. That's the part I'm trying to figure out. thanks

Rosie

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

MODERATOR.

[imrosie]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.
MODERATOR.

So sorry, I'll reply in proper format.
Rosie

[ADezii]

Hi ADezii,

Actually I'm trying to add a form to the image database, then have the switchboard open either documents or images. So I already had in there some of your declaration code:

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

There is some 'Open Dialog' code there too (used for the images), sorry it's pretty long:

Option Compare Database
Option Explicit
 
' Code to display standard "Open File" dialog.
 
Type OPENFILENAME
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type
 
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
  "GetOpenFileNameA" (OFN As OPENFILENAME) As Boolean
 
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
  "GetSaveFileNameA" (OFN As OPENFILENAME) As Boolean
 
Private Const ALLFILES = "All files"
 
Function MakeFilterString(ParamArray varFilt() As Variant) As String
' Create filter string.
' Returns "" if there are no arguments.
' Expects an even number of arguments (filter name, extension).
' Adds *.* if the number of arguments is odd.
 
  Dim strFilter As String
  Dim intRes As Integer
  Dim intNum As Integer
 
  intNum = UBound(varFilt)
  If (intNum <> -1) Then
    For intRes = 0 To intNum
      strFilter = strFilter & varFilt(intRes) & vbNullChar
    Next
    If intNum Mod 2 = 0 Then
      strFilter = strFilter & "*.*" & vbNullChar
    End If
 
    strFilter = strFilter & vbNullChar
  End If
 
  MakeFilterString = strFilter
End Function
 
Private Sub InitOFN(OFN As OPENFILENAME)
  With OFN
    ' Initialize fields user doesn't want to know about
    .hwndOwner = hWndAccessApp
    .hInstance = 0
    .lpstrCustomFilter = vbNullString
    .nMaxCustFilter = 0
    .lpfnHook = 0
    .lpTemplateName = 0
    .lCustData = 0
    .nMaxFile = 511
    .lpstrFileTitle = String(512, vbNullChar)
    .nMaxFileTitle = 511
    .lStructSize = Len(OFN)
    ' Use default filter if not specified.
    If .lpstrFilter = "" Then
      .lpstrFilter = MakeFilterString(ALLFILES)
    End If
    ' Pad lpstrFile with null chars.
    .lpstrFile = .lpstrFile & String(512 - Len(.lpstrFile), vbNullChar)
  End With
End Sub
 
Function OpenDialog(OFN As OPENFILENAME) As Boolean
  Dim intRes As Integer
  InitOFN OFN
  intRes = GetOpenFileName(OFN)
  If intRes Then
    ' Remove trailing null chars from lpstrFile.
    With OFN
      .lpstrFile = Left$(.lpstrFile, InStr(.lpstrFile, vbNullChar) - 1)
    End With
  End If
  OpenDialog = intRes
End Function

I was thinking I could add your code "Public Success As Boolean" to the Declaration module; add the function:

Public Function Execute_Program(ByVal strFilePath As String, _
    ByVal strParms As String, ByVal strDir As String) _
    As Boolean

(as you suggested to Steve123) then I would have most of what I needed???


I'm not real clear on which mechanism to use for the viewing. for pdf's, Adobe, for .doc's, Word...etc. is that what you're referring to?
The problem is the graying out of the the cmdbutton you use.....until there's an absolute path. That's the part I'm trying to figure out. thanks

Rosie

I am attempting to build a form that allows for viewing files (txt, doc, rtf, xls, pdf) within a subform

.
Rosie, the previously illustrated technique will not allow you to do this. It will open a File within the Server Application that created it based on its Extension.

[imrosie]

.

Rosie, the previously illustrated technique will not allow you to do this. It will open a File within the Server Application that created it based on its Extension.

Hi and thanks,

Is there anyway to alter your illustration in order to do that? I have a group of users that are utilizing the image database, where they can browse to a folder, select an image view it in the subform window, save if they choose, erase it. Now they're asking to do the same with documents..? The idea is to store up a repository of (full path links) so various groups can browse and find doc's quickly. The docs would be of various types (txt, xls, docs, pdfs...) That's why I thought your example would be perfect with some tweeking. thanks for your help.
Rosie

[imrosie]

Hi ADezii,

Maybe I'm not explaining correctly. I want to open the files based on their extensions, exactly the same. I want to each user to be opened based on the applications their machines, such as if they have Adobe on their workstation, or Word..etc. I'm not expecting Access to open the files for editing.

Is this possible?
thanks
Rosie

[missinglinq]

So basically, you want to replace Windows Explorer with an Access database?

[ADezii]

Hi ADezii,

Maybe I'm not explaining correctly. I want to open the files based on their extensions, exactly the same. I want to each user to be opened based on the applications their machines, such as if they have Adobe on their workstation, or Word..etc. I'm not expecting Access to open the files for editing.

Is this possible?
thanks
Rosie

Yes it is. Each File will be opened on each Workstation (using ShellExecute()) based on the Default Application assigned to Open the specific File Extension as defined in the System Registry e.g. MS Paint may open a *.bmp on one PC, while that same File may be opened by another Graphic Application on another PC. Make sense?

[NeoPa]

So basically, you want to replace Windows Explorer with an Access database?

Very similar Linq, but I suspect that the database will offer a certain level of restriction as to what can be accessed this way, as well as possibly a more 'simple' user interface for the operator.

[imrosie]

So basically, you want to replace Windows Explorer with an Access database?

No that isn't my intent at all. I provided a user group with an image database, where they can browse to any folder containing images(bmps, jjpgs, tiffs, gifs), view an image within the (subform) window and the 'absolute' path to the image is stored (via 'add image' cmd). There also a control box on form to 'add' a new description. All this and the path to the actual file is stored in an image table.

Now my mgr (who used to be a programmer) has asked me to add the same capability to same database for documents (of any type). I'm a newbie, so I just assumed that it wouldn't be that hard but since I'm getting so many concerns about this it must be impossible.

My intent was to similarly establish a 'browse' to any folder containing documents and open/save. I searched around and found that it can be done using Windows File Open/Save dialog box (Ken Gertz). However, it will allow browse (outside of Access),save a filepath into a table and that's it.

From your response I''m gathering it is technically impossible to open/save path, and ALSO somehow view (like the images)
in an Access window?!? The purpose for the request is that several groups want to share documents amongst the group, store them in their group folders and in that 'Vewiing' window (if it were possible) edit a doc, and resave it back into the groups folder....

If I can get anything close to doing this it would be helpful. Or if I am totally in left field with this request...I'll let it go and move on. thanks

Rosie

[imrosie]

Yes it is. Each File will be opened on each Workstation (using ShellExecute()) based on the Default Application assigned to Open the specific File Extension as defined in the System Registry e.g. MS Paint may open a *.bmp on one PC, while that same File may be opened by another Graphic Application on another PC. Make sense?

Hi ADezii,

Yes it does make sense. So how can I alter your code to get the results I want? thanks

Rosie

[ADezii]

Hi ADezii,

Yes it does make sense. So how can I alter your code to get the results I want? thanks

Rosie

From your response I''m gathering it is technically impossible to open/save path, and ALSO somehow view (like the images) in an Access window?

1. Use a File Browsing utility to first locate the File.
2. Retrieve the File and Save its Path to the underlying RecordSource for the Form.
3. In order to Open the file within an 'Access Window', you would probably have to dynamically load it into an Unbound Object Frame on a Form.
4. I'm assuming that this operation would have to be repeated frequently, why the need to store the Path if the File will loaded this way each time?

[imrosie]

1. Use a File Browsing utility to first locate the File.
2. Retrieve the File and Save its Path to the underlying RecordSource for the Form.
3. In order to Open the file within an 'Access Window', you would probably have to dynamically load it into an Unbound Object Frame on a Form.
4. I'm assuming that this operation would have to be repeated frequently, why the need to store the Path if the File will loaded this way each time?

Hi ADezii,
"why the need to store the Path if the File will loaded this way each time?"

Because the 'File and Save its Path to the underlying RecordSource' for the Form I believe is the path only to the original file. These documents will be altered by group members and somehow stored with a new description (added to identify it) by a different table field hopefully.
Also there is another combo control on the Form which holds a list of the 'stored' altered docs which points to the new stored location. So a person can (double-click to) select a doc from the combo control by its' description.

Yes, you're right, this operation would be repeated frequently. I have 3 departments waiting with baited breath for something like this to work....again, I'm newbie who has limited knowledge. I'm going off now to research how to implement your suggestion of "Open the file within an 'Access Window', you would probably have to dynamically load it into an Unbound Object Frame on a Form"

Thanks and any help you can provide would be greatly appreciated.

Rosie

[ADezii]

Hi ADezii,
"why the need to store the Path if the File will loaded this way each time?"

Because the 'File and Save its Path to the underlying RecordSource' for the Form I believe is the path only to the original file. These documents will be altered by group members and somehow stored with a new description (added to identify it) by a different table field hopefully.
Also there is another combo control on the Form which holds a list of the 'stored' altered docs which points to the new stored location. So a person can (double-click to) select a doc from the combo control by its' description.

Yes, you're right, this operation would be repeated frequently. I have 3 departments waiting with baited breath for something like this to work....again, I'm newbie who has limited knowledge. I'm going off now to research how to implement your suggestion of "Open the file within an 'Access Window', you would probably have to dynamically load it into an Unbound Object Frame on a Form"

Thanks and any help you can provide would be greatly appreciated.

Rosie

This should point you in the right direction:
The following example creates a linked OLE object using an Unbound Object Frame named OLE1 and sizes the control to display the object's entire contents when the user clicks a command button.

Dim strFileName As String          
strFileName = "C:\Test\Test.xls"        'retrieved by any means necessary
 
OLE1.Class = "Excel.Sheet"    ' Set class name.
 
' Specify type of object.
OLE1.OLETypeAllowed = acOLELinked
 
' Specify source file.
OLE1.SourceDoc = strFileName
 
' Create linked object.
OLE1.Action = acOLECreateLink
 
' Adjust control size.
OLE1.SizeMode = acOLESizeZoom

A couple of other things I forgot to mention:

1. Set the Enabled Property of the Unbound OLE Object Frame = Yes.
2. Set the Locked Property of the Unbound Object Frame = No.
3. Experiment with the SizeMode Property (Reference the Help Files).

[imrosie]

This should point you in the right direction:
The following example creates a linked OLE object using an Unbound Object Frame named OLE1 and sizes the control to display the object's entire contents when the user clicks a command button.

Dim strFileName As String          
strFileName = "C:\Test\Test.xls"        'retrieved by any means necessary
 
OLE1.Class = "Excel.Sheet"    ' Set class name.
 
' Specify type of object.
OLE1.OLETypeAllowed = acOLELinked
 
' Specify source file.
OLE1.SourceDoc = strFileName
 
' Create linked object.
OLE1.Action = acOLECreateLink
 
' Adjust control size.
OLE1.SizeMode = acOLESizeZoom

A couple of other things I forgot to mention:

1. Set the Enabled Property of the Unbound OLE Object Frame = Yes.
2. Set the Locked Property of the Unbound Object Frame = No.
3. Experiment with the SizeMode Property (Reference the Help Files).

thanks ADezii,

I've been playing with unbound Object Frame most of the day....as yet it wasn't doing what I wanted...I going to implement this now..

Am I correct in assuming this code goes behind a (one) control on my form (or inside of the subform on the form)??

thanks
Rosie