How to Insert Any Type of Data or Object into an Existing Document
Contents
Why Do You Need to Insert Anyway?
There are instances where we might forget to add data while creating a Word document or Excel spreadsheet. When we realize our mistake later, we can go in and add it using insert rows/columns, insert pages/shapes/images, etc., and by using the context menu of Microsoft Excel or Microsoft Word. Also, some data might need to be appended to the document on a conditional basis in a particular order, which again might result in needing to insert data.
Let’s look at the different types of data insertion one-by-one.
Insert Rows or Columns in Microsoft Excel
Rows or columns can be inserted into existing spreadsheets either manually or through VBA.
The Context Menu for Insert
Select a row 🡪 insert menu 🡪 insert one blank row above the selected row.
In this image, the inserted row would be row 3 (the row immediately above it):
Select a column 🡪 right-click 🡪 insert menu 🡪 insert a blank column to the left of the selected column.
In this image, the inserted column would be column B (the column immediately before it):
When we select a cell and then choose the “insert” option from the context menu, we can find a small dialog box with four options as shown in the image below:
The option we choose determines whether:
- Only a cell will be inserted, pushing existing cells to bottom/right or
- An entire row/column needs to be inserted.
The table below explains further what the options mean:
S. no | Option | Description |
1 | Shift cells right | Moves all the cells to the right of the selected cell to the right by one position as a new cell gets created in the place of the selected cell. |
2 | Shift cells down | Moves all the cells at the bottom of the selected cell to the bottom by one position as a new cell gets created in the place of the selected cell. |
3 | Entire row | Inserts a new row in the place of the selected cell by pushing all the rows below + current row down. |
4 | Entire column | Inserts a new column in the place of the selected cell by pushing all the columns on the right + current column to the right. |
But what if your task is enormous? For example, what if we need to insert around 100 rows at different positions based on different criteria? That is when we need to rely on the help of VBA code.
Insert Rows and Columns Using VBA Code
Using VBA Range or selection we can insert rows or columns to an Excel spreadsheet or even the tables in a Word document.
Insert Function in Excel VBA to Insert Rows/Columns
Here is the syntax for the insert function in VBA:
Syntax
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Where
- The Shift parameter indicates the direction in which the selected and cells below or to the right have to be moved. The value would be
xlDown
for row insertions andxlRight
for column insertions, - The CopyOrigin parameter has two possible values for
xlFormatFromLeftOrAbove
.- 0 is a default value which copies the formatting from the cells on the row above.
- 1 is a specific value if you do not want any formatting to be copied.
Example of Inserting a Row Using VBA
This program selects the fourth row of an active spreadsheet and inserts a new row above it using the insert function.
Sub insertions() 'select row number 4 rows("4:4").Select 'insert a row above row number 4 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub
Example of Inserting a Column Using VBA
This program selects the fourth row of an active spreadsheet and inserts a new row above it using the Insert function.
Sub insertions() 'select column C Columns("C:C").Select 'insert a row above row number 4 Selection.Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove End Sub
Output After Running Both the Examples Above
In this output, we can see that the 4th row is a new/blank row that is inserted. Similarly, Col C is the new blank column inserted.
Insert Text in Word Using VBA
The Selection.InsertAfter
and Selection.InsertBefore
functions can be used to insert text in the open Word document using VBA.
Selection.InsertAfter
This code snippet inserts the text in the parameter in the open/associated Word document after the selected range of text or content. If nothing is selected, it is inserted after the cursor.
Sub selectioninsert_demo() Selection.InsertAfter ("I'm in a dreamland.") End Sub
Selection.InsertBefore
This code snippet inserts the text in the parameter in the open/associated word document before the selected range of text or content. If nothing is selected, it is inserted before the cursor.
Sub selectioninsert_demo() Selection.InsertBefore ("I'm well educated.") End Sub
Run both the procedures above, placing the cursor at different positions/selecting different text on an existing document. The output can help you understand how it works.
Inserting a Page Break
Selection.InsertBreak
is the statement/function used to insert a page break in an existing document.
Here is the document in which we are going to insert a page break.
We have to place the cursor in between two lines and run the subprocedure below:
Sub selectioninsert_demo() ' code to insert a page break Selection.InsertBreak End Sub
The output is:
Insertions in a Table in Microsoft Word
Insertions like cells, rows, or columns can be performed on a table object inserted in a Word file.
Insert Cells to a Table
Selection.InsertCells
without a parameter can insert the same number of cells (rows and columns) at the bottom of the selected table in the document.
Sub selectioninsert_demo() ' select the first table in the document ( specified in the index as 1) ActiveDocument.Tables(1).Select ' insert same no of cells as in the selected table. This does not add another table. Just increments the no of cells Selection.InsertCells End Sub
Before running the code:
After running the code:
Insert Rows to a Table in Microsoft Word
Selection.InsertRows
(< number of rows >) can insert the specified number of rows on top of the table, pushing down the existing content.
Sub selectioninsert_demo() ' select the table ActiveDocument.Tables(1).Select Selection.InsertRows (3) End Sub
The code above will insert three rows at the top of the selected table.
InsertRowsAbove and InsertRowsBelow
Functions like InsertRowsAbove and InsertRowsBelow can explicitly be used to insert the rows above or below the table respectively.
Insert Columns to the Table in Microsoft Word
Selection.Insertcolumns
can insert the existing number of columns to the left of the table pushing the existing content to the right. It does not support any parameters.
Sub selectioninsert_demo() ' select the table ActiveDocument.Tables(1).Select Selection.InsertColumns End Sub
The code above will insert three columns to the left of the selected table.
Insert Columns to the Right of the Table
In the above example we noticed that the new columns get inserted to the left of the table by default. When the function “InsertColumnRight
” is used, the columns get inserted to the right of the column.
Sub selectioninsert_demo() ' select the table ActiveDocument.Tables(1).Select Selection.InsertColumnsRight End Sub
Here are the newly inserted columns to the right of the table when the above program is run:
Insertion of Date and Time
The code below helps in inserting the current date and time in the place of the cursor or selected text.
Selection.InsertDateTime
Please run the code above to see the output visually, as the current date keeps changing every day. 😊
Inserting a Formula
Formulas can be inserted in the selected area using the InsertFormula function.
Syntax:
Selection.Insertformula ([<formula>] ,[<Number Format>])
Where formula
can be any Excel formula and the number format parameter represents the format in which are expecting the output value to be.
Eg:
Selection.InsertFormula ("=sum(C1:C10)")
Insert a New Page
The code below can help in inserting a new page using VBA in Microsoft Word.
Sub selectioninsert_demo() ' insert a new page Selection.InsertNewPage End Sub
Insert a Paragraph
The InsertParagraph function can help in inserting a new paragraph in the place of the selected text or the cursor.
Sub selectioninsert_demo() ' insert a new paragraph With Selection .InsertParagraph End With End Sub
Similarly, InsertParagraphAfter and InsertParagraphBefore functions can be used to insert the paragraphs before/after the selections respectively.
InsertFile, InsertXML, InsertSymbol, and InsertStyleSeparator are the other insertions that can be achieved using VBA codes.
Conclusion
Insertion of rows/cells/columns are mostly useful with Microsoft Excel and the table objects of Microsoft Word. There are many other insertions discussed in this article that can be of much use in Microsoft Word.
These can be used in VBA macro of a Word document when the insertion is recursive based on the need.