[RESOLVED] Word 2000 & 2003 compatibilities

Hey all,

I've got a strange issue, currently our corporate systems run on Win 2000, and Office 2000. Our Tech people are in the processes on upgrading us to XP and Office 2003. Within our department we have a customised normal.dot template that everyone has a copy of. We had to customise the Open and Save option in Word as far too many deletion was happening. This works perfectly in Word 2000, but in 2003 the customised Open option just hangs.

The below code refers to the customised open, I've tried to catch out the error by remming out the error handling 'end nows' but there doesn't seem to be an error.

The only differences I've found is in the References from 2000 to 2003

Office 2000 Refs
VB for Apps
MS Word 9.0 OL
OLE Automation
MS Forms 2.0 OL
MS Office 9.0 OL
MS Oulook 9.0 OL
MS Shell Controls and Auto

Office 2003 Refs
VB for Apps
MS Word 11.0 OL
OLE Automation
MS Forms 2.0
MS Office 11.0 OL
MS Outlook 11.0 OL
MS Shell Controls and auto

Is the ref version difference causeing the issue?

Code:

---

Dim UserCancelled As Boolean, InvalidSelection As Boolean
Dim strUsersFolder As String, strLastFolder As String
Dim mySelectedFile As String 'folder/file selected by user in ShowFolderList
Dim MyFolder As Integer 'used to store user's default folder based on their Word username
Dim strDocType As String

Const myWork As String = "K:\"
Const myHome As String = "H:\"

Private Sub CommandButton1_Click()
    MsgBox "If your folder can't be found, ensure that your user name is set up in Word." & Chr(13) & Chr(13) & "Tools/Options/User Information/User Name should be your name (Firstname Surname).", vbOKOnly
End Sub

Private Sub labChooseFolderH_Click()
    ChooseFolderH
End Sub

Private Sub labChooseFolderK_Click()
    ChooseFolderK
End Sub

Private Sub labChooseFolderLast_Click()
    ChooseFolderLast
End Sub

Private Sub labChooseFolderMy_Click()
    ChooseFolderMy
End Sub

Private Sub UserForm_Initialize()
On Error GoTo ENDNOW
    'Get last selected folder 'set by ShowFolderList
    strLastFolder = Options.DefaultFilePath(path:=wdDocumentsPath)
    'Get username
    strUsersFolder = Word.Application.UserName
    'Update username to match folder name for user and use as default folder
    Dim myLen, FName, SName
    myLen = InStr(1, strUsersFolder, " ")
    FName = Mid(strUsersFolder, 1, myLen)
    SName = Mid(strUsersFolder, myLen)
    strUsersFolder = myWork & LTrim(SName) & ", " & FName 'used by ShowFolderList routine
   
Exit Sub
ENDNOW:
End Sub

Private Sub btnCancel_Click()
    Unload Me
End Sub

Sub ShowFolderList()
On Error GoTo ENDNOW
'Set a reference to the "Microsoft Shell Controls And Automation" library

Dim SH As Shell32.Shell
Dim Fldr As Shell32.Folder2
Set SH = New Shell32.Shell
Dim myTitle As String, myRoot As String, chkFileType

myTitle = "Double click to open each folder and choose OK to open Word Document"

    If MyFolder = 1 Then 'user's main folder
        myRoot = strUsersFolder
    ElseIf MyFolder = 2 Then 'user's last selected folder
        myRoot = strLastFolder
    ElseIf MyFolder = 3 Then 'K drive
        myRoot = myWork
    ElseIf MyFolder = 4 Then 'H drive
        myRoot = myHome
    End If
       
    'Runtime Error 5 will occur in Win 2000 if txt or rtf are opened on the root. Trapped below
    On Error Resume Next
    Set Fldr = SH.BrowseForFolder(0, myTitle, 16384, myRoot)
    If Err.Number <> 0 Then
        If Err.Number = 5 Then 'txt or rtf was selected on the root
            MsgBox "Not possible to open this file type in the root folder"
            UserCancelled = False
            InvalidSelection = True
            Me.Enabled = True
            GoTo ReleaseObjs
        End If
    End If
   
    'No Runtime Error
    If Fldr Is Nothing Then
        'User chose Cancel
        UserCancelled = True
    Else
        UserCancelled = False
        If Fldr.Self.IsFolder = True Then 'User has not selected a file
            InvalidSelection = True
            MsgBox "You have not chosen a document"
            Me.Enabled = True
            GoTo ReleaseObjs


        Else 'User has selected a file
            mySelectedFile = Fldr.Self.path
            'Validate File Type
            chkFileType = InStr(1, mySelectedFile, ".doc") '.doc
            If chkFileType = 0 Then
                chkFileType = InStr(1, mySelectedFile, ".DOC") '.DOC 'post save/open version 1 files 08/10/08
                If chkFileType = 0 Then
                    chkFileType = InStr(1, mySelectedFile, ".txt") '.txt
                    If chkFileType = 0 Then
                        chkFileType = InStr(1, mySelectedFile, ".rtf") '.rtf
                        If chkFileType = 0 Then
                            '<> .doc, .txt, .rtf
                            InvalidSelection = True
                            MsgBox "Can not open file"
                            Me.Enabled = True
                            GoTo ReleaseObjs
                        Else
                            InvalidSelection = False '.rtf ok
                        End If
                    Else
                        InvalidSelection = False '.txt ok
                    End If
                Else
                    InvalidSelection = False '.doc ok
                End If
            Else
                InvalidSelection = False '.DOC ok
            End If
        End If
       
        'Set filepath as last selected location
        Dim myLen, myChar
        myLen = 1
        While myLen > 0
            myChar = myLen
            myLen = InStr(myLen + 1, mySelectedFile, "\")
        Wend
        strLastFolder = Mid(mySelectedFile, 1, myChar)
        Options.DefaultFilePath(path:=wdDocumentsPath) = strLastFolder

    End If

ReleaseObjs:
Set SH = Nothing
Set Fldr = Nothing

Exit Sub
ENDNOW:
'Error trap some file types, e.g. bas etc.
MsgBox "Can not open file", vbExclamation, "DLS"
InvalidSelection = True
Me.Enabled = True
Word.Application.Activate
End Sub

Private Sub Userform_QueryClose(Cancel As Integer, closemode As Integer)
    If closemode = vbFormControlMenu Then
        Cancel = True
    End If
End Sub

Public Function FileFolderExists(strFullPath As String) As Boolean
    'Check if a file or folder exists
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

Private Sub ChooseFolderMy()
On Error GoTo ENDNOW
    If FileFolderExists(strUsersFolder) Then
        MyFolder = 1
        Me.Enabled = False
        ShowFolderList
        If UserCancelled = True Or InvalidSelection = True Then 'User cancelled BrowseForFOlder or did not select a file
            Me.Enabled = True
            Word.Application.Activate
            Exit Sub
        End If
        OpenMyFile
    Else
        MsgBox "Folder does not exist!  Ensure that your user name is set up in Word." & Chr(13) & Chr(13) & "Tools/Options/User Information/User Name should be your name (Firstname Surname)"
    End If
Exit Sub
ENDNOW:
End Sub

Private Sub ChooseFolderLast()
On Error GoTo ENDNOW
    If FileFolderExists(strLastFolder) Then
        MyFolder = 2
        Me.Enabled = False
        ShowFolderList
        If UserCancelled = True Or InvalidSelection = True Then 'User cancelled BrowseForFOlder or did not select a file
            Me.Enabled = True
            Word.Application.Activate
            Exit Sub
        End If
        OpenMyFile
    Else
        MsgBox "Folder can not be located!"
    End If

Exit Sub
ENDNOW:
End Sub

Private Sub ChooseFolderK()
On Error GoTo ENDNOW
    If FileFolderExists(myWork) Then
        MyFolder = 3
        Me.Enabled = False
        ShowFolderList
        If UserCancelled = True Or InvalidSelection = True Then 'User cancelled BrowseForFOlder or did not select a file
            Me.Enabled = True
            Word.Application.Activate
            Exit Sub
        End If
        OpenMyFile
    Else
        MsgBox "Drive can not be located!"
    End If
Exit Sub
ENDNOW:
End Sub

Private Sub ChooseFolderH()
On Error GoTo ENDNOW
    If FileFolderExists(myHome) Then
        MyFolder = 4
        Me.Enabled = False
        ShowFolderList
        If UserCancelled = True Or InvalidSelection = True Then 'User cancelled BrowseForFOlder or did not select a file
            Me.Enabled = True
            Word.Application.Activate
            Exit Sub
        End If
        OpenMyFile
    Else
        MsgBox "Drive can not be located!"
    End If
Exit Sub
ENDNOW:
End Sub

Private Sub OpenMyFile()
On Error GoTo ENDNOW
   
    Documents.Open mySelectedFile
    Unload Me
    'Set Word focus
    Word.Application.Activate
    End

Exit Sub
ENDNOW:
End 'kill if any other non trapped errors occur opening file
End Sub

---

the different version numbers in references are just for the different versions of word

it is more likely to do with different file paths when word is setup or the users home folder in xp

Thanks Westconn,

Although I'm not entirly sure what you mean by the Filepaths when word is setup?

Do you mean a setting within Word 03 that will allow it to pick up the NTFS file convention?

i meant paths to word default folders

put
msgbox strusersfolder
to see if it returns the correct path

i am assuming the code is failing in userform initialize event?

some proper error handling would have to help, just going to end now is no better than on error resume next, should at least have an error number an description
msgbox err.number & vbnewline & err.description

Quote:

---

Do you mean a setting within Word 03 that will allow it to pick up the NTFS file convention?

---

not sure of your meaning here, but word has no problem with ntfs filesystem

are you showing the userform from the document open event? what code is in it?

Hey Westconn,

Sorry for the late reply.

Quote:

---

put
msgbox strusersfolder
to see if it returns the correct path

---

When I've tested the script I use the step into function and it shows the correct file path.

Quote:

---

i am assuming the code is failing in userform initialize event?

---

No, initialize works fine, it seems to skip past some code (of which im yet to find out) but when it eventually get to the Drive locations (K: H: or Last selection Folder) it just hangs, doesn't unload and activate the document, it skips Sub Openmyfile.

Quote:

---

some proper error handling would have to help, just going to end now is no better than on error resume next, should at least have an error number an description
msgbox err.number & vbnewline & err.description

---

Yeah once I know what some of the errors are I'll log them better then original auther of this code.

Quote:

---

are you showing the userform from the document open event? what code is in it?

---

Once the Form initialises, the rest is on click. The form is mented to replace Words Original open interface, to restrict the delete and rename ability.

take out the on error resume next
handle the case of files in root by conditional code rather than by error
then you can see any errors, rather than not knowing what errors are resuming next

Finally trapped the error:

Quote:

---

Runtime error 2147024894 (80070002)
Automation Error
The System Cannot find the file specified

---

It's odd though, we use the same code for a save form we designed and it uses the same shell32 automation and it finds the value (Which is the folder we've specified to save in)

which part of the code generates this error?

It displays the error at "Set Fldr = SH.BrowseForFolder(0, myTitle, 16384, myRoot)"

Code:

---

Sub ShowFolderList()
On Error GoTo ENDNOW
'Set a reference to the "Microsoft Shell Controls And Automation" library

Dim SH As Shell32.Shell
Dim Fldr As Shell32.Folder2
Set SH = New Shell32.Shell
Dim myTitle As String, myRoot As String, chkFileType

myTitle = "Double click to open each folder and choose OK to open Word Document"

    If MyFolder = 1 Then 'user's main folder
        myRoot = strUsersFolder
    ElseIf MyFolder = 2 Then 'user's last selected folder
        myRoot = strLastFolder
    ElseIf MyFolder = 3 Then 'K drive
        myRoot = myWork
    ElseIf MyFolder = 4 Then 'H drive
        myRoot = myHome
    End If
       
    'Runtime Error 5 will occur in Win 2000 if txt or rtf are opened on the root. Trapped below
    'On Error Resume Next
    Set Fldr = SH.BrowseForFolder(0, myTitle, 16384, myRoot)

---

what value has myRoot when error occurs?
though i don't see it should matter unless maybe the user does not have permissions for the selected folder
i get no error when i test the code in xp machine

myRoot value is correct "myroot = "K:\". but at the fldr shows 'Fldr = Nothing' not sure why.

The code runs untill I get to select a file from my chosen drive location, myroot shows the correct path, but fldr doesn't pick up the file selected, I think I'm missing something.

I've checked the permissions and everything is fine, I'm set as administrator with full access to all drives.

is the browseforfolderdialog showing?
does it show for other drives?

do you have reference to shell32 automation set in word 2003?
edit: reread first post can see you do

Yeah the Browse for folder shows, but its limited by design to only show the one drive. Once you select the file you wish to open it then prompts the error.

Hey Westconn, sorry to bug you, is the script still working fine at your end? The only other option is I contact our Network administrators and ask them if they have placed any restrictions on shell32.dll. I've noticed they're going for a complete lock down, no access even to the basics of XP.

Do you think this could be the reason behind it?

i am not sure if they can limit the extent of shell32, beyoond the permissions to each folder but even so the return from the browse should still be ok, even if they can not write to the folder selected

could you try using the API version of SHbrowseforfolder, instead of creating a shell object?

I'll give it a shot, do you have a mini example to point me in the right direction?

from allapi

vb Code:

Private Type BrowseInfo
    hWndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type
Const BIF_RETURNONLYFSDIRS = 1
Const MAX_PATH = 260
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long
Private Sub Form_Load()
    'KPD-Team 1998
    'URL: [url]http://www.allapi.net/[/url]
    '[email protected]
    Dim iNull As Integer, lpIDList As Long, lResult As Long
    Dim sPath As String, udtBI As BrowseInfo
 
    With udtBI
        'Set the owner window
        .hWndOwner = Me.hWnd
        'lstrcat appends the two strings and returns the memory address
        .lpszTitle = lstrcat("C:\", "")
        'Return only if the user selected a directory
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With
 
    'Show the 'Browse for folder' dialog
    lpIDList = SHBrowseForFolder(udtBI)
    If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        'Get the path from the IDList
        SHGetPathFromIDList lpIDList, sPath
        'free the block of memory
        CoTaskMemFree lpIDList
        iNull = InStr(sPath, vbNullChar)
        If iNull Then
            sPath = Left$(sPath, iNull - 1)
        End If
    End If
 
    MsgBox sPath
End Sub

pass 0 for hwndowner

I'm having a right mare with this, to be honest I've never used api before. never thought the switch from 2000 to 2003 was going to be so much trouble.

I understand the reason for API, as the current code is just pointing to browseforfolder, where as in this instance its the file selection thats generating the error,

We use the Exact same code for our save and it still works in word 03 and XP but as your suggesting it's whole different kettle of fish for opening

Save Code

Code:

---

Sub ShowFolderList()
'Set a reference to the "Microsoft Shell Controls And Automation" library
On Error GoTo ENDNOW

Dim SH As shell32.Shell
Dim Fldr As shell32.Folder2
Set SH = New shell32.Shell
Dim myTitle As String, myRoot As String

Me.labMainHeadingFolder.ForeColor = vbBlack
myTitle = "Double click to open each folder and choose OK to open document"

    If MyFolder = 1 Then 'User's main folder
        myRoot = strUsersFolder
    ElseIf MyFolder = 2 Then 'User's last selected folder
        myRoot = strLastFolder
    ElseIf MyFolder = 3 Then 'K drive
        myRoot = myWork
    ElseIf MyFolder = 4 Then 'H drive
        myRoot = myHome
    End If
   
    Set Fldr = SH.BrowseForFolder(0, myTitle, 0, myRoot)

    If Fldr Is Nothing Then 'Cancel selected
        'No folder selected
    Else
        'ok selected:
        Dim fs, f, f1, s, sf
        'Selected folder returned to Fldr.Self.Path
        mySelectedFolder = Fldr.Self.path
       
        'Set objects to check for subfolders
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFolder(mySelectedFolder)
        Set sf = f.SubFolders
   
        If MyFolder = 1 Or MyFolder = 3 Then 'K drive folder
            'Check the selected folder is a bottom level folder, ie it doesnt contain sub folders
            For Each f1 In sf
                s = s & f1.Name
            Next
        ElseIf MyFolder = 2 Then 'LastSelectedFolder could be K or H
            If Mid(strLastFolder, 1, 1) = "h" Or Mid(strLastFolder, 1, 1) = "H" Then
                s = ""
            Else
                'Check the selected folder is a bottom level folder, ie it doesnt contain sub folders
                For Each f1 In sf
                    s = s & f1.Name
                Next
            End If
        ElseIf MyFolder = 4 Then 'H drive folder
            s = ""
        End If
         
        If s = "" Then 'Does not contain sub folders and is valid
            'Set as last folder saved to
            Options.DefaultFilePath(path:=wdDocumentsPath) = mySelectedFolder
            GenerateFileName
        Else 'Contains sub folders and is not valid
            MsgBox "Invalid Folder!  You must select a bottom level case folder.", vbCritical
        End If
     
  End If
 
Me.Enabled = True
Word.Application.Activate

'Release objects:
Set SH = Nothing
Set Fldr = Nothing
Set fs = Nothing
Set f = Nothing
Set sf = Nothing

Exit Sub

---

why did the developers of Vb only allow the code for sh.browseforfolder for us to have to declare a new function for browseforfile or am I getting my 6's confused with my 9's?

i can not know why your shbrowseforfolder does not work, especially if it does work in some other part of your code

i do not have the same paths, file permissions or it department, screwing around locking up the system,

so i can only make suggestions, to try to help find out why the code does not work, or find some alternative

i would be pretty sure the API came first, the shell object is a wrapper for it and other APIs

Oh Sure, I'm not having a go, just alittle frustrated... sorry if I gave that impression.

Allot of people are going nuts over this issue where I work so, I'm gonna get someone to come in a take a look at the code.

Many thanks for the help anyways

Quote:

---

We use the Exact same code for our save and it still works in word 03 and XP

---

can you post that code?

Yeah sure, although the entire is too long to post so the below is just the showfolderlist, the entire code is attached.

Save Code

Code:

---

Sub ShowFolderList()
'Set a reference to the "Microsoft Shell Controls And Automation" library
On Error GoTo ENDNOW

Dim SH As shell32.Shell
Dim Fldr As shell32.Folder2
Set SH = New shell32.Shell
Dim myTitle As String, myRoot As String

Me.labMainHeadingFolder.ForeColor = vbBlack
myTitle = "Double click to open each folder and choose OK to open document"

    If MyFolder = 1 Then 'User's main folder
        myRoot = strUsersFolder
    ElseIf MyFolder = 2 Then 'User's last selected folder
        myRoot = strLastFolder
    ElseIf MyFolder = 3 Then 'K drive
        myRoot = myWork
    ElseIf MyFolder = 4 Then 'H drive
        myRoot = myHome
    End If
   
    Set Fldr = SH.BrowseForFolder(0, myTitle, 0, myRoot)

    If Fldr Is Nothing Then 'Cancel selected
        'No folder selected
    Else
        'ok selected:
        Dim fs, f, f1, s, sf
        'Selected folder returned to Fldr.Self.Path
        mySelectedFolder = Fldr.Self.path
       
        'Set objects to check for subfolders
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFolder(mySelectedFolder)
        Set sf = f.SubFolders
   
        If MyFolder = 1 Or MyFolder = 3 Then 'K drive folder
            'Check the selected folder is a bottom level folder, ie it doesnt contain sub folders
            For Each f1 In sf
                s = s & f1.Name
            Next
        ElseIf MyFolder = 2 Then 'LastSelectedFolder could be K or H
            If Mid(strLastFolder, 1, 1) = "h" Or Mid(strLastFolder, 1, 1) = "H" Then
                s = ""
            Else
                'Check the selected folder is a bottom level folder, ie it doesnt contain sub folders
                For Each f1 In sf
                    s = s & f1.Name
                Next
            End If
        ElseIf MyFolder = 4 Then 'H drive folder
            s = ""
        End If
         
        If s = "" Then 'Does not contain sub folders and is valid
            'Set as last folder saved to
            Options.DefaultFilePath(path:=wdDocumentsPath) = mySelectedFolder
            GenerateFileName
        Else 'Contains sub folders and is not valid
            MsgBox "Invalid Folder!  You must select a bottom level case folder.", vbCritical
        End If
     
  End If
 
Me.Enabled = True
Word.Application.Activate

'Release objects:
Set SH = Nothing
Set Fldr = Nothing
Set fs = Nothing
Set f = Nothing
Set sf = Nothing

---

the only thing i can see that may produce an incorrect result is your folder exists function, it could return true if there is a file with the name of the folder you are testing, this may never happen, but could, particularly if anyone saves files with no extension
you should check the file attributes, to confirm it is a directory

i would suggest, to remove all on errors, in every procedure, or better put some proper error handling, so that you can tell some error occurred and what, as it is now any error exits the sub then continues to the calling sub, with some invalid return and no warning

i can only see one place you call SH.BrowseForFolder, so do you call that procedure to save as well as to open?

Quote:

---

Originally Posted by westconn1

the only thing i can see that may produce an incorrect result is your folder exists function, it could return true if there is a file with the name of the folder you are testing, this may never happen, but could, particularly if anyone saves files with no extension
you should check the file attributes, to confirm it is a directory

---

Not sure what you mean here, there is a ref in the code to find the user info but thats only if they select the option "if Folder = 1 then 'Users main folder"

Both Codes work fine up untill the Open code gets to selecting a file rather then a folder in the Save. (if that makes any sence :confused:)

Quote:

---

i can only see one place you call SH.BrowseForFolder, so do you call that procedure to save as well as to open?

---

Yeah, the two codes where designed to work identically, when selecting a folder to save and to open from.

ahhh, now i see

you can view files in shbrowseforfolder, but not select a file, only select a folder

Aye, thats the puppy!

In Save, you can only see the folder you wish to save into, but in Open it shows you the folder and the files inside, click on a file and pop the RT Error appears.

The weird thing is, it all works like a dream on Word 2000, but not in 2003. I thought it would be as simple as changing "sh.browseforfolderfile" or "sh.browseforfile" but nope cheers Mr Gates... Grrr..... (Yeah I've lost it!)

you could probably show the application.GetSaveAsFilename to do the same thing

Quote:

---

Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

Syntax

expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

expression Required. An expression that returns an Application object.

InitialFilename Optional Variant. Specifies the suggested file name. If this argument is omitted, Microsoft Excel uses the active workbook's name.

FileFilter Optional Variant. A string specifying file filtering criteria.

This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: "Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla".

To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt".

If omitted, this argument defaults to "All Files (*.*),*.*".

FilterIndex Optional Variant. Specifies the index number of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used.

Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the default title is used.

---

I've given your suggestion a go with the below code

Found it on http://www.xcelfiles.com/comdlg.html

The only problem I found is that the content in the browse pane is editable, meaning any user can delete, rename, move. The other browse pane in browseforfolder doesn't allow for edit. we had massive problems with this before we developed the current code.

Can this app be modified through the code to lock it down?

Code:

---

Option Explicit



Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" ( _
    pOpenfilename As OPENFILENAME) As Long



Private 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



Sub Open_Comdlg32()

Dim OpenFile As OPENFILENAME

Dim lReturn As Long

Dim strFilter As String



OpenFile.lStructSize = Len(OpenFile)

'// Define your wildcard string here

'// Note we pad the strings with Chr(0)

'// This indicates an end of a string

strFilter = "(.txt)" & Chr(0) & ".txt" & ".doc" & Chr(0)



With OpenFile

    .lpstrFilter = strFilter

    .nFilterIndex = 1

    .lpstrFile = String(257, 0)

    .nMaxFile = Len(.lpstrFile) - 1

    .lpstrFileTitle = .lpstrFile

    .nMaxFileTitle = .nMaxFile

    .lpstrInitialDir = "K:\"

    .lpstrTitle = "My FileFilter Open"

    .flags = 0

End With



lReturn = GetOpenFileName(OpenFile)

    If lReturn = 0 Then

        '// User cancelled Do your thing

        MsgBox "User cancelled"

    Else

        '// Do your thing

        MsgBox "User selected" & ":=" & OpenFile.lpstrFile

        '// So to Open the File use above Format

        '// OpenFile gets part of OpenFile.lpstrFile

        '// to the left of first Chr(0)

        '// eg:

        '//

        Dim FileToOpen As String

        FileToOpen = Application.WorksheetFunction.Clean(OpenFile.lpstrFile)

        Workbooks.Open FileToOpen

    End If



End Sub

---

in that case, i can see why you like browse for folder

i don't see any advantage, in this case, to use the api when excel has the same dialog, builtin application.getopenfilename

an option i can see is to create your own, putting all the files, from selected folder into a listbox (or filelistbox if available in VBA), or for a better presentation a listview, on a userform

Many thanks for your help, I had a feeling it might be down to designing our own.

I'm having a look at Listviews now, wish me luck (i'm gonna need it!)

thanks again