Create then save Word doc with variable name to OneDrive using Excel VBA

Question

I have code to create a new Word document then copy and paste information from Excel.

It then needs to check if a file exists with a specific filename from variables from the Excel sheet and if not then save in the user's OneDrive with that filename.

I get the Word document and the information is pasted but it hangs on .SaveAs2 giving an error message

Run-time error '-2147023170 (800706be)':
Automation Error
The remote procedure call failed.

Sub SubmitFormB()
'
' Submit Macro

Application.ScreenUpdating = True

    Range("A1:D37").Select
    Selection.Copy

    Dim objWord
    Dim objDoc
    Dim FullPath As String
    Dim olApp As Outlook.Application
    Dim OutMail As Outlook.Mailitem
    
    'sets word doc
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Add
    
    'gets PID
    Set objNetwork = CreateObject("WScript.network")
    getUserPID = objNetwork.UserName
    
    'sets Email
    Set olApp = New Outlook.Application
    Set OutMail = olApp.createitem(olMailitem)
    
    'set File Path
    FullPath = Environ("UserProfile") + "\OneDrive\" + Sheets("Form B").Range("C10").Value + " Facilities.docx"
    pathexists = Dir(FullPath)
   
   If pathexists = "" Then
       'copies details to word
        objWord.Visible = True
        objWord.Activate
        objWord.Selection.PasteSpecial DataType:=wdKeepSourceFormatting
        
        'saves word doc
        with objDoc
            .SaveAs2 (FullPath)
            .Close
        End with
    Else
        MsgBox "This file already exists at " + FullPath + " Please delete and try again"
        Exit Sub
    End If

End Sub

The debugger shows the full file path and as far as I can see it is correct. I previously had the full file path typed out and had the same issue.

Did you check to see whether Environ("UserProfile") + "\OneDrive\" exists as a path? — Tim Williams
– Tim Williams, Commented Jul 9 at 22:25
@TimWilliams yes it does, the debugger shows the full file path and as far as I can see its correct. I'd previously had the full file path typed out and had the same issue — RappaportXXX
– RappaportXXX, Commented Jul 9 at 22:53
You don't seem to be doing anything with the mail item created by the code. — Michal
– Michal, Commented Jul 9 at 23:01
There's another slice of code after that to added open an email and attach the saved document. I've missed that from the code when ive copied it over. — RappaportXXX
– RappaportXXX, Commented Jul 9 at 23:09

Michal · Accepted Answer · 2025-07-09 23:14:19Z

I don't know about enough about OneDrive to authoritatively answer your question but when I ran my version of the code it saved the file in a local directory C:\Users\ROSAM\OneDrive

and not in OneDrive itself. I think you have to change your code as per my below attempt (I've cleaned up your code to the best of my abilities) and also make sure the file name does not begin with a space as OneDrive seems to dislike it.

Sub SubmitFormB()
    Dim objWord As Object
    Dim objDoc As Object
    Dim FullPath As String
    Dim olApp As Object
    Dim OutMail As Object
    Dim objNetwork As Object
    Dim getUserPID As String
    Dim pathexists As String
    Dim rngToCopy As Range
    Dim oneDrivePath As String

    On Error GoTo ErrorHandler

    ' Define the range to copy
    Set rngToCopy = Sheets("Form B").Range("A1:D37")

    ' Get OneDrive path from environment variable
    oneDrivePath = Environ("OneDriveCommercial")
    If oneDrivePath = "" Then oneDrivePath = Environ("OneDriveConsumer")
    If oneDrivePath = "" Then
        MsgBox "OneDrive path could not be found. Please ensure OneDrive is set up.", vbCritical
        Exit Sub
    End If

    ' Construct full file path
    FullPath = oneDrivePath & "\" & Sheets("Form B").Range("C10").Value & "Facilities.docx"
    pathexists = Dir(FullPath)

    ' Create Word application and document
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Add

    ' Get user PID
    Set objNetwork = CreateObject("WScript.Network")
    getUserPID = objNetwork.UserName

    ' Create Outlook email item
    Set olApp = CreateObject("Outlook.Application")
    Set OutMail = olApp.CreateItem(0) ' olMailItem = 0

    If pathexists = "" Then
        ' Copy range to clipboard
        rngToCopy.Copy

        ' Paste into Word
        objWord.Visible = True
        objWord.Activate
        objWord.Selection.PasteSpecial DataType:=2 ' wdPasteRTF = 2

        ' Save and close document
        With objDoc
            .SaveAs2 FullPath
            .Close False
        End With
    Else
        MsgBox "This file already exists at " & FullPath & vbCrLf & "Please delete and try again.", vbExclamation
        GoTo CleanUp
    End If

CleanUp:
    ' Release objects
    Set objDoc = Nothing
    Set objWord = Nothing
    Set olApp = Nothing
    Set OutMail = Nothing
    Set objNetwork = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

Thanks for this. I won't be able to try the suggested amendments until tomorrow evening. Ill let you know if I get it to work
Hiya, unfortunately this code is still hanging on the .Saveas2 function with the same error. I even changed the filepath to put it onto the desktop and still have the error. Thanks anyway

Collectives™ on Stack Overflow

Create then save Word doc with variable name to OneDrive using Excel VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related