Solved

vba FileDIalog box does not display Word XP

Posted on 2010-11-09
10
610 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

0
Comment
Question by:purbrick
  • 6
  • 4
10 Comments
 
LVL 17

Expert Comment

by:wobbled
ID: 34101480
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)
0
 

Author Comment

by:purbrick
ID: 34107845
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
0
 
LVL 17

Expert Comment

by:wobbled
ID: 34109910
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

0
 

Author Comment

by:purbrick
ID: 34116671
THanks, I will copy this into my code and let you know how I go.
0
 

Author Comment

by:purbrick
ID: 34117556
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

Accepted Solution

by:
wobbled earned 500 total points
ID: 34119213
Just reading your reply it is sounding like the problem is lies somewhere in the Document object.  As I haven't seen all of your code I can't be sure, but one thing that I have always tended to do is to associate each document I open/work with to a variable.  That way I don't use the ActiveDocument control which can get messed up etc.  It also means that you can destroy the variable easily and therefore remove it from memory.


e.g.

Dim DocMain as Document
Set DocMain = ActiveDocument

'all your other code here etc

Set DocMain = Nothing

Regards

W
 
0
 

Author Comment

by:purbrick
ID: 34132902
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.

0
 

Author Comment

by:purbrick
ID: 34151008
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
0
 
LVL 17

Expert Comment

by:wobbled
ID: 34153856
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!
0
 

Author Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now