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

Open a new instance of Notepad.exe Using the value of the active cell Search column A…

  1. User

    Open a new instance of Notepad.exe Using the value of the active cell Search column A from A5 down to the first empty cell in sheet QUOTES for a match. For each match found in sheet QUOTES where the corresponding value in column M of the same row is empty Then copy the values of each row from A to G Activate notepad and paste each row value seperated by a line

  2. Assistant

    Here is a VBA code snippet that accomplishes the task described:

    Sub SearchAndCopyToNotepad()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rng As Range
        Dim cell As Range
        Dim notepadApp As Object
        Dim i As Integer
    
        ' Activate Notepad
        Set notepadApp = CreateObject("WScript.Shell")
        notepadApp.Run "notepad.exe"
    
        ' Get active workbook and worksheet
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("QUOTES")
    
        ' Get the range to search
        Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
        ' Loop through each cell in the range
        For Each cell In rng
            If cell.Value = ActiveCell.Value Then
                If ws.Cells(cell.Row, 13).Value = "" Then
                    ' Copy values from columns A to G
                    For i = 1 To 7
                        notepadApp.AppActivate "Untitled - Notepad"
                        SendKeys ws.Cells(cell.Row, i).Value & "~"
                    Next i
                End If
            End If
        Next cell
    End Sub
    

    This code will search for the value of the active cell in column A of the QUOTES sheet and copy the values of columns A to G for each matching row where the corresponding value in column M is empty. It will then activate Notepad and paste each row's values separated by a line. Note that you may need to make adjustments based on your specific requirements.

  3. User

    Is this correct - If cell.Value = Me.ActiveCell. Value Then

  4. Assistant

    Yes, that is correct. If you are running the VBA code from a worksheet module, you can use Me.ActiveCell.Value to refer to the value of the active cell in that particular worksheet.

    So, the line If cell.Value = Me.ActiveCell.Value Then would compare the value of the cell in column A of the QUOTES sheet with the value of the active cell in the currently active worksheet.

    This will work if the VBA code is placed in the worksheet module (for example, Sheet1, Sheet2, etc.) of the Excel file.

  5. User

    The values in the ActiveCell are always Text values. Will this modigfication to the VBA work. Please note the Dim qt As String..... Sub SearchAndCopyToNotepad() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String

    ' Activate Notepad
    Set notepadApp = CreateObject("WScript.Shell")
    notepadApp.Run "notepad.exe"
    
    ' Set workbook and worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("QUOTES")
    Set qt = Me.ActiveCell.Value
    
    
    ' Get the range to search
    Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' Loop through each cell in the range
    For Each cell In rng
        If cell.Value = qt Then
            If ws.Cells(cell.Row, 13).Value = "" Then
                ' Copy values from columns A to G
                For i = 1 To 7
                    notepadApp.AppActivate
                    SendKeys ws.Cells(cell.Row, i).Value & "~"
                Next i
            End If
        End If
    Next cell
    

    End Sub

  6. Assistant

    Yes, your modification to the VBA code looks correct. By declaring qt as a string variable and setting it to Me.ActiveCell.Value, you are storing the text value of the active cell in the qt variable for comparison in the loop.

    So now, in the line If cell.Value = qt Then, the code will check if the value of the cell in column A of the QUOTES sheet is equal to the text value stored in the qt variable.

    The rest of your code remains the same, looping through the range and copying values from columns A to G for each matching row where the corresponding value in column M is empty.

    Your code should work as intended for searching and copying the required values to Notepad based on the text value of the active cell.

  7. User

    Will this code send the values to notepad one character at a time or will it send it one cell at a time

  8. Assistant

    The SendKeys function in VBA sends keystrokes to the active application one character at a time. In the code snippet provided, each value from columns A to G is being sent to Notepad using SendKeys in a loop.

    If you want to send one cell at a time to Notepad, you can concatenate or format the values from columns A to G into a single string before sending it using SendKeys. Here is an updated version of your code that sends each row value as one string to Notepad:

    Sub SearchAndCopyToNotepad()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rng As Range
        Dim cell As Range
        Dim notepadApp As Object
        Dim i As Integer
        Dim qt As String
    
        ' Activate Notepad
        Set notepadApp = CreateObject("WScript.Shell")
        notepadApp.Run "notepad.exe"
    
        'Set workbook and worksheet
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("QUOTES")
        qt = Me.ActiveCell.Value
    
        ' Get the range to search
        Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
        ' Loop through each cell in the range
        For Each cell In rng
            If cell.Value = qt Then
                If ws.Cells(cell.Row, 13).Value = "" Then
                    ' Combine values from columns A to G into a single string
                    Dim rowString As String
                    For i = 1 To 7
                        rowString = rowString & ws.Cells(cell.Row, i).Value & vbCrLf
                    Next i
                    ' Activate Notepad and paste the rowString
                    notepadApp.AppActivate "Untitled - Notepad"
                    SendKeys rowString
                End If
            End If
        Next cell
    End Sub
    

    In this updated code, each row's values from columns A to G are concatenated into rowString and then sent to Notepad as a single string separated by new lines. This should send the complete row value at once instead of character by character.

  9. User

    Which will be best, one cell at a time or the entire row at a time

  10. Assistant

    The best approach depends on your specific requirements and preferences:

    1. Sending one cell at a time:

      • If you want each cell value to be inputted separately in Notepad, sending one cell at a time using the SendKeys function can provide more controlled and granular input.
      • This approach may be preferable if you need to format or modify individual cell values before sending them to Notepad.
    2. Sending the entire row at a time:

      • If you want to maintain the structure of the row and have each row displayed as a unit in Notepad, sending the entire row at a time as a single string can provide a more coherent representation.
      • This approach can be more convenient when you want to view and work with the entire row as a single entity.

    Consider the readability and usability of the data in Notepad, as well as any formatting or processing requirements you may have. Testing both approaches with sample data can help you decide which method best suits your needs.

  11. User

    Can you please then write the code below to send One Row At A time: Sub SearchAndCopyToNotepad() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String

    ' Activate Notepad
    Set notepadApp = CreateObject("WScript.Shell")
    notepadApp.Run "notepad.exe"
    
    ' Set workbook and worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("QUOTES")
    Set qt = Me.ActiveCell.Value
    
    
    ' Get the range to search
    Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' Loop through each cell in the range
    For Each cell In rng
        If cell.Value = qt Then
            If ws.Cells(cell.Row, 13).Value = "" Then
                ' Copy values from columns A to G
                For i = 1 To 7
                    notepadApp.AppActivate
                    SendKeys ws.Cells(cell.Row, i).Value & "~"
                Next i
            End If
        End If
    Next cell
    

    End Sub

  12. Assistant

    Sure, here is the modified code that sends one row at a time to Notepad:

    Sub SearchAndCopyToNotepad()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim rng As Range
        Dim cell As Range
        Dim notepadApp As Object
        Dim i As Integer
        Dim qt As String
        Dim rowString As String
        
        ' Activate Notepad
        Set notepadApp = CreateObject("WScript.Shell")
        notepadApp.Run "notepad.exe"
        
        ' Set workbook and worksheet
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("QUOTES")
        qt = Me.ActiveCell.Value
        
        ' Get the range to search
        Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        
        ' Loop through each cell in the range
        For Each cell In rng
            If cell.Value = qt Then
                If ws.Cells(cell.Row, 13).Value = "" Then
                    ' Combine values from columns A to G into a single string
                    For i = 1 To 7
                        rowString = rowString & ws.Cells(cell.Row, i).Value & " "
                    Next i
                    ' Activate Notepad and paste the rowString
                    notepadApp.AppActivate "Untitled - Notepad"
                    SendKeys rowString & vbCrLf
                    rowString = ""
                End If
            End If
        Next cell
    End Sub
    

    In this modified code, each row is concatenated into rowString and then sent to Notepad as a single string, with each cell value separated by a space and a new line after each row. This will send one row at a time to Notepad. Let me know if you require any further assistance.

  13. User

    This is how the VBA code is and it works accurately and quickly. Is there a way of putting a "," to seperate each cell of the row that is pasted in notepad.

    Sub SearchPendingQuotes()

    If ActiveCell.Column <> 1 Or ActiveSheet.Name <> "PREQUEST" Then Application.EnableEvents = True Me.Protect Password:="edit" Exit Sub End If

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim notepadApp As Object
    Dim i As Integer
    Dim qt As String
    Dim rowString As String
    
    ' Activate Notepad
    Set notepadApp = CreateObject("WScript.Shell")
    notepadApp.Run "notepad.exe"
    
    ' Set workbook and worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("QUOTES")
    qt = ActiveCell.Value
    
    ' Get the range to search
    Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.count, "A").End(xlUp).row)
    Application.EnableEvents = False
    ' Loop through each cell in the range
    For Each cell In rng
        If cell.Value = qt Then
            If ws.Cells(cell.row, 13).Value = "" Then
                ' Combine values from columns A to G into a single string
                For i = 1 To 7
                    rowString = rowString & ws.Cells(cell.row, i).Value & " "
                Next i
                ' Activate Notepad and paste the rowString
                notepadApp.AppActivate "Untitled - Notepad"
                SendKeys rowString & vbCrLf
                rowString = ""
            End If
        End If
    Next cell
    
    Call Module57.ClearClipboard
    
    Application.EnableEvents = True
    Me.Protect Password:="edit"
    

    End Sub

  14. Assistant

    To modify the VBA code to separate each cell value in the row with a comma before pasting it into Notepad, you can update the code as shown below:

    Sub SearchPendingQuotes()
    
    If ActiveCell.Column <> 1 Or ActiveSheet.Name <> "PREQUEST" Then
        Application.EnableEvents = True
        Me.Protect Password:="edit"
        Exit Sub
    End If
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim notepadApp As Object
    Dim i As Integer
    Dim qt As String
    Dim rowString As String
    
    ' Activate Notepad
    Set notepadApp = CreateObject("WScript.Shell")
    notepadApp.Run "notepad.exe"
    
    ' Set workbook and worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("QUOTES")
    qt = ActiveCell.Value
    
    ' Get the range to search
    Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    Application.EnableEvents = False
    
    ' Loop through each cell in the range
    For Each cell In rng
        If cell.Value = qt Then
            If ws.Cells(cell.Row, 13).Value = "" Then
                ' Combine values from columns A to G into a single string with comma separators
                For i = 1 To 7
                    rowString = rowString & ws.Cells(cell.Row, i).Value & ","
                Next i
                ' Remove the last comma
                rowString = Left(rowString, Len(rowString) - 1)
                ' Activate Notepad and paste the rowString
                notepadApp.AppActivate "Untitled - Notepad"
                SendKeys rowString & vbCrLf
                rowString = ""
            End If
        End If
    Next cell
    
    Call Module57.ClearClipboard
    
    Application.EnableEvents = True
    Me.Protect Password:="edit"
    End Sub
    

    In this updated code, each cell value in the row is concatenated with a comma separator, and then before pasting into Notepad, the last comma is removed. This way, each cell value will be separated by a comma when pasted into Notepad.