Set wb = Workbooks.Open("C:\Temp\Documents Page XX_US-VC Combo Template. Set wb = Workbooks.Open("C:\Temp\Documents Page XX_US-VC Combo Template.xlsx")Īnd finally when we have the destination all set, simply copy the table from word to Excel. Before we copy it, let's set our Excel objects. Now we have the handle to the Word Table. Now you have the document open, Let's connect with say Table1 of the word document. When you copy a sequence, you get unformatted text. Usually IDEs (integrated development environment) decorate source programs internally and do not make explicit the mark up. "Browse for file containing table to be imported") If your pasted text has some formatting (color, font face, ), this means it is not pure unformatted text but contains some mark up. See this example Sub Sample()įlName = Application.GetOpenFilename("Word files (*.Doc*),*.Doc*",, _ Once you have connected with/created the Word instance, simply open the word file. Set oWordApp = CreateObject("Word.Application") Set oWordApp = GetObject(, "Word.Application") '~~> Establish an Word application object For example Sub Sample()ĭim oWordApp As Object, oWordDoc As Object I am using Late Binding where you do not need to add any references.ĭeclare your Word objects and then bind with either an existing instance of Word or create a new instance. To interact with Word from Excel, you can choose either Early Binding or Late Binding. ObjTemplateSheetExcelWkBk.SaveAs strFld & "\" & strFileName WorksheetFunction.Clean(.cell(iRow + 1, iCol + 1).Range.Text) ObjTemplateSheetExcelSheet.Cells(2, yourArray(arrycnt) + 1) = _ WorksheetFunction.Clean(.cell(iRow, iCol + 1).Range.Text) ObjTemplateSheetExcelSheet.Cells(2, yourArray(arrycnt)) = _ StrEach = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text) 'copy cell contents from Word table cells to Excel cells Set objTemplateSheetExcelSheet = (5) '(Selecting the desired tab) ("C:\Temp\Documents Page XX_US-VC Combo Template.xlsx") Set objTemplateSheetExcelApp = CreateObject("Excel.Application")
#Pasting text with source formatting in word code#
Ok I am attaching the remaining piece of code as well 'Creating TemplateSheet object Then for all the tables present in the word doc accessing each row and column of the tables using the above mentioned code. I am running a table count on the word file. MsgBox "This document contains no tables", _ TableNo = '(Counting no of tables in the document) Set wdDoc = GetObject(wdFileName) '(open Word file) If wdFileName = False Then Exit Sub '(user cancelled import file browser) "Browse for file containing table to be imported") '(Browsing for a file) The logic I am using is as follows - wdFileName = Application.GetOpenFilename("Word files (*.*),*.*",, _ ObjTemplateSheetExcelSheet.Cells(1, 2) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text) Kindly let me know how I can edit this so as to preserve source formatting. Now i want to preserve the source formatting present in word doc. It is copying the text perfectly as desired. Paste values only: Pastes only the text contained in the original range of cells.I am trying to copy some data from a word table to an excel sheet using a VB Macro.By default, the original formatting of the content will stay the same.Paste the content in the same spreadsheet.In a spreadsheet, copy the content you want to paste.You can format the content after you paste it. But cutting-and-pasting is a frequent source of formatting disasters in Microsoft Word. For example, if you copy a column of cells and use paste transpose, it will paste them into a row, and vice versa. If you’re like most lawyers, you recycle text from other documents a lot. Paste transposed: Pastes a rotated version of the copied cells.Paste conditional formatting only: Only applies conditional formatting rules to a range of cells.Paste data validation only: Pastes a data validation rule over a range of cells without changing existing formatting, formulas, or text.Paste formula only: Pastes the formulas contained in a copied range of cells, not the resulting calculations of the formulas.Paste column widths only: Resizes the selected columns to match the original.Paste all except borders: Pastes everything except cell borders.Paste format only: This option is identical to using the paint format tool - it only copies cell formatting, and won't change existing text or formulas.Paste values only: Pastes only the text from the original range of cells.Copy the data you want to paste and put your cursor in the cell you want to paste into.On your computer, open a spreadsheet in Google Sheets.You can't copy cells from one spreadsheet and use paste special in a spreadsheet open in another tab. The first thing you should try before anything else is the common shortcut key that is used to paste unformatted text which is Ctrl+Shift+V. Paste special works only within a single spreadsheet. You can use paste special to decide which formatting gets copied when copying cells.