We help IT Professionals succeed at work.

vba FileDIalog box does not display Word XP

632 Views
Last Modified: 2012-05-10
We are using WordXP vba I am using the filedialog with msoFileDialogFilePicker to prompt the user for the name/location of a file.  the code runs OK twice but if you run the code a third time it skips over the If .Show = -1 Then and executes the code in the else portion of the statement.  It is as if the user pressed cancel but the dialog box is never displayed.

The code is run from a template.  If the code is run from the template when the template file is open no error occurs, the error only occurs when running the macros loaded through Templates and Add-ins.

Any thoughts?  

Dim dlgOpen As FileDialog
sFileName = ""
set dlgOpen = Application.FileDialog FileDialogType:=msoFileDialogFilePicker)
        With dlgOpen
            .Filters.Clear
            .InitialFileName = sQualityPath
            .Filters.Add Description:="All files (*.*)", Extensions:="*.*"
            .AllowMultiSelect = False
            .title = "Select EXISTING quality document"
            If .Show = -1 Then
                sFileName = .SelectedItems(1)
                'extract filename from pathname
                sFileName = UCase(Mid(sFileName, Len(sQualityPath) + 1))
                '.SelectedItems(1) = ""
                Set dlgOpen = Nothing
            Else
                Beep
                MsgBox "No file selected", vbInformation, "Creat draft of next version of quality document"
                Set dlgOpen = Nothing
                Main
            End If
        End With

Open in new window

Comment
Watch Question

Commented:
you need to add the Set dlgOpen = Nothing outside of the With Statement.  Currently you are still accessing this variable within the With so you can't destroy it.

Hopefully that should remove it from memory and this problem should disappear (famous last words)
Jenny CoulthardInformation Technology Manager

Author

Commented:
Hey Wobbled, when I read your response, I thought - yes, how silly of me why didnt I see that.  I have made the change and checked all other set statements but the error is still occuring.  I thought this was the answer, I tried to expand the error messages to see if could get more information but simply get error number of 0, i do get an 'there is not enough memory or disk space' error when using another filedialog box but cannot work out why

Commented:
I have looked at your code and made a few changes.  I've run this many times on my PC and I am not getting the errors that you say you do so give it a go.

I have made a few changes - I now use a function SelectAFile to open the dialog box, once completed the function closes and any memory associated to items within the function should be removed.
I also slightly changed how you were setting the fileDialog to the variable.

I have gone a bit overkill on the file select, by looping through any selected items even though you have set multiselct to false, but this way you can easily change it to true and modify the loop in the future if you wish
Public Function SelectAFile() As String

Dim fd As FileDialog
Dim vrtSelectedItem As Variant

    SelectAFile = vbNullString
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        
        .Filters.Add Description:="All files (*.*)", Extensions:="*.*"
        .AllowMultiSelect = False
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                SelectAFile = vrtSelectedItem
            Next
            SelectAFile = UCase(Mid(SelectAFile, Len(sQualityPath) + 1))
        Else
            SelectAFile = vbNullString      'If the user presses Cancel...
        End If
    End With
    
    Set fd = Nothing

End Function

Sub GetFile()
Dim strFile As String

    strFile = SelectAFile

    If strFile <> vbNullString Then
        'do what you want with the file name etc
        'call main
        MsgBox strFile
    Else
         Beep
         MsgBox "No file selected", vbInformation, "Creat draft of next version of quality document"
    End If
    
End Sub

Open in new window

Jenny CoulthardInformation Technology Manager

Author

Commented:
THanks, I will copy this into my code and let you know how I go.
Jenny CoulthardInformation Technology Manager

Author

Commented:
OK, so your code works and if i strip down my code to only use your functions it works, I then progressivelly added back in the remaining code to get full functionality and have determined that something is occuring further down but havent been able to get a consistant enough result to pinpoint the area.
Something which I noticed but havent been able to determine why/what.  With the final version of the code, if you leave the last file created by the macro open then it works every time.  The error occurs if you manuallu close the file opened by the macro, and then restart the macro.  Not really sure what this means.

I changed the code again, thinking it may have been caused by the open word document so created teh document then closed in the macro.  Error now occurs on the second run.  Not really sure what is going on or if worth persuing.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jenny CoulthardInformation Technology Manager

Author

Commented:
I had the same thought and removed the activeDocument and ActiveWindow code but no change.  After reading your response I went back and checked for other instanaces of ActiveDocument which I found in a function.  I removed this as well but still no change.  There are other instances of ActiveDocument but I didnt worry about those as not part of the code which is being run.

The fact that the error doesnt occur as long as one of the window doccuments is open is interesting and probably the key but I cant work out why.

Jenny CoulthardInformation Technology Manager

Author

Commented:
I have accepted wobbled suggestions as a solution as have allowed me to at least identify the area of the problem and provide a workaround to the client until I have resolved what is going on

Commented:
Thank you for the points.  Hope that you are able to track down the exact cause of the issue, I know how difficult these things can be and often how simple the solution is!
Jenny CoulthardInformation Technology Manager

Author

Commented:
I hope so too, will leave for a while and come back and look at it with fresh eyes, often that can help

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.