Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· OpenAIAi2 WildChat-4.8M (AI2)

I am trying to write a VBA event that will do the following. Open Shell "notepad.exe",…

  1. User

    I am trying to write a VBA event that will do the following. Open Shell "notepad.exe", vbNormalFocus then For Each xWs In ActiveWorkbook.Worksheets If Len(wsName) = 3 Then If the value in B:B = "Service" and Offset(0, 6).Value = "" Then copy the row Range from Offset(0, -1) to Offset(0, 8)), add a space and concatenate it with the sheet name 'wsName' and write each result found to the open notepad one row at a time. Then go on to next shhet and do the sam until all shhets with Len(wsName) = 3 have been searched.

  2. Assistant

    Here is a VBA code that achieves the mentioned functionality:

    Sub WriteDataToNotepad()
        Dim xWs As Worksheet
        Dim rowRange As Range
        Dim wsName As String
        Dim ws As Worksheet
        Dim notepadApp As Object
        Dim notepadFile As Object
        Dim textLine As String
        
        Set notepadApp = CreateObject("WScript.Shell")
        notepadApp.Run "notepad.exe"
        Application.Wait Now + TimeValue("0:00:01")
        
        For Each xWs In ThisWorkbook.Worksheets
            If Len(xWs.Name) = 3 Then
                For Each rowRange In xWs.Range("B:B")
                    If rowRange.Value = "Service" And IsEmpty(rowRange.Offset(0, 6).Value) Then
                        textLine = Join(Application.Transpose(rowRange.Offset(0, -1).Resize(1, 9).Value))
                        textLine = textLine & " " & xWs.Name
                        notepadApp.AppActivate "Notepad"
                        SendKeys textLine
                        SendKeys "{ENTER}"
                    End If
                Next rowRange
            End If
        Next xWs
        
        MsgBox "Data has been successfully written to Notepad."
    End Sub
    

    Please note that this code will write the concatenated result to the notepad window one row at a time using the SendKeys method. Make sure to save your work before running this code as it uses keyboard input to write to Notepad.

  3. User

    Erro: Method 'Run' of object 'IWshShell3' failed, on the line 'notepadApp.Run "notepad.exe"""

  4. Assistant

    I apologize for the inconvenience. Let's modify the code to open Notepad using a different method and write the data directly to the Notepad window without using the SendKeys method. Here is the updated VBA code:

    Sub WriteDataToNotepad()
        Dim xWs As Worksheet
        Dim wsName As String
        Dim ws As Worksheet
        Dim notepadApp As Object
        Dim notepadFile As Object
        Dim textLine As String
        Dim rowRange As Range
        Dim notepadText As String
        
        Set notepadApp = CreateObject("Shell.Application")
        Set notepadFile = notepadApp.Windows("Notepad")
        
        If notepadFile Is Nothing Then
            MsgBox "Notepad is not open. Please open Notepad before running this macro."
            Exit Sub
        End If
        
        For Each xWs In ThisWorkbook.Worksheets
            If Len(xWs.Name) = 3 Then
                For Each rowRange In xWs.Range("B:B")
                    If rowRange.Value = "Service" And IsEmpty(rowRange.Offset(0, 6).Value) Then
                        textLine = Join(Application.Transpose(rowRange.Offset(0, -1).Resize(1, 9).Value))
                        textLine = textLine & " " & xWs.Name
                        notepadFile.Document.Content.Text = notepadFile.Document.Content.Text & textLine & vbCrLf
                    End If
                Next rowRange
            End If
        Next xWs
        
        MsgBox "Data has been successfully written to Notepad."
    End Sub
    

    This code will now check if Notepad is open before attempting to write the data. If Notepad is not open, a message box will prompt the user to open Notepad before running the macro. The data will be written directly to the Notepad window without using the SendKeys method.

  5. User

    Invalid procedure call on the line 'Set notepadFile = notepadApp.Windows("Notepad")

  6. Assistant

    I apologize for the oversight. Let's modify the approach to address the issue. Here is an updated VBA code that opens Notepad and writes the data to it without relying on the Shell.Application method:

    Sub WriteDataToNotepad()
        Dim xWs As Worksheet
        Dim wsName As String
        Dim textLine As String
        Dim notepadPID As Long
        Dim notepadWindow As Object
        
        Shell "notepad.exe", vbNormalFocus
        Application.Wait Now + TimeValue("0:00:01")
        
        notepadPID = FindWindow("Notepad", vbNullString)
        
        If notepadPID = 0 Then
            MsgBox "Notepad window not found. Please open Notepad and run the macro again."
            Exit Sub
        End If
        
        For Each xWs In ThisWorkbook.Worksheets
            If Len(xWs.Name) = 3 Then
                For Each rowRange In xWs.Range("B:B")
                    If rowRange.Value = "Service" And IsEmpty(rowRange.Offset(0, 6).Value) Then
                        textLine = Join(Application.Transpose(rowRange.Offset(0, -1).Resize(1, 9).Value))
                        textLine = textLine & " " & xWs.Name
                        notepadWindow = FindWindow("Notepad", vbNullString)
                        SendMessage notepadWindow, WM_SETTEXT, 0, ByVal StrPtr(textLine & vbCrLf)
                    End If
                Next rowRange
            End If
        Next xWs
        
        MsgBox "Data has been successfully written to Notepad."
    End Sub
    

    In this updated code, we use the FindWindow and SendMessage functions to find the Notepad window and send the text directly to it. If the Notepad window is not found, a message box will prompt the user to open Notepad before running the macro.