How to catch silent failure of wdDialogFileSaveAs if file is locked?

Question

I created a dialog with three buttons, where the third should save a Word document (Office Pro Plus 2013, BTW) as a PDF file.

Private Sub Button_Dokument_mit_Grafik_als_PDF_speichern_Click()
    Dim Grafik As Boolean
    Grafik = Options.PrintDrawingObjects
    Options.PrintDrawingObjects = True
    With Dialogs(wdDialogFileSaveAs)
        .Format = wdFormatPDF
        ' .Format = 17 '17 = PDF
        .Show
    End With
    Options.PrintDrawingObjects = Grafik
End Sub

If the PDF exists I can choose to overwrite it, which does work in most cases.

If the PDF to be overwritten is already open, in Adobe Reader for instance, then the file isn't saved, as it is locked. I don't get any notification that the file is locked.

How can I catch this and pop up the same message that I get when saving manually within Word?

EDIT:
To explain why my question is different to others that have been answered:
I don't need to check if the file is open in Word already. I'm saving the file as a PDF not as a Word file.
I need to check if the file is open and locked in any other application, such as Adobe Reader, Edge or whatever.

This check is done by Word (and/or the OS?) already, and THIS is the event I need to catch. I don't understand why I need to catch it at all, as the result of the check if the file does exist does come up, but the result of the check if the file is locked seems to be ignored.

The VBA code behaves as if the file has been saved, but it is not, if locked by any application other than Word.

I have no clue which code snippet exactly I would need to grab from Detect whether Excel workbook is already open

Your post is OK, except that the question has been asked many, many times before. A Google search on "test if file is locked" turns up multiple "hits", including on StackOverflow. See https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open — Cindy Meister, Mar 19 '18 at 19:00
Possible duplicate of [Detect whether Excel workbook is already open](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open) — Cindy Meister, Mar 19 '18 at 19:01
Thanks, Cindy, but: I'm not sure if we're really talking about the same thing. First: I have seen so many posts in the net that Excel and Word VBA are that different, that I didn't even dare to look for Excel VBA. Second: I don't want to check if the file is open in Word, but if the file to be saved is open in a different application (in this case Adobe Reader). — myfxp, Mar 19 '18 at 23:07
Third: Word (and/or the OS itself) does this check already anyway (at least outside VBA), and I'd like to catch THIS Event inside VBA. Currently it looks as if the file would be saved, but it's not. Please note that saving a Word doc as PDF is a lot different from saving a Word doc as a Word file. Huh, answering to a comment here is quite limited in terms of number of characters and time... — myfxp, Mar 19 '18 at 23:19
We're talking about exactly the same thing... The code is neutral as to what application calls it and what application the file is open in. If the file is locked on the system, the code detects it. — Cindy Meister, Mar 20 '18 at 18:00
OK. I wasn't sure because if I rephrase the Excel related question to "Detect whether Word file is already open" it does not address my problem. The document may not even be saved as a word file already at all, it will in most cases be a freshly created and still unsaved document. Furthermore, there were so many different approaches that I had no clue which of them is the best one to use. Not to speak about how to merge it with my code in detail. The solution from @CSS does help already, but is still not the final one as it does not exactly what it should do. See my (later) response to it. — myfxp, Mar 21 '18 at 19:10
@Cindy, are you able to remove the link "This question may already have an answer here:" above? If so, I kindly ask you to do so. The word specific solution given here by @CSS ist not posted there at all, especially not in combination with the ``wdDialogFileSaveAs`` dialog and the ``.Display`` method (and exactly that was part of the problem). Furthermore, can you probably shed some light on the -1 vs. -2 button issue (see comments from @CSS and myself below)? — myfxp, Mar 30 '18 at 14:24

CSS · Accepted Answer · 2018-03-29T07:33:36.983

1

Here is what you might be looking for:

Sub SaveAsPdf()
    Dim Grafik As Boolean
    Grafik = Options.PrintDrawingObjects
    Options.PrintDrawingObjects = True

    Dim fDialog As FileDialog
    Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

    fDialog.Title = "Save a file"
    'works only in Word2016 not in word 2013; 
    'fDialog.InitialFileName = "*.pdf"
    'we can use the filterindex property instead
    fDialog.FilterIndex = 7

    If fDialog.Show = -1 Then
        Dim selectedFilePath As String
        selectedFilePath = fDialog.SelectedItems(1)
        If IsFileInUse(selectedFilePath) Then
            MsgBox "The target pdf file you are trying to save is locked or used by other application." & vbCrLf & _
            "Please close the pdf file and try again.", vbExclamation
        Else
            ActiveDocument.SaveAs2 selectedFilePath, wdFormatPDF
        End If
    End If

    Options.PrintDrawingObjects = Grafik
End Sub

Private Function IsFileInUse(ByVal filePath As String) As Boolean
    On Error Resume Next
    Open filePath For Binary Access Read Lock Read As #1
    Close #1
    IsFileInUse = IIf(Err.Number > 0, True, False)
    On Error GoTo 0
End Function

If you would like to use wdDialogFileSaveAs dialog, you can try the below code:

The Display method will display the dialog box without executing the actual functionality. You can validate the result of the display to identify the button clicked and use the execute method to execute the actual functionality.

'Save As Pdf using wdDialogFileSaveAs dialog
'However, it doesn't work as expected.
'The Display method should
Sub SaveAsPdf()
    Dim dlg As Dialog
    Dim dlgResult As Long
    Set dlg = Dialogs(wdDialogFileSaveAs)
    With dlg
        .Format = wdFormatPDF
        dlgResult = .Display
        If dlgResult = -1 Then 'user clicks save button;
            If .Name <> "" Then
                If IsFileInUse(.Name) Then
                    MsgBox "The target pdf file you are trying to save is locked or used by other application." & vbCrLf & _
"Please close the pdf file and try again.", vbExclamation
                Else
                    .Execute
                End If
            End If
        End If
    End With
End Sub

Please note that, the above code (wdDialogFileSaveAs dialog) doesn't work as expected in Word 2016 at least in my local enviornment. The Display method executes the actual functionality once the save button is clicked. Also it returns -2 as a dialog result if Save button is clicked.

edited Mar 29 '18 at 07:33

answered Mar 20 '18 at 07:23

CSS

154
1
2
12

Thanks so much, @CSS. But unfortunately, the code doesn't do exactly what it should do. The thing with _fDialog.InitialFileName = "C:\temp\1.pdf"_ doesn't work as intended (a file name _1.docx_ and the file type _*.docx_ are preselected instead). And even if it would work, it's not exactly what I need. The file name field should be empty, the file type should be *.pdf, and the directory shouldn't be _C:\temp_. Nonetheless, the function itself works, although the details of the code still escape me (I'm simply not familiar enough with VBA). I'll try to fiddle around myself a bit with this. – myfxp Mar 21 '18 at 19:43
Tried this and that, but haven't been successful. Also, I still don't understand why an own function is required at all. If I save a document in Word as PDF manually, it is automatically detected as being locked. Why not when running the VBA code in my initial question? Can someone shed some light on that? – myfxp Mar 21 '18 at 20:15
You can specify the initial filename as *.pdf or you can modify as you wish. `fDialog.InitialFileName = "*.pdf"` – CSS Mar 22 '18 at 02:41
With regards to file locking issue, the method I gave is how it works. Word will not give everything as per our need, as it will change for every business case. In this case you are expecting Word should tell you if the file is locked without writing a code. If you use a unique filename (which doesn't exists) which cannot be duplicate then there is no need to check whether the file is already exists/locked. – CSS Mar 22 '18 at 02:46
No, sorry, `fDialog.InitialFileName = "*.pdf"` doesn't work. The file name then is _"*.docx"_, and the file type is _"Word-Dokument (*.docx)"_. Also, the dialog is the wrong one, (not the one that should come up when saving a Word document as PDF). Did you test your code yourself and it does work as intended? If so, with which version of Word/Office? IMO, this `wdFormatPDF` thing is missing in this part of the code. But I have no clue how to implement it. – myfxp Mar 23 '18 at 01:30
As to your comment _"Word will not give everything as per our need, as it will change for every business case. In this case you are expecting Word should tell you if the file is locked without writing a code."_ Yeah, sure - because that's exactly what Word (or Windows?) is doing when I'm doing exactly the same procedure manually **outside VBA**. **That** is what I don't understand. I mean, it even detects that the file does exist already when running the VBA code - why is no special function required for this detection, but for the locking issue it is required...? – myfxp Mar 23 '18 at 01:46
With regards to file locking issue, what I am trying to say is when Word tries to save the file they will internally check whether the file is locked or not which is not exposed to end user via VBA. So it requires a additional function to validate the same. – CSS Mar 23 '18 at 05:08
I use Word 2016. The code I gave works fine for me. Yes the dialog is different though. The default one has some additional save options for saving PDF. If the user only needs to save the file as PDF then the dialog I used in my code is enough. Also you are saying *.pdf doesn't work. What do you mean by that? Is it throws any errors? – CSS Mar 23 '18 at 05:11
Tested the code in Word 2013 and found that `fDialog.InitialFileName = "*.pdf"` doesn't work correctly. Instead of that we can use the FilterIndex property. Updated my post with changes. Please check. – CSS Mar 23 '18 at 05:23
Thanks again, @CSS, for looking after it, and sorry for late reply. Indeed, the changed code does now show PDF files only in the dialog. But the dialog itself is still not the one that should come up when saving a Word document as a PDF (and from time to time, I do need the specific options of this dialog). Is there really no way do the same as in my original code... `With Dialogs(wdDialogFileSaveAs) .Format = wdFormatPDF .Show End With` ...and by the same time checking if the file is locked? The answer to this question seems not to be that easy as @Cindy did initially state. ;) – myfxp Mar 25 '18 at 22:51
No. It is not possible to automatically check whether the file is locked. We need to write code to validate the same. – CSS Mar 26 '18 at 03:49
You are welcome :-) The "right" way to thank for a useful contribution is to upvote (arrows to the left). To mark something as "the" answer, click the checkmark to the left. This 1) gives the person "points" and 2) lets others who may have a similar requirement know that the contribution answered the question. ref: https://stackoverflow.com/help/someone-answers – CSS Mar 26 '18 at 05:11
I know how to thank for a contribution, but I wanted to wait until we're really done. ;) This is not the case yet, as my code brings up the right dialog but doesn't do the lock check, your code does the lock check but doesn't bring up the right (PDF specific) dialog. I'm still hoping for the "perfect" solution. Can't we specify that in the ``Set fDialog = Application.FileDialog(msoFileDialogSaveAs)`` statement somehow? – myfxp Mar 26 '18 at 18:37
I have t split this in two comments: I just discovered another problem with your code: if the word document is saved as a word file already, the default file name is empty and the default directory is C:\Windows\System32. But the file name should be the one of the saved file, just with the extension ".pdf" instead of ".docx", and the directory should point to the directory where the word file has been saved. – myfxp Mar 26 '18 at 19:48
That's what happens automatically when using ``wdDialogFileSaveAs``, but we're using ``msoFileDialogSaveAs`` instead. To me it looks as if this might be part of the problem. Is there really no way to use ``wdDialogFileSaveAs``? I tried that, but just replacing the string ``msoFileDialogSaveAs`` in your code with ``wdDialogFileSaveAs`` throws a runtime error (as I said, I'm a VBA noob). – myfxp Mar 26 '18 at 19:48
Also, in the meantime I'm aware that we "have to write code" ;), I wasn't expecting (anymore) that it works automatically. My question six comments above was more meant how to combine "my" initial code with your function ``IsFileInUse`` so that it will work as expected. Nonetheless, it still somewhat escapes me that Word checks the existence of a file (= no VBA code needed), but not if the file is locked (= VBA code needed). When doing the same manually via Backstage, it does **both** things automatically. If VBA code would be needed for **both** things, this would appear more logical to me. – myfxp Mar 26 '18 at 20:03
I have updated my post with code which uses wdDialogFileSaveAs dialog. However, it doesn't work as expected. That's why I have suggested using msoFileDialogSaveAs. – CSS Mar 27 '18 at 03:25
Thanks, @CSS, I'll check this on Word 2013. At least an attempt that might help. – myfxp Mar 27 '18 at 11:35
Your code is working, @CSS :). You're just checking the wrong button with ``If dlgResult = -2``, this should read ``If dlgResult = -1`` instead. Furthermore, there's a logic error - if the file is in use, ``.Execute`` is not the right action to be taken. ;) If I change the code as shown below, then everything appears to be working (still have to do some testing in detail, though). But anyway, currently it looks as if this would indeed be the perfect solution I was looking for. – myfxp Mar 27 '18 at 19:07
Thanks a lot for this! It's a bit embarrassing that I didn't get the idea to use the ``.Display`` method myself, although I did read about it somewhere before - I just had no clue how to interpret the result of the dialog without instantly executing it. – myfxp Mar 27 '18 at 19:09
Interesting. In 2016 version, I am getting -2 when save button is clicked and the actual functionality is getting executed even if we set the break point on `If dlgResult = -2 Then`. So, you must be careful while you upgrade your office version. Or it might be an issue with my environment. Add some reminder notes in your code :) – CSS Mar 28 '18 at 03:18
In fact, I'm also getting ``-2`` if I send ``dlgResult`` to a MsgBox. Weird! Probably a VBA bug...? Anyway, please test the code with ``-1`` in Word 2016 - does it work then? BTW: You still have the logic error with ``.Execute`` in your code, please correct that before testing (see "my" Code below). – myfxp Mar 28 '18 at 15:33
Fixed the .execute logic error in my code now. Yes. I am getting -2 if the user presses save button and 0 if the user presses the cancel button. – CSS Mar 29 '18 at 07:35
Weird, but thanks again for your help. :) BTW, do you have a clue what the argument "LockAnnot" is doing? – myfxp Mar 30 '18 at 01:20

myfxp · Answer 2 · 2018-03-27T23:18:43.113

Thanks to the help of @CSS (see answer and comments above), this is the full currently working code (unless I'd still find any flaws):

Private Sub Button_Dokument_mit_Grafik_als_PDF_speichern_Click()
    Dim Grafik As Boolean
    Grafik = Options.PrintDrawingObjects
    Options.PrintDrawingObjects = True

    Dim dlg As Dialog
    Dim dlgResult As Long
    Set dlg = Dialogs(wdDialogFileSaveAs)
    With dlg
        .Format = wdFormatPDF
        dlgResult = .Display
        If dlgResult = -1 Then 'user clicked save button
            If .Name <> "" Then
                If IsFileInUse(.Name) Then
                    MsgBox "The target PDF file you are trying to save is locked or used by other application." & vbCrLf & _
                    "Please close the PDF file and try again.", vbExclamation
                Else
                    .Execute
                End If
            End If
        End If
    End With

    Options.PrintDrawingObjects = Grafik
End Sub

Private Function IsFileInUse(ByVal filePath As String) As Boolean
    On Error Resume Next
    Open filePath For Binary Access Read Lock Read As #1
    Close #1
    IsFileInUse = IIf(Err.Number > 0, True, False)
    On Error GoTo 0
End Function

Thanks to @CSS again. :)

You may want to edit your answer, though, so that it does reflect the finally working code. I've given appropriate thanks.

How to catch silent failure of wdDialogFileSaveAs if file is locked?

2 Answers2