Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

When an excel sheet opens, I want a vba code to open a specific word document doc1 only…

  1. User

    When an excel sheet opens, I want a vba code to open a specific word document doc1 only if the values in the last row of column B and Column D are different. If the particular word document doc1 is left open the vba code should not attempt to open the already open document doc1 again. Only opening the word document doc1 if it is not or no longer open therefore only having one instance of the word document doc1.

    After finishing and closing the word document, I will manually enter a date in column E of the last row entry. After entering the date a differnet word document doc2 should open. While the word document doc2 is open, if I click on different cell in column E it should not attempt to open the already open document doc2 again. Only opening the word document doc2 if it is not or no longer open therefore only having one instance of the word document doc2.

    I also need a code that runs everytime I select or enter a value in a cell in Column E. I want the vba code to copy 10 values of 10 cells in the same row as the cell in column E I selected (B,C,D,E,F,G,H,I,J,L) and appended their values to column M in the following order M1 to M10. The values changing for every cell I select in column E and the values cleared when I also leave the sheet. The word document doc2 that is opened by activity on column E as mentioned above will capture data from colum M1 to M10.

    All these codes will by about 50 excel sheet for the same purpose. Can the codes be called from modules?

  2. Assistant

    Yes, the codes can be organized in modules and called upon by each of the 50 Excel sheets. Here is a possible implementation of the described functionalities:

    In a module, define the following variables:

    Dim wordApp As Word.Application
    Dim doc1 As Word.Document
    Dim doc2 As Word.Document
    Dim lastRow As Long
    Dim colBLastVal As Variant
    Dim colDLastVal As Variant
    Dim colELastVal As Variant
    

    The first three variables are used to keep track of the Word application and documents. The other variables will be used to check if the last values in columns B, D, and E have changed.

    In the Workbook_Open event, add the following code to initialize the Word application and open doc1:

    Set wordApp = New Word.Application
    wordApp.Visible = True
    On Error Resume Next
    Set doc1 = wordApp.Documents("doc1.docx")
    On Error GoTo 0
    If doc1 Is Nothing Then
        Set doc1 = wordApp.Documents.Open("path\to\doc1.docx")
    End If
    lastRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
    colBLastVal = ThisWorkbook.Sheets(1).Cells(lastRow, "B").Value
    colDLastVal = ThisWorkbook.Sheets(1).Cells(lastRow, "D").Value
    

    This code creates a new instance of the Word application and sets it to visible. It attempts to retrieve an already open instance of doc1, and opens it if it is not found. Finally, it sets lastRow to the last used row, and sets colBLastVal and colDLastVal to the values in the last row of columns B and D.

    Add the following code to the Worksheet_SelectionChange event:

    If ActiveCell.Column = 5 Then
        If colELastVal <> ActiveCell.Value Then
            On Error Resume Next
            Set doc2 = wordApp.Documents("doc2.docx")
            On Error GoTo 0
            If doc2 Is Nothing Then
                Set doc2 = wordApp.Documents.Open("path\to\doc2.docx")
            End If
            colELastVal = ActiveCell.Value
        End If
    End If
    

    This code checks if the active cell is in column E. If it is, and the value has changed since the last time the event was triggered, it attempts to retrieve an already open instance of doc2, and opens it if it is not found. Finally, it sets colELastVal to the current value in the active cell.

    Add the following code to the Worksheet_Change event:

    If Target.Column = 5 Then
        Dim rowValues As Range
        Set rowValues = ThisWorkbook.Sheets(1).Range("B" & Target.Row & ":L" & Target.Row)
        If Not Intersect(Target, rowValues) Is Nothing Then
            Dim mValues As String
            Dim i As Long
            For i = 1 To 10
                mValues = mValues & rowValues(1, i).Value & " "
            Next i
            ThisWorkbook.Sheets(1).Cells(Target.Row, "M").Value = mValues
        End If
    End If
    

    This code checks if the changed cell is in column E. If it is, and it is within the range of columns B to L in the same row, it extracts the values of the 10 cells in that row and concatenates them into a string mValues. It then writes mValues to the corresponding cell in column M.

    Finally, add the following code to the Workbook_BeforeClose event:

    On Error Resume Next
    doc1.Close False
    doc2.Close False
    wordApp.Quit
    Set doc1 = Nothing
    Set doc2 = Nothing
    Set wordApp = Nothing
    On Error GoTo 0
    

    This code closes the Word documents and application, and releases the associated objects.

    Note that you may need to adjust the paths to the Word documents in the code, as well as add error handling and other features depending on your specific requirements.

  3. User

    Can I use the workbook open event in a worksheet activate event so that it is the worksheet that opens the word document

  4. Assistant

    Yes, you can use the Worksheet_Activate event instead of the Workbook_Open event to open the Word document associated with each worksheet. Here's an example implementation:

    1. Create a new module and define the following variables:
    Dim wordApp As Word.Application
    Dim doc1 As Word.Document
    
    1. In the Workbook_Open event, initialize the Word.Application object and open doc1:
    Private Sub Workbook_Open()
        Set wordApp = New Word.Application
        wordApp.Visible = True
        Set doc1 = wordApp.Documents.Open("path\to\doc1.docx")
    End Sub
    
    1. In each worksheet module, add the following code to the Worksheet_Activate event:
    Private Sub Worksheet_Activate()
        On Error Resume Next
        Set doc1 = wordApp.Documents("doc1.docx")
        On Error GoTo 0
        If doc1 Is Nothing Then
            Set doc1 = wordApp.Documents.Open("path\to\doc1.docx")
        End If
    End Sub
    

    This code attempts to retrieve an already open instance of doc1, and opens it if it is not found. Note that the On Error Resume Next statement is used to handle the case where wordApp.Documents("doc1.docx") throws an error because the document is not open.

    You can add similar code to the Worksheet_SelectionChange and Worksheet_Change events to open other word documents and update cell values, as described in the previous answer.

    Note that you may need to adjust the paths to the Word documents in the code, and make sure that each worksheet has its own copy of doc1 (or any other Word document that you want to open).

  5. User

    I do not want the workbook open event to open the word document. I want the document to only be opened by a worksheet and only when I have clicked on a cell in column E

  6. Assistant

    Understood. You can modify the code as follows:

    1. In a new module, define the following variables:
    Dim wordApp As Word.Application
    Dim colELastVal As Variant
    Dim doc2 As Word.Document
    
    1. In each worksheet module, add the following code to the Worksheet_Activate event:
    Private Sub Worksheet_Activate()
        Set wordApp = New Word.Application
        wordApp.Visible = True
        Set colELastVal = Nothing
        Set doc2 = Nothing
    End Sub
    

    This code creates a new instance of the Word.Application object, sets it to visible, and initializes the colELastVal and doc2 variables to Nothing.

    1. Add the following code to the Worksheet_SelectionChange event:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Column = 5 Then
            If colELastVal <> Target.Value Then
                On Error Resume Next
                Set doc2 = wordApp.Documents("doc2.docx")
                On Error GoTo 0
                If doc2 Is Nothing Then
                    Set doc2 = wordApp.Documents.Open("path\to\doc2.docx")
                End If
                colELastVal = Target.Value
            End If
        End If
    End Sub
    

    This code checks if the selected cell is in column E. If it is, and the value of the cell has changed since the last time the event was triggered, it attempts to retrieve an already open instance of doc2, and opens it if it is not found. Finally, it sets colELastVal to the current value of the selected cell.

    1. Add the following code to the Worksheet_Change event:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 5 Then
            Dim rowValues As Range
            Set rowValues = Range("B" & Target.Row & ":L" & Target.Row)
            If Not Intersect(Target, rowValues) Is Nothing Then
                Dim mValues As String
                Dim i As Long
                For i = 1 To 10
                    mValues = mValues & rowValues(1, i).Value & " "
                Next i
                Range("M" & Target.Row).Value = mValues
            End If
        End If
    End Sub
    

    This code checks if the changed cell is in column E. If it is, and it is within the range of columns B to L in the same row, it extracts the values of the 10 cells in that row and concatenates them into a string mValues. It then writes mValues to the corresponding cell in column M.

    1. Finally, add the following code to the Worksheet_Deactivate event to close doc2:
    Private Sub Worksheet_Deactivate()
        On Error Resume Next
        doc2.Close False
        On Error GoTo 0
        Set doc2 = Nothing
    End Sub
    

    This code attempts to close doc2 when the worksheet loses focus. Note that it uses On Error Resume Next to handle the case where doc2 is not open, and releases the object using Set doc2 = Nothing.

    You will need to adjust the paths to the Word documents in the code, and make sure that each worksheet has its own copy of doc2 (or any other Word document that you want to open).

  7. User

    The code that runs every time I select or enter a value in a cell in Column E does not work. I want the vba code to copy 10 values of 10 cells in the same row as the cell in column E I selected (B,C,D,E,F,G,H,I,J,L) and appended their values to column M in the following order M1 to M10. The values changing for every cell I select in column E and the values cleared when I also leave the sheet. Here is the code: Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 5 Then Dim rowValues As Range Set rowValues = Range("B" & Target.Row & ":L" & Target.Row) If Not Intersect(Target, rowValues) Is Nothing Then Dim mValues As String Dim i As Long For i = 1 To 10 mValues = mValues & rowValues(1, i).Value & " " Next i Range("M" & Target.Row).Value = mValues End If End If

    End Sub

  8. Assistant

    The code you provided for copying the row data to column M when a cell in column E is changed looks correct. Here are a few things you can check to make sure it works:

    1. Make sure that you have placed the code in the correct worksheet module. It should be in the worksheet module for the sheet where you want this functionality.

    2. Make sure that macros are enabled in your Excel file. If macros are disabled, the code will not execute.

    3. Make sure that you are changing the value of a cell in column E, and not any other column, when you test the code. The Target.Column = 5 condition is what restricts the code to cells in column E.

    4. Make sure that there is no other code in the worksheet module that interferes with this code. If you have other Worksheet_Chnage events, for example, that change the same cells, it may affect how this code works.

    If you have checked these things and the code is still not working, try adding some Debug.Print statements to the code to see if it is executing at all. For example, you can add the following line at the beginning of the code:

    Debug.Print "Worksheet_Change event was triggered"
    

    Then, in the Immediate Window (View > Immediate Window in the VBA Editor), you can see if this message is printed when you change a cell in column E.

    You can also add Debug.Print statements inside the For loop to see if the row values are being concatenated correctly. For example:

    For i = 1 To 10
        mValues = mValues & rowValues(1, i).Value & " "
        Debug.Print "mValues after appending value " & i & ": " & mValues
    Next i
    

    This way, you can see if the mValues variable contains the expected values.

    I hope this helps you debug the issue. Let me know if you have any other questions or concerns!

  9. User

    still not working

  10. Assistant

    I'm sorry to hear that. Here are a few more things you can check:

    1. Make sure that you are changing the value of a cell in the same sheet where the code is running. If you change the value of a cell in a different sheet, the code in this sheet will not execute.

    2. Make sure that you have not accidentally turned off events processing in Excel. This can happen if you run code that turns off events and does not turn them back on. You can check if events processing is on by going to the VBA Immediate Window and typing Application.EnableEvents. If the result is True, events processing is on. If the result is False, type Application.EnableEvents = True to turn events processing back on.

    3. Make sure that the code is running at all by adding a message box to the Worksheet_Change event code:

    If Target.Column = 5 Then
        Dim rowValues As Range
        Set rowValues = Range("B" & Target.Row & ":L" & Target.Row)
        If Not Intersect(Target, rowValues) Is Nothing Then
            Dim mValues As String
            Dim i As Long
            For i = 1 To 10
                mValues = mValues & rowValues(1, i).Value & " "
            Next i
            Range("M" & Target.Row).Value = mValues
            MsgBox "Code ran!"
        End If
    End If
    

    If this message box appears when you change a cell in column E, it means that the code is running.

    1. Try replacing the Range, Rows, and Columns functions with the fully qualified reference to the sheet where the code is running. For example, instead of Range("B" & Target.Row & ":L" & Target.Row), use ThisWorkbook.Sheets(1).Range("B" & Target.Row & ":L" & Target.Row). This ensures that the code is executing on the correct worksheet even if it is not the active sheet.

    If none of these steps help resolve the issue, please provide more information on the behavior you are observing, and any error messages you are seeing.

  11. User

    it worked but it put he values of B,C, D,E,F,G,H,L into the same row starting at m. I want it to put the values into M1, M2,M3, M4 up to M10

  12. Assistant

    To write the values to M1 to M10 instead of M in the same row, you can modify the loop in the Worksheet_Change event as follows:

    Dim mValues As Variant
    mValues = Application.Transpose(rowValues)
    For i = 1 To 10
        Range("M" & i).Value = mValues(i)
    Next i
    

    This code uses the Transpose function to convert the range into a 2D array where each column represents one of the values. Then, it iterates over the first 10 values in the first row of the mValues array, and writes them to cells M1 to M10.

    Make sure to remove or comment out the previous code that writes the concatenated string to a single cell in column M.

    I hope this solves the issue! Let me know if you have any other questions.

  13. User

    I am getting an error on if TaskRequest is nothing Then : Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column = 5 Then If colELastVal <> Target.Value Then On Error Resume Next Set TaskRequest = wordApp.Documents("TaskRequest.docm") On Error GoTo 0 If TaskRequest Is Nothing Then Set TaskRequest = wordApp.Documents.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\TaskRequest.docm") End If colELastVal = Target.Value End If End If

    End Sub

  14. Assistant

    It seems like the TaskRequest variable is not defined in your code. Please add the following line to the beginning of your code module to define it:

    Dim TaskRequest As Word.Document
    

    This will declare TaskRequest as a variable that can hold a reference to a Word document. Make sure it is at the top of the module, before any other procedures or functions.

    Also, make sure that you have a reference to the Microsoft Word Object Library in your Excel project. To check this, go to the VBA Editor, click on Tools > References, and see if Microsoft Word is checked in the list.

    Let me know if this helps, or if you have any other questions or issues.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.