VBA (Office 97)

Topic

New Reply

I was looking to create a file manager windows on a form. Have read up on a few things but would be interested to know if anyone had done this or if it was possible?

Reply | Quote

Replies

Depends what you mean by “file manager”

I am in the middle of a project where I needed a File Open/fileSave capability. Found this code somewhere and adapted it to my PPT VBA

Option Explicit

Type RECT_Type
left As Long
top As Long
right As Long
bottom As Long
End Type

Declare Function apiGetWindowRect Lib “user32” Alias _
“GetWindowRect” (ByVal Hwnd As Long, lpRect As RECT_Type) As Long
Declare Function apiGetDC Lib “user32” Alias “GetDC” _
(ByVal Hwnd As Long) As Long
Declare Function apiReleaseDC Lib “user32” Alias “ReleaseDC” (ByVal _
Hwnd As Long, ByVal hDC As Long) As Long
Declare Function apiGetDeviceCaps Lib “gdi32” Alias “GetDeviceCaps” _
(ByVal hDC As Long, ByVal nIndex As Long) As Long
Declare Function apiGetActiveWindow Lib “user32” Alias _
“GetActiveWindow” () As Long
Declare Function apiGetParent Lib “user32” Alias “GetParent” (ByVal _
Hwnd As Long) As Long
Declare Function apiGetClassName Lib “user32” Alias “GetClassNameA” _
(ByVal Hwnd As Long, ByVal lpClassName As String, ByVal _
nMaxCount As Long) As Long

‘ calling procedure will need this declaration
‘ to find the hWnd of the current window
Private Declare Function GetForegroundWindow Lib _
“user32” () As Long

Global Const TWIPSPERINCH = 1440

Public Const ApplicationName = “Some Random Application”

Private Type OPENFILENAME
lStructSize As Long
Hwnd 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” (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib “comdlg32.dll” Alias “GetSaveFileNameA” (pOpenfilename As OPENFILENAME) As Long
‘//
‘// Constants (Public for File Dialog Box)
‘//

Public Const cdFileOpen = 1
Public Const cdFileSaveAs = 2

‘//
‘// Constants (Private)
‘//

Private Const FW_BOLD = 700
Private Const GMEM_MOVEABLE = &H2
Private Const GMEM_ZEROINIT = &H40
Private Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)
Private Const OFN_ALLOWMULTISELECT = &H200
Private Const OFN_CREATEPROMPT = &H2000
Private Const OFN_ENABLEHOOK = &H20
Private Const OFN_ENABLETEMPLATE = &H40
Private Const OFN_ENABLETEMPLATEHANDLE = &H80
Private Const OFN_EXPLORER = &H80000
Private Const OFN_EXTENSIONDIFFERENT = &H400
Private Const OFN_FILEMUSTEXIST = &H1000
Private Const OFN_HIDEREADONLY = &H4
Private Const OFN_LONGNAMES = &H200000
Private Const OFN_NOCHANGEDIR = &H8
Private Const OFN_NODEREFERENCELINKS = &H100000
Private Const OFN_NOLONGNAMES = &H40000
Private Const OFN_NONETWORKBUTTON = &H20000
Private Const OFN_NOREADONLYRETURN = &H8000
Private Const OFN_NOTESTFILECREATE = &H10000
Private Const OFN_NOVALIDATE = &H100
Private Const OFN_OVERWRITEPROMPT = &H2
Private Const OFN_PATHMUSTEXIST = &H800
Private Const OFN_READONLY = &H1
Private Const OFN_SHAREAWARE = &H4000
Private Const OFN_SHAREFALLTHROUGH = 2
Private Const OFN_SHARENOWARN = 1
Private Const OFN_SHAREWARN = 0
Private Const OFN_SHOWHELP = &H10

‘//
‘// DialogFile Function
‘//
‘// Description:
‘// Displays the File Open/Save As common dialog boxes.
‘//
‘// Syntax:
‘// StrVar = DialogFile(hWnd, IntVar, StrVar, StrVar, StrVar, StrVar, StrVar)
‘//
‘// Example:
‘// szFilename = DialogFile(Me.hWnd, 1, “Open”, “MyFileName.doc”, “Documents|*.doc|All Files|*.*”, App.Path, “doc”)
‘// szFilename = DialogFile(GetForegroundWindow(), 1, “Open”, “MyFileName.doc”, “Documents|*.doc|All Files|*.*”, App.Path, “doc”)
‘//

Public Function DialogFile(Hwnd As Long, dwMode As Integer, szDialogTitle As String, szFilename As String, szFilter As String, szDefDir As String, szDefExt As String) As String

Dim x As Long, OFN As OPENFILENAME, szFile As String, szFileTitle As String

OFN.lStructSize = Len(OFN)
OFN.Hwnd = Hwnd
OFN.lpstrTitle = szDialogTitle
OFN.lpstrFile = szFilename & String$(250 – Len(szFilename), 0)
OFN.nMaxFile = 255
OFN.lpstrFileTitle = String$(255, 0)
OFN.nMaxFileTitle = 255
OFN.lpstrFilter = szFilter
OFN.nFilterIndex = 1

OFN.lpstrInitialDir = szDefDir
OFN.lpstrDefExt = szDefExt

If dwMode = cdFileOpen Then
OFN.Flags = OFN_HIDEREADONLY Or OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST
x = GetOpenFileName(OFN)
Else
OFN.Flags = OFN_HIDEREADONLY Or OFN_OVERWRITEPROMPT Or OFN_PATHMUSTEXIST
x = GetSaveFileName(OFN)
End If

If x 0 Then

‘// If InStr(OFN.lpstrFileTitle, Chr$(0)) > 0 Then
‘// szFileTitle = Left$(OFN.lpstrFileTitle, InStr(OFN.lpstrFileTitle, Chr$(0)) – 1)
‘// End If
If InStr(OFN.lpstrFile, Chr$(0)) > 0 Then
szFile = left$(OFN.lpstrFile, InStr(OFN.lpstrFile, Chr$(0)) – 1)
End If
‘// OFN.nFileOffset is the number of characters from the beginning of the
‘// full path to the start of the file name
‘// OFN.nFileExtension is the number of characters from the beginning of the
‘// full path to the file’s extention, including the (.)
‘// MsgBox “File Name is ” & szFileTitle & Chr$(13) & Chr$(10) & “Full path and file is ” & szFile, , “Open”

‘// DialogFile = szFile & “|” & szFileTitle
DialogFile = szFile

Else

DialogFile = “”

End If

End Function

Reply | Quote

Thanks. Hmmm…it looks complicated but I suspect that as it uses built in dialogs its not going to afford a checkbox type functionality that I need. Pls refer a subsequent posting that details more specifically what I want to build.

I appreciate the contribution and will try to get your code operational but if you felt inclined to attach a blank PPT file with form to save time that would be useful. Thanks again.

Reply | Quote

A demo in word 97 is attached.
You are right — the code looks complicated — but the form that calls the code is very simple.

Reply | Quote

Sorry – tried a couple of times to upload a file, but I can’t connect to the server. Send me your email if you still want the file. All it does is open a FileOpen dialog and allow you to select one or more files.

Reply | Quote

Thanks for trying jacksonmacd. No need to progress further with windows dialogs. I have been fishing on www and need more functionality that what a standard windows dialog offers. The solution at this stage has to be based around the Treeview control. I have so far figured out how to populate it from a database (of file found listings) and need to work on the implementation of the checkbox option so that files can be nominated for the next level of processing.

Reply | Quote

You didn’t say which application you want to do this in, but if it’s Access, I’d suggest you post the question in the Access forum.

In any Office app, you have a couple of options if you want a standard Windows File Open dialog: using Jackson’s approach which directly addresses the Windows common dialog, or using a common dialog control which wraps the API calls in the common dialog OCX. If you’re looking for something else, like a TreeView, you’ll need to be more specific.

You might also want to take a look at The Access Web, where there are several examples of both use of the common dialog and the creation of something like a file manager window on a form. You should be able to adapt either to other Office apps, since the code to do so should be the same as in Access.

Reply | Quote

I am wanting to build in Word 97 on a form a 2 window file manager type panel. In the left window a list of sub dirs and files of a nominated (parent) dir. I will want to employ a checkbox capability (as available in Treeview) to nominate files (and their corresponding folders) that will be copied to the nominated directory on the right hand side. So The left may contain 10 folders containing 20 files and the right may end up (a subset of) with 3 folders and 8 files. The left is my exclusive author area, and the right a all others on the network visible area.

Hope that clarifies what I hope is a not too impossible developement.

I have since followed through with some other similar requests in the lounge and got to the MSDN site and found some doco on Treeview. Its got a VB focus but I have been able to figure out how to populate a node with some additem type commands. Ultimately the items values will be populated from the nominated parent directory as described above.

Thanks for the reply.

Reply | Quote

Charlotte, Thanks for the lnk to The Access Web. I found an article titled “Use Class modules to return Dir and File names” working in Access 97 that lists all drives on the system. I’ll see if it can be migrated across to Word 97.

Reply | Quote

Charlotte:

the reason that I posted the code (and I know it looks complicated) is that I tried to find the common controls to put into a UserForm in PPT. I could not find them in the registered components on the computer. Did I overlook something? *Should* the common controls be accessible via the UserForm in PPT (they are accessible, say, from VB6 on this machine). I had the code already written in another project, and it was just expedient to cut-and-paste it.

OTK: sorry if I mislead you — that code will simply provide a standard FileOpen dialog on your form. Reading your subsequent post — that doesn’t seem to address your concern.

I am afraid that I don’t fully understand your aim of your program. However, it looks like you are wanting to generate a list of files. I suggest that you investigate the FileSearch object, which makes a list of files that meet a given specification. Check out the online Help file for the FileSearch object.

Reply | Quote

Yes, the common controls should be available on the machine, although they may not be neatly listed in other apps the way there are in VB. I haven’t tried using them in PPT, so I can’t speak to that issue, but you should be able to browse for the control. Depending on which version of VB Pro you have installed, the filename will be mscomctl.ocx (VB6) or comctl32.ocx (VB5).

Reply | Quote