I would like to modify this code I am using in a word document so that it updates its…
- User
I would like to modify this code I am using in a word document so that it updates its table columns with the currently selected row in excel: Sub AppendCellValuesToTable() 'Get a reference to the Excel Application object Set xlApp = GetObject(, "Excel.Application")
'Get a reference to the active Workbook object Set xlWB = xlApp.ActiveWorkbook
'Get the values of cells A1, B1, C1, and D1 from the active Excel sheet using the Excel object model cellA1 = xlApp.ActiveWorkbook.ActiveSheet.Range("A10").Value cellB1 = xlApp.ActiveWorkbook.ActiveSheet.Range("B10").Value cellA2 = xlApp.ActiveWorkbook.ActiveSheet.Range("D10").Value cellB2 = xlApp.ActiveWorkbook.ActiveSheet.Range("E10").Value cellA3 = xlApp.ActiveWorkbook.ActiveSheet.Range("C10").Value cellB3 = xlApp.ActiveWorkbook.ActiveSheet.Range("M10").Value cellA4 = xlApp.ActiveWorkbook.ActiveSheet.Range("L10").Value cellB4 = xlApp.ActiveWorkbook.ActiveSheet.Range("F10").Value cellA5 = xlApp.ActiveWorkbook.ActiveSheet.Range("G10").Value 'cellB5 = xlApp.ActiveWorkbook.ActiveSheet.Range("I10").Value cellA6 = xlApp.ActiveWorkbook.ActiveSheet.Range("H10").Value 'cellB6 = xlApp.ActiveWorkbook.ActiveSheet.Range("J10").Value
'Get the Word table and append the values to the corresponding cells With ActiveDocument.Tables(1) 'Row 1 .Cell(1, 1).Range.Text = cellA1 .Cell(1, 2).Range.Text = cellB1
'Row 2 .Cell(2, 1).Range.Text = cellA2 .Cell(2, 2).Range.Text = cellB2
'Row 3 .Cell(3, 1).Range.Text = cellA3 .Cell(3, 2).Range.Text = cellB3
'Row 4 .Cell(4, 1).Range.Text = cellA4 .Cell(4, 2).Range.Text = cellB4
'Row 5 .Cell(5, 1).Range.Text = cellA5 '.Cell(5, 2).Range.Text = cellB5
'Row 6 .Cell(6, 1).Range.Text = cellA6 '.Cell(6, 2).Range.Text = cellB6
End With
End Sub
- Assistant
To update the table columns with the currently selected row in Excel, you will need to modify the code to retrieve the values from the selected row instead of the fixed row. Here's an updated code:
Sub AppendCellValuesToTable() Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim selectedRow As Integer
'Get a reference to the Excel Application object Set xlApp = GetObject(, "Excel.Application") 'Get a reference to the active Workbook object Set xlWB = xlApp.ActiveWorkbook 'Get the active worksheet in Excel Set xlSheet = xlApp.ActiveSheet 'Get the currently selected row in Excel selectedRow = xlApp.Selection.Row 'Get the values of cells A to F from the selected row in Excel cellA1 = xlSheet.Cells(selectedRow, 1).Value cellB1 = xlSheet.Cells(selectedRow, 2).Value cellA2 = xlSheet.Cells(selectedRow, 4).Value cellB2 = xlSheet.Cells(selectedRow, 5).Value cellA3 = xlSheet.Cells(selectedRow, 3).Value cellB3 = xlSheet.Cells(selectedRow, 13).Value cellA4 = xlSheet.Cells(selectedRow, 12).Value cellB4 = xlSheet.Cells(selectedRow, 6).Value cellA5 = xlSheet.Cells(selectedRow, 7).Value cellA6 = xlSheet.Cells(selectedRow, 8).Value 'Get the Word table and append the values to the corresponding cells With ActiveDocument.Tables(1) 'Row 1 .Cell(1, 1).Range.Text = cellA1 .Cell(1, 2).Range.Text = cellB1 'Row 2 .Cell(2, 1).Range.Text = cellA2 .Cell(2, 2).Range.Text = cellB2 'Row 3 .Cell(3, 1).Range.Text = cellA3 .Cell(3, 2).Range.Text = cellB3 'Row 4 .Cell(4, 1).Range.Text = cellA4 .Cell(4, 2).Range.Text = cellB4 'Row 5 .Cell(5, 1).Range.Text = cellA5 'Row 6 .Cell(6, 1).Range.Text = cellA6 End WithEnd Sub
In the updated code, we first retrieve the currently selected row in Excel using the Selection.Row property. Then, we update the calls to xlSheet.Cells() to use the selected row number instead of the fixed row. Finally, we append the retrieved values to the corresponding cells in the Word table as before.
- User
In this code, I would like to enter the £ sign before the Value in cell B4: cellA4 = "Value: " cellB4 = xlSheet.Cells(selectedRow, 4).Value
- Assistant
To add the '£' sign before the value in cell B4, you can modify the line:
cellB4 = xlSheet.Cells(selectedRow, 4).Valueto:
cellB4 = "£" & xlSheet.Cells(selectedRow, 4).ValueSo the complete modified code will be:
Sub AppendCellValuesToTable() Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim selectedRow As Integer 'Get a reference to the Excel Application object Set xlApp = GetObject(, "Excel.Application") 'Get a reference to the active Workbook object Set xlWB = xlApp.ActiveWorkbook 'Get the active worksheet in Excel Set xlSheet = xlApp.ActiveSheet 'Get the currently selected row in Excel selectedRow = xlApp.Selection.Row 'Get the values of cells A to F from the selected row in Excel cellA1 = xlSheet.Cells(selectedRow, 1).Value cellB1 = xlSheet.Cells(selectedRow, 2).Value cellA2 = xlSheet.Cells(selectedRow, 4).Value cellB2 = xlSheet.Cells(selectedRow, 5).Value cellA3 = xlSheet.Cells(selectedRow, 3).Value cellB3 = xlSheet.Cells(selectedRow, 13).Value cellA4 = "Value: " cellB4 = "£" & xlSheet.Cells(selectedRow, 4).Value cellA5 = xlSheet.Cells(selectedRow, 7).Value cellA6 = xlSheet.Cells(selectedRow, 8).Value 'Get the Word table and append the values to the corresponding cells With ActiveDocument.Tables(1) 'Row 1 .Cell(1, 1).Range.Text = cellA1 .Cell(1, 2).Range.Text = cellB1 'Row 2 .Cell(2, 1).Range.Text = cellA2 .Cell(2, 2).Range.Text = cellB2 'Row 3 .Cell(3, 1).Range.Text = cellA3 .Cell(3, 2).Range.Text = cellB3 'Row 4 .Cell(4, 1).Range.Text = cellA4 .Cell(4, 2).Range.Text = cellB4 'Row 5 .Cell(5, 1).Range.Text = cellA5 'Row 6 .Cell(6, 1).Range.Text = cellA6 End With End SubNow, "£" will appear before the value of cell B4 when it's inserted into the table in Word.