Skip to content
The Chat Archive
gpt-3.5-turbo-0301· OpenAI WildChat-4.8M (AI2)

I would like to modify this code I am using in a word document so that it updates its…

  1. 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

  2. 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 With
    

    End 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.

  3. 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

  4. Assistant

    To add the '£' sign before the value in cell B4, you can modify the line:

    cellB4 = xlSheet.Cells(selectedRow, 4).Value
    

    to:

    cellB4 = "£" & xlSheet.Cells(selectedRow, 4).Value
    

    So 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 Sub
    

    Now, "£" will appear before the value of cell B4 when it's inserted into the table in Word.