excel - Having problems copying an Excel range to a Word file using VBA
问题描述
I have looked at some VBA codes on how to copy a range from Excel and paste to a Word Document, but I can't get it to work, it creates a pdf file, but the file is corrupted.
I have the following VBA code:
Sub CopyToWordAndPrintPDF()
'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'NOTE: Must have Word Object Library Active in Order to Run _
(VBE > Tools > References > Microsoft Word 1x.0 Object Library)
'Name of the existing Word document
Const stWordDocument As String = "C:\Users\SDETHBP\Documents\FCM\FCM Ulvetræning Øvelser\U7-U12\Word Forside\Forside fra Excel.docx"
'Word objects/declared variables.
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
'Excel objects
Dim wbBook As Workbook
Dim wsSheet As Worksheet
'Instantiate Word and open the "Table Reports" document.
Set wdApp = New Word.Application
'Making word App Visible
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Add(stWordDocument)
' copy content to word
ThisWorkbook.Worksheets("U7AB1").Range("A1:N24").Copy
' Pastes it to the selected Word doc template.
wdApp.Documents.Add
wdApp.Selection.Paste
' Sets your printer in Word to Adobe PDF and then prints the whole doc.
wdApp.WordBasic.FilePrintSetup Printer:="Adobe PDF", DoNotSetAsSysDefault:=1
wdApp.ActiveDocument.PrintOut
'Cleaning
Set wsCell = Nothing
Application.StatusBar = "Cleaning up..."
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub
I hope someone can guide me.
##EDIT Try:## I have tried this and that take my existing word file and fill the range from the excel sheet into it, but my watermark is then behind the "table" and I can't see it (know that I don't save as pdf yet). So atm. it's the layout that's the problem, if I select the cells/range manual, and then copy em to the word, it's fine, I can see my watermark.
Sub ExcelRangeToWord()
'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'NOTE: Must have Word Object Library Active in Order to Run _
(VBE > Tools > References > Microsoft Word 12.0 Object Library)
'SOURCE: www.TheSpreadsheetGuru.com
Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table
'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False
'Copy Range from Excel
Set tbl = ThisWorkbook.Worksheets("U7AB1").Range("A1:N24")
'Create an Instance of MS Word
On Error Resume Next
'Is MS Word already opened?
Set WordApp = GetObject(class:="Word.Application")
'Clear the error between errors
Err.Clear
'If MS Word is not already open then open MS Word
If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")
'Handle if the Word Application is not found
If Err.Number = 429 Then
MsgBox "Microsoft Word could not be found, aborting."
GoTo EndRoutine
End If
On Error GoTo 0
'Make MS Word Visible and Active
WordApp.Visible = True
WordApp.Activate
'Create a New Document
'Set myDoc = WordApp.Documents.Add
'Change: [Set myDoc = WordApp.Documents.Add] to:
Set myDoc = WordApp.Documents.Open("C:\Users\SDETHBP\Documents\FCM\FCM Ulvetræning Øvelser\U7-U12\Word Forside\Forside fra Excel.docx")
'Copy Excel Table Range
tbl.Copy
'Paste Table into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False
EndRoutine:
'Optimize Code
Application.ScreenUpdating = True
Application.EnableEvents = True
'Clear The Clipboard
Application.CutCopyMode = False
End Sub
解决方案
For example:
Sub CopyToWordAndPrintPDF()
'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'NOTE: Must have Word Object Library Active in Order to Run _
(VBE > Tools > References > Microsoft Word #.0 Object Library)
'Name of the existing Word document
Const stWordDocument As String = "C:\Users\SDETHBP\Documents\FCM\FCM Ulvetræning Øvelser\U7-U12\Word Forside\Forside fra Excel.docx"
'Word objects/declared variables.
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
.Visible = False
' Open the Word document
Set wdDoc = .Documents.Open(Filename:=stWordDocument, AddToRecentFiles:=False, Visible:=False)
' copy content to word
ThisWorkbook.Worksheets("U7AB1").Range("A1:N24").Copy
' Pastes it to the selected Word template.
With wdDoc
.Range.Characters.Last.Paste ' or, for example: .PasteExcelTable False, False, True
' Saves then prints the doc.
.SaveAs2 Filename:=Split(stWordDocument, ".doc")(0) & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
.Close False
End With
.Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
To use the workbook's name & path instead of the document's name & path for the PDF, change:
Split(stWordDocument, ".doc")(0)
to:
Split(ThisWorkbook.FullName, ".xls")(0)
推荐阅读
- ruby-on-rails - 通过 Stripe Checkout 支付后如何更改 Rails 对象字段?
- javascript - Vuejs表单在数组中获取多个值
- laravel - Laravel 策略总是返回 403
- java - 如何从 JavaFX 中的 ComboBox 中获取所选项目的数量?
- amazon-web-services - Terraform,ElasticSearch:错误:InvalidTypeException:错误设置策略
- c++ - 如何在 c++17 中使用嵌套的静态模板结构
- flutter - Flutter iOS 风格的 UIKit 动态视图动画
- android - 我应该按什么顺序调用此注销序列中的事件?
- julia - 在 Julia 中使用 `\` 而不是 `/`
- r - 如何使用 read.table 从 .rda 读取数据