I am trying to write a VBA event that will do the following. Open Shell "notepad.exe",…
- 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.
- 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 SubPlease note that this code will write the concatenated result to the notepad window one row at a time using the
SendKeysmethod. Make sure to save your work before running this code as it uses keyboard input to write to Notepad. - User
Erro: Method 'Run' of object 'IWshShell3' failed, on the line 'notepadApp.Run "notepad.exe"""
- 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
SendKeysmethod. 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 SubThis 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
SendKeysmethod. - User
Invalid procedure call on the line 'Set notepadFile = notepadApp.Windows("Notepad")
- 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 SubIn this updated code, we use the
FindWindowandSendMessagefunctions 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.