时间戳

首页 > 解决方案 > Excel VBA将文件另存为默认文件夹中的word文档

excel - Excel VBA将文件另存为默认文件夹中的word文档

问题描述

Sub Submit_Click()

Dim wApp As Object
Dim wDoc As Object

Set wApp = CreateObject("Word.Application")
wApp.Visible = True

'Retrieves the word doc template and inserts values from the userform using bookmarks

Set wDoc = wApp.Documents.Open(Filename:="C:\Users\Documents\template1.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

'set the default filename

ProposedFileName = Format(Now(), "DD-MMM-YYYY") & "Serial Number" & " " & TextBox1.Value _
& " " & TextBox2.Value & "- RMA" & ".docx"

'trying to save file back to .doc instead of the default .xlms format

Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
    .FilterIndex = 2
    .InitialFileName = ProposedFileName
    If .Show Then
        ActiveDocument.SaveAs2 Filename:=.SelectedItems(1), _
            FileFormat:=wdFormatDocumentDefault
    Else
    Call CommandButton4_Click 'cancel save
    End If
End With

Set fd = Nothing

End Sub

大家好，

我上面的脚本只是从我的用户表单中获取的部分脚本。基本情况是我的用户表单打开一个word文档模板并使用书签从excel用户表单中插入文本到文档中。

在用户表单上单击提交后，文件对话框以默认的 .xlms 打开，并且不允许我将其保存回 .doc

我多年来一直在搜索和修改我的脚本，但似乎无法做到正确。如果有人能告诉我怎么做，我将不胜感激。谢谢你。

问候，凯夫

标签： excelvbams-wordsave-as

解决方案

Private Sub SubmitButton_Click()  

'set default file name and file path
ProposedFileName = Format(Now(), "DDMMMYYYY") & " " & TextBox1.Value & "-" & TextBox2.Value & ".doc"
ProposedFilePath = "C:\Users\"

    'save the word document called by excel to a .doc format
    With wApp.FileDialog(msoFileDialogSaveAs)
      wDoc.SaveAs2 ProposedFilePath & ProposedFileName, _
      FilterIndex = 1, _
        FileFormat:=wdFormatDocument
    End With

'unloads the userforms and .doc file after the document is saved
Unload Me
wApp.Quit

'a dialog box pops up after document is saved to say where the file is saved since I was't unable to implement the browse folder option
  MsgBox "The document is saved in " & ProposedFilePath, vbOKOnly
  Cancel = False
Exit Sub

End Sub

大家好，感谢您的帮助。我已经设法用上面的代码解决了我的问题，但不幸的是不能用浏览位置对话框来解决。我希望这对需要它的每个人都有用。

但是，如果有人知道如何使用此代码实现浏览文件夹位置，对其他人来说会更好和有用。

推荐阅读