excel - excel vba - open a previous saved document

Question

I am really stuck on this one and don't know where to start really. Still new to VBA.

I got a code that writes textbox values from a userform into a word document using bookmarks. This document is then saved to C:\documents after clicking submit.

Basically what I am trying to achieve is to use another command button on the userform to open that saved file based on one of the values I entered previously and amend the document when needed.

Is this even possible?

Thanks.

This is what I have that works to write to word doc from a userform when i click submit button. But I added another command button to recall that doc based on the textbox value within the userform example, Userform textbox1 value is 3. So when i click on edit command button, it recalls the doc that has that value in its content i.e. bookmark1 = 3

        '----------The Script below writes values to the document ----------------------- 
     -----------------

    Dim wApp As Object
    Dim wDoc As Object

    'We need to continue through errors since if Word isn't
    'open the GetObject line will give an error

    On Error Resume Next
    Set wApp = GetObject(, "Word.Application")


'We've tried to get Word but if it's nothing then it isn't open
If wApp Is Nothing Then
    Set wApp = CreateObject("Word.Application")
End If

'It's good practice to reset error warnings
On Error GoTo 0

'Open your document and ensure its visible and activate after openning

Set wDoc = 
wApp.Documents.Open(Filename:="C:\Documents\template.docx ", 
ReadOnly:=False)
    With wDoc
    .Bookmarks("bookmark1").Range.Text = Me.TextBox1.Value
    .Bookmarks("bookmark2").Range.Text = Me.TextBox3.Value
    .Bookmarks("bookmark3").Range.Text = Me.TextBox4.Value
    .Bookmarks("bookmark4").Range.Text = Me.TextBox5.Value
    .Bookmarks("bookmark5").Range.Text = Me.TextBox6.Value
    .Bookmarks("bookmark6").Range.Text = Me.TextBox7.Value
    .Bookmarks("bookmark7").Range.Text = Me.TextBox8.Value
    End With

wApp.Visible = True

'set default file name and file path

ProposedFileName = Format(Now(), "DDMMMYYYY") &  
TextBox1.Value & "-" & TextBox2.Value & ".doc"
ProposedFilePath = "C:\Users\Documents\"

    With wApp.FileDialog(msoFileDialogSaveAs)
    wDoc.SaveAs2 ProposedFilePath & ProposedFileName, _
    FilterIndex = 1, _
    FileFormat:=wdFormatDocument

    End With   
End Sub

On another userform has an edit button that when clicked, is to recall the saved doc from the above script. Something like below maybe?

Sub EditButton_Cick()
Set wDoc = wApp.Documents.Open(Filename:="C:\Documents\SavedDoc.docx ", 
ReadOnly:=False) 'here is where I want to open the previous saved document located in C:\Documents from the script in my initial question however it should open the` doc referenced to textbox1.value
    With wDoc
    .Bookmarks("bookmark10").Range.Text = Me.TextBox10.Value 'In the userform, I add in another value later to the saved document

   objDoc.Save

End Sub

标签： excelvbareferencems-word

Answer 1

Like this:

At the top of a regular code module:

Dim savePath As String '<< global variable for saved file path

In your first userform:

Sub cmdSave_Click()
    '...
    '...
    savePath = ProposedFilePath & ProposedFileName  '<< save to Global var

    wDoc.SaveAs2 ProposedFilePath & ProposedFileName, _
        FilterIndex = 1, _
        FileFormat:=wdFormatDocument
    '...
    '...
End Sub

In some later userform:

Sub cmdReOpenFile_Click()

    Dim doc
    '...
    Set doc = wApp.Documents.Open(savePath) '<< read from global var
    '... work with doc

End Sub

FYI it looks like you're using late binding so you will need to define any Word constants such as wdFormatDocument

If you put Option Explicit at the top of your code modules it will warn you about things like that.