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

I need a VBA code that can do the following: From my open workbook 'Service Provider'and…

  1. User

    I need a VBA code that can do the following:

    From my open workbook 'Service Provider'and active sheet, Open into the background the closed workbook 'Service Provider History' located at "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx". Copy cells with data in column J from J5 downwards from the sheet in workbook 'Service Provider History, that has the same name as the active sheet in workbook 'Service Provider' Open a new Note Pad sheet and paste each cell copied into a seprate line in Note Pad. Close workbook 'Service Provider History'

  2. Assistant

    Here is the VBA code that accomplishes the mentioned tasks:

    Sub CopyToNotepad()
        Dim sourceWorkbook As Workbook
        Dim destinationWorkbook As Workbook
        Dim sourceWorksheet As Worksheet
        Dim destinationWorksheet As Worksheet
        Dim path As String, fileName As String
        Dim lastRow As Long
        Dim i As Long
        
        ' Set the path and filename of the destination workbook
        path = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\"
        fileName = "Service Providers History.xlsx"
        
        ' Open the source workbook
        Set sourceWorkbook = ThisWorkbook
        ' Open the destination workbook in the background
        Set destinationWorkbook = Workbooks.Open(path & fileName)
        
        ' Get the active sheet name in the source workbook
        Dim activeSheetName As String
        activeSheetName = sourceWorkbook.ActiveSheet.Name
        
        ' Set the source worksheet
        Set sourceWorksheet = sourceWorkbook.ActiveSheet
        
        ' Set the destination worksheet with the same name as the active sheet in the source workbook
        Set destinationWorksheet = destinationWorkbook.Sheets(activeSheetName)
        
        ' Find the last row with data in column J of the destination worksheet
        lastRow = destinationWorksheet.Cells(destinationWorksheet.Rows.Count, "J").End(xlUp).Row
        
        ' Open Notepad
        Dim objShell As Object
        Set objShell = CreateObject("WScript.Shell")
        objShell.Run "notepad", 1, True
        
        ' Loop through the cells in column J and copy each cell to a separate line in Notepad
        For i = 5 To lastRow
            ' Copy the cell value to Notepad
            objShell.SendKeys destinationWorksheet.Range("J" & i).Value & vbCrLf
        Next i
        
        ' Close the destination workbook without saving changes
        destinationWorkbook.Close False
        
        ' Clean up the variables
        Set destinationWorksheet = Nothing
        Set destinationWorkbook = Nothing
        Set sourceWorksheet = Nothing
        Set sourceWorkbook = Nothing
        Set objShell = Nothing
    End Sub
    

    Make sure to adjust the file path and name in the path and fileName variables to match your actual file location.

    To run this code, you can press Alt + F11 to open the VBA editor in Excel, and then insert a new module. Copy and paste the code into the new module, and then close the VBA editor.

    To execute the code, go back to your Excel worksheet and press Alt + F8 to open the macro dialog. Select the CopyToNotepad macro and click "Run".

    This will open the desired workbook in the background, copy the data from column J of the corresponding sheet, open Notepad, and paste each cell value into a separate line. Finally, it will close the workbook without saving changes.

  3. User

    error on this line of code: Set destinationWorkbook = Workbooks.Open(path & fileName)

  4. Assistant

    If you are getting an error on the line Set destinationWorkbook = Workbooks.Open(path & fileName), it could be due to one of the following reasons:

    1. File path or name is incorrect: Double-check the path and fileName variables to ensure that they match the actual file path and name of the 'Service Provider History' workbook. Make sure to include the file extension (.xlsx).

    2. File is not accessible: Make sure that you have the necessary permissions to access the file located at the specified path. If the file is locked or opened by another user, it may cause an error.

    3. File is already open: Check if the 'Service Provider History' workbook is already open in Excel. If it is open, you do not need to open it again and can directly reference it using Workbooks("Service Provider History.xlsx") or by its index.

    You can also try debugging the code by adding a message box to display the path and fileName variables before the problematic line to check if they have the correct values.

    MsgBox "Path: " & path & vbCrLf & "File Name: " & fileName
    Set destinationWorkbook = Workbooks.Open(path & fileName)
    

    This will display a message box showing the path and file name. Verify the values and ensure that they are correct.

  5. User

    Code opened 'Service Provider History', copied the data from Column J, opened Note Pad, but did not paste the copied data into Note Pad. When I closed notepad, the code continued and pasted the data into my active sheet in Workbook 'Service Provider'. The code should only paste into Note Pad.

  6. Assistant

    Apologies for the confusion. I misunderstood your requirement earlier. To paste the copied data into Notepad rather than the active sheet, you can modify the code as follows:

    Sub CopyToNotepad()
        Dim sourceWorkbook As Workbook
        Dim destinationWorkbook As Workbook
        Dim sourceWorksheet As Worksheet
        Dim destinationWorksheet As Worksheet
        Dim path As String, fileName As String
        Dim lastRow As Long
        Dim i As Long
        
        ' Set the path and filename of the destination workbook
        path = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\"
        fileName = "Service Providers History.xlsx"
        
        ' Open the source workbook
        Set sourceWorkbook = ThisWorkbook
        ' Open the destination workbook in the background
        Set destinationWorkbook = Workbooks.Open(path & fileName)
        
        ' Get the active sheet name in the source workbook
        Dim activeSheetName As String
        activeSheetName = sourceWorkbook.ActiveSheet.Name
        
        ' Set the source worksheet
        Set sourceWorksheet = sourceWorkbook.ActiveSheet
        
        ' Set the destination worksheet with the same name as the active sheet in the source workbook
        Set destinationWorksheet = destinationWorkbook.Sheets(activeSheetName)
        
        ' Find the last row with data in column J of the destination worksheet
        lastRow = destinationWorksheet.Cells(destinationWorksheet.Rows.Count, "J").End(xlUp).Row
        
        ' Open Notepad
        Dim objShell As Object
        Set objShell = CreateObject("WScript.Shell")
        objShell.Run "notepad", 1, True
        
        ' Loop through the cells in column J and copy each cell to a separate line in Notepad
        Dim data As String
        For i = 5 To lastRow
            ' Copy the cell value to a string variable
            data = data & destinationWorksheet.Range("J" & i).Value & vbCrLf
        Next i
        
        ' Paste the data into Notepad
        objShell.SendKeys data
        
        ' Close Notepad
        objShell.AppActivate "Notepad"
        objShell.SendKeys "^w"
        
        ' Close the destination workbook without saving changes
        destinationWorkbook.Close False
        
        ' Clean up the variables
        Set destinationWorksheet = Nothing
        Set destinationWorkbook = Nothing
        Set sourceWorksheet = Nothing
        Set sourceWorkbook = Nothing
        Set objShell = Nothing
    End Sub
    

    In this modified code, rather than directly pasting each cell value into Notepad using objShell.SendKeys, we first store the copied data in the data string variable. After the loop completes, we use objShell.SendKeys data to paste the entire data into Notepad. Additionally, we add code to activate and close Notepad at the end.

    This way, the data from column J will be pasted into Notepad, and the 'Service Provider History' workbook will be closed without saving any changes.