I need a VBA code that can do the following: From my open workbook 'Service Provider'and…
- 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'
- 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 SubMake sure to adjust the file path and name in the
pathandfileNamevariables to match your actual file location.To run this code, you can press
Alt + F11to 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 + F8to open the macro dialog. Select theCopyToNotepadmacro 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.
- User
error on this line of code: Set destinationWorkbook = Workbooks.Open(path & fileName)
- 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:-
File path or name is incorrect: Double-check the
pathandfileNamevariables 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). -
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.
-
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
pathandfileNamevariables 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.
-
- 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.
- 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 SubIn this modified code, rather than directly pasting each cell value into Notepad using
objShell.SendKeys, we first store the copied data in thedatastring variable. After the loop completes, we useobjShell.SendKeys datato 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.