Insert an Excel Worksheet into a Word Document: Instructions. To insert an Excel worksheet into a Word document, place the insertion marker into the document location where you want to show the worksheet data. Then click the “Table” button within the “Tables” button group on the “Insert” tab within the Ribbon. If the table has less rows than the array, the table doesnt reisze and so the last rows of the array are not copied into excel. If the table has more rows than the array, the rows are repeated in the table. So, I need to resize the table to fit the array and, after that, copy the array to the table. No missing data and no surplus data.
![]()
Insert an Excel Worksheet into a Word Document: OverviewYou can easily insert an Excel worksheet into a Word document to harness the power of Excel within Word. This helps Word users who like creating worksheets and performing calculations in Microsoft Excel. To insert an Excel worksheet into a Word document, you must have both Microsoft Word and Microsoft Excel installed on your computer as part of the same Microsoft Office installation. After you insert an Excel worksheet into a Word document, you can then edit the information in the worksheet using the Excel interface.
However, it displays the data in the Word document as if it is a table.To insert an Excel worksheet into a Word document, place the insertion marker into the document location where you want to show the worksheet data. Then click the “Table” button within the “Tables” button group on the “Insert” tab within the Ribbon. Then click the “Excel Spreadsheet” command in the button’s drop-down menu. Doing this then inserts an Excel worksheet object into that location in the document.Insert an Excel Worksheet into a Word Document – Tutorial: A picture of a user inserting an Excel spreadsheet into a Word document.At that point, you are working within the Excel interface. Edit the contents of the cells in the Excel worksheet area. Also, apply all formatting to the cells in Excel, as you cannot make any formatting changes in Word.Note that the embedded Excel worksheet object has a dashed border around it.
![]()
Only the information shown in this area appears in the document. To resize this display area to increase the data shown or remove extra cells from appearing, place your mouse pointer over the small black resizing squares that appear around the perimeter of the embedded worksheet object until your mouse pointer turns into a double-pointed arrow. Then click and drag to increase or decrease the size of the worksheet area that appears in the document.After entering the worksheet data and resizing its display, then click into the document area.
Doing this then changes the display of the worksheet to a table. To edit its contents again in the future or to change its display size, you must return to editing mode.
To return to editing mode, double-click the embedded Excel worksheet object in the document. It then switches back to Excel so you can make the necessary changes. Then click back into the document after editing it to change it back into an embedded table within Word.To delete an embedded Excel worksheet object in your document, simply click it to select it. Then press the “Delete” or “Del” key on your keyboard to delete the Excel worksheet.
Tables are one of the most powerful features of Excel. Being able to automate them using VBA provides a way to make Tables even more powerful. Creating and converting TablesThis first set of macros is about creating and resizing macros. Convert selection to Excel TableThis macro will create a Table based on the currently selected region and name it “myTable” Sub ConvertRangeToTableActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Selection.CurrentRegion, xlListObjectHasHeaders:=xlYes ).Name = 'myTable'End Sub Convert Excel Table back to RangeThis macro will convert a Table back to a normal range. Sub ConvertTableToRangeActiveSheet.ListObjects('myTable').UnlistEnd Sub Resize the range of the tableTo following macro will resize a Table to Cell A1 – J100 Sub ResizeTableRangeActiveSheet.ListObjects('myTable').Resize Range('$A$1:$J$100')End Sub Table StylesThere are many formatting options, which can be used with Tables. The most common of which are shown below. Change Table StyleChange the style of a Table to an existing pre-defined style.
Sub ChangeTableStyleActiveSheet.ListObjects('myTable').TableStyle = 'TableStyleLight15'End SubTo apply different Table styles, use the macro recorder to find the name of a style. Apply style to first or last columnThe first and last columns of a Table can be formatted differently using the following macros. Sub ColumnStyles'Apply special style to first columnActiveSheet.ListObjects('myTable').ShowTableStyleFirstColumn = True 'Apply special style to last columnActiveSheet.ListObjects('myTable').ShowTableStyleLastColumn = True End SubSome alternative codes you could use are:Remove special style from first column ActiveSheet.ListObjects('myTable').ShowTableStyleFirstColumn = FalseRemove special style from last column ActiveSheet.ListObjects('myTable').ShowTableStyleLastColumn = False Adding or removing stripesTables can have a banded rows and or columns. Sub ChangeStripes'Apply column stripesActiveSheet.ListObjects('myTable').ShowTableStyleColumnStripes = True'Remove row stripesActiveSheet.ListObjects('myTable').ShowTableStyleRowStripes = False End SubSome alternative codes you could use are:Remove column stripes ActiveSheet.ListObjects('myTable').ShowTableStyleColumnStripes = FalseApply row stripes ActiveSheet.ListObjects('myTable').ShowTableStyleRowStripes = True Set default Table StyleThe following macro will set the default table style. Sub SetDefaultTableStyle'Set default table styleActiveWorkbook.DefaultTableStyle = 'TableStyleMedium2'End Sub Looping through TablesThe macros in this section will through all the tables on the worksheet or workbook.
Loop through all tables on worksheet Sub LoopThroughAllTablesWorksheet'Create variables to hold the worksheet and the table Dim ws As WorksheetDim tbl As ListObjectSet ws = ActiveSheet'Loop through each table in worksheet For Each tbl In ws.ListObjects'Do something to the Table. Next tblEnd Sub Loop through all tables in a workbook Sub LoopThroughAllTablesWorkbook'Create variables to hold the worksheet and the table Dim ws As WorksheetDim tbl As ListObject'Loop through eac worksheet For Each ws In ActiveWorkbook.Worksheets'Loop through each table in worksheet For Each tbl In ws.ListObjects'Do something to the Table. Next tblNext wsEnd Sub Adding & removing rows and columnsThe following macros will add and remove rows, headers and totals from a Table.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |