Power Platform Community Forum Thread Details

Hi All,

Would like to enquire, I wanted to copy a datatable from excel & transfer it to an existing microsoft word document via vbscript. But no matter how i set, the format doesn't follow. Let's say if my excel was under Arial, font 10, during script runs and paste this datatable to word, it will change to Time New Romans font 11. Not just that, the width column would also change. Is there any possibilities that thru vbscripts i could preserve the format?

' Create Excel and Word objects

Set excelApp = CreateObject("Excel.Application")

Set wordApp = CreateObject("Word.Application")

excelApp.DisplayAlerts = False

' Open the Excel workbook

Set excelWorkbook = excelApp.Workbooks.Open("C:\Users\....\Masterlisted.xlsx")

Set excelSheet = excelWorkbook.Sheets(1)

' Select all the data in the used range

excelSheet.UsedRange.Copy ' Copies with formatting

' Open the Word document

Set wordDocument = wordApp.Documents.Open("C:\Users\.....\International A2P Blocking List (test.docx")

' Make Word visible

wordApp.Visible = True

' Find the last free space in the Word document

Set wordRange = wordDocument.Content

wordRange.Collapse 0 ' Collapse to the end of the document

' Paste the data with "Keep Source Formatting"

' Equivalent of keeping source formatting is just using Paste (Word usually retains Excel formatting)

wordRange.Paste

' Clean up

excelWorkbook.Close

excelApp.Quit

wordDocument.Close

wordApp.Quit

Set excelSheet = Nothing

Set excelWorkbook = Nothing

Set excelApp = Nothing

Set wordRange = Nothing

Set wordDocument = Nothing

Set wordApp = Nothing

Categories:

Power Automate Desktop

Hey Hi,

Try this below Vbscript code.

' Create Excel and Word objects
Set excelApp = CreateObject("Excel.Application")
Set wordApp = CreateObject("Word.Application")

excelApp.DisplayAlerts = False 

' Open the Excel workbook
Set excelWorkbook = excelApp.Workbooks.Open("C:\Users\....\Masterlisted.xlsx")
Set excelSheet = excelWorkbook.Sheets(1)

' Select all the data in the used range
excelSheet.UsedRange.Copy ' Copies with formatting

' Open the Word document
Set wordDocument = wordApp.Documents.Open("C:\Users\.....\International A2P Blocking List (test.docx")

' Make Word visible
wordApp.Visible = True

' Find the last free space in the Word document
Set wordRange = wordDocument.Content
wordRange.Collapse 0 ' Collapse to the end of the document

' Paste with "Keep Source Formatting" using Word's built-in PasteSpecial function
wordRange.PasteSpecial DataType:=12 ' DataType 12 is wdPasteHTML, which retains formatting

' You could also try this for an embedded Excel object:
' wordRange.PasteSpecial DataType:=1 ' DataType 1 is wdPasteOLEObject

' Clean up
excelWorkbook.Close False
excelApp.Quit
wordDocument.Save ' Save Word document
wordDocument.Close False
wordApp.Quit

' Release objects
Set excelSheet = Nothing
Set excelWorkbook = Nothing
Set excelApp = Nothing
Set wordRange = Nothing
Set wordDocument = Nothing
Set wordApp = Nothing

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy