Example: ' create the Excel object Set ExcelObj = CreateObject("Excel.Application") ExcelObj.Visible = true
' add a new Workbooks and a new Sheet ExcelObj.Workbooks.Add
Set NewSheet = ExcelObj.Sheets.Item(1) NewSheet.Name = "Page Information"
' Enter some data into the sheet For row = 1 to 10 NewSheet.Cells(row,1) = "index = " & row NewSheet.Cells(row,2) = row * row Next
' Insert a new cell into row 6, column A (shift existing contents to the right) NewSheet.Cells(6, 1).Insert -4161 ' xlShiftToRight
' Insert a new row into row 8 (shift existing rows down) NewSheet.Rows(8).Insert -4121 'xlShiftDown
' Insert a new column B (shift existing columns to the right) NewSheet.Columns("B").Insert -4161
Set ExcelObj = Nothing
Note: When you instruct Excel to shift the cells, rows, or columns, you will need to use the constant values. For information on Excel constant definitions, refer toMicrosoft Excel Constants.
Alternatively, you can record inserting a new cell using low-level recording and hotkeys. 1. Start recording. 2. Go to Test -> Low-Level Recording or press CTRL+SHIFT+F3 to start recording in low-level mode. 3. Record the desired actions or keystrokes. 4. Return to standard recording by selecting Test -> Low-Level Recording or by pressing CTRL+SHIFT+F3.
Example: ' put focus on the cell Window("Book1").WinObject("Book1").Click 41,61 ' Alt-I(nsert) > E (for C(e)ll) Window("Book1").Window("Book1").Type micAltDwn + "i" + micAltUp Window("Book1").Window("Book1").Type "e" ' click the OK button Window("Book1").Window("Insert").Click 35,128
The location of the cell needs to be clicked by coordinates.