Results 1 to 11 of 11

Thread: VBA (Office 97)

  1. #1
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA (Office 97)

    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?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    Depends what you mean by "file manager" <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    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
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA (Office 97)

    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 <A target="_blank" HREF=http://www.mvps.org/access/>The Access Web</A>, 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.
    Charlotte

  4. #4
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    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.

  5. #5
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    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.

  6. #6
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    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. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    A demo in word 97 is attached.
    You are right -- the code looks complicated -- but the form that calls the code is very simple.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  10. #10
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (Office 97)

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA (Office 97)

    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).
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •