Consulting

Results 1 to 9 of 9

Thread: Error when using .SaveAs2 - but file still saves

  1. #1
    VBAX Newbie
    Joined
    Dec 2009
    Posts
    5
    Location

    Error when using .SaveAs2 - but file still saves

    Hi,

    Scenario: Word macro enabled documents live in SharePoint Document Library. User clicks .docm and the macro starts to run. It prompts the user for a file name and for a folder. Then the file is saved as a new document without macros with the file name and path provided.

    Users have virtual machines. They don't have access to a C:\ drive. Their home drive with their profiles, "My Documents" and all that is on a server drive, the H:\ drive.

    The problem: The SaveAs2 command triggers an error dialog on the screen "H:\dump\filename.docx" cannot be found. Check your spelling or try a different path."

    Well, of course it can't be found. I'm trying to save it as a new file. That's the whole point.

    The odd bit is that after displaying the message, Word goes ahead and saves the file in the folder, anyway.

    I've tried 'On Error Resume Next' and I tried 'Application.DisplayAlerts = wdAlertsNone' but the message still displays.

    Any idea what I can do to make the message go away? Just suppressing it would suffice, since the saving actually happens.

    Here's the code.

    Private Sub Document_Open()
    Dim fd As FileDialog
    Dim strFolder As String
    Dim strDoc As String
    Dim fullPath As String
    Dim o As Document
    Set o = ActiveDocument
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    
    ' the original code uses a sub in a code module to get the user name.
    ' For the purpose of this demo, I will hard wire a value
    
    
    Dim userName As String
    userName = "TheUser"
    ' strDoc = Left(o.Name, 18) & "-" & GetUserName & "-" & Format(Now, "yyyy-mm-dd-hhMMss")
    strDoc = Left(o.Name, 18) & "-" & userName & "-" & Format(Now, "yyyy-mm-dd-hhMMss")
    
    
    UserForm1.frmFileName.Value = strDoc
    ' allow the user to change the file name
    UserForm1.Show vbModal
    strDoc = UserForm1.frmFileName.Value
    If strDoc = "SecretPassword" Then Exit Sub ' allow Power Users a way out
    
    
    GetFolder:
    With fd
        .Title = "Select a folder to store the draft contract"
        .InitialFileName = "H:\"
        If .Show = -1 Then
            strFolder = .SelectedItems(1) & "\"
        Else
            MsgBox "Sorry, you cannot cancel at this stage. Please select a folder."
            GoTo GetFolder
        End If
    End With
    fullPath = strFolder & strDoc & ".docx"
    ' MsgBox fullPath
    
    
    With o
    '    .SaveAs strFolder & strDoc & ".docx", wdFormatDocumentDefault
        .SaveAs strFolder & strDoc & ".docx"
    End With
    MsgBox "The document will now close to remove macro code. Please reopen from you recent files list.", vbOKOnly
    With o
        .Close
    End With
    End Sub
    Windows 7 Enterprise Version 6.1 Build 7601 Service Pack 1
    Office Professional Plus 2010 version 14.0.6129.5000

    Edit: I just noticed that the error message does not come up when the original .docm is opened via File > Recent. But if I open the file by clicking it in the SharePoint document library, I get the error. It is exactly the same file and it is in the exact same location. Go figure.

    Another Edit: I just noticed that the error message does not come up when I open the file in Edit mode. I have permissions to edit files in the document library. I can select if I want to open the file in read mode or in edit mode. Read mode will be used by the common user as the default. Why is there a difference when I use "save as" whether or not the file is in read mode?


    Thanks for looking,

    cheers, teylyn
    Last edited by teylyn; 04-09-2014 at 06:53 PM.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,657
    What happens if you use:

    Sub M_snb()
      With Application.FileDialog(msoFileDialogSaveAs)
        .InitialFileName = "G:\OF\teylyn 001.docx"
        .Show
        c00 = .SelectedItems(1)
      End With
    End Sub

  3. #3
    VBAX Newbie
    Joined
    Dec 2009
    Posts
    5
    Location
    Hi snb,

    I'll try that when I'm back in the office tomorrow. In the meantime, can you explain the components of the command? I gather that the .Show shows the dialog ( I don' want another dialog, actually). What does the c00 do?

    cheers, teylyn

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,657
    My suggestion is not to use any userform in this case. The dialog serves the same purpose.
    c00 is the variable that stores the chosen path & name, so you can use it to save the file. If you think that is too prolix:

    Sub M_snb()
        With Application.FileDialog(msoFileDialogSaveAs)
            .InitialFileName = "G:\OF\teylyn 001.docx"
            .Show
            .Execute
        End With
    End Sub

  5. #5
    VBAX Newbie
    Joined
    Dec 2009
    Posts
    5
    Location
    Nope, that does not make a difference. Still get the warning "Path\filename cannot be found. Check your spelling, or try a different path." After I click OK on the message, the file gets saved, just as it does with my original code.

  6. #6
    VBAX Newbie
    Joined
    Dec 2009
    Posts
    5
    Location
    I ended up with this technique

    - before using .SaveAs, create a new, empty document
    - get the user's temp folder with environ("Temp")
    - write the document to the temp folder, with the file name specified by the user
    - use FileCopy to copy the file from the temp folder to the previously specified folder
    - KillFile in the temp folder
    - Now the file exists in the desired destination folder and .SaveAs does not trigger the message.

    cheers, teylyn

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,657
    In that case it might be practical to create a file in the desired location with the desired name using this VBA code:

    Sub M_snb()
        Open "G:\OF\dummy.docx" For Output As #1
        Close
        activedocument.saveas2 "G:\OF\dummy.docx"
    End Sub

  8. #8
    VBAX Newbie
    Joined
    Dec 2009
    Posts
    5
    Location
    Thanks for your suggestion. Have you tried that with a .docm that is stored in a SharePoint document library and opened in read mode? Can you confirm that it works in that scenario?

    If not, please don't post any more suggestions.

  9. #9
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,657
    So did you test it in your environment ?
    What was the result ?
    It's the OP who can do the testing. Helpers in a forum can't as you might be aware of.

Posting Permissions

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