AI writing code

[volchara]

Мне нужно было синкить 2 екселовских файла, а самому лень. И чо вы думаете, таки чатжпт могЁт! Надо будет чутка поправить, но для начала уже ничего


create VBA script to sync 2 excel files by 1st column and adding additional columns and rows if missing in the 1st excel file from 2nd excel file

Sure! Here is an example VBA script that should accomplish what you described.

Assuming your first Excel file is called "ExcelFile1.xlsx" and has data in Sheet1, and your second Excel file is called "ExcelFile2.xlsx" and also has data in Sheet1.

This script will compare the first column of both sheets, and if a row exists in ExcelFile2.xlsx that does not exist in ExcelFile1.xlsx, it will add that row to ExcelFile1.xlsx and copy over any additional columns from ExcelFile2.xlsx.

VBA
Copy code

Sub SyncExcelFiles()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim j As Long
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim foundMatch As Boolean

'Set up the two workbooks and worksheets
Set wb1 = Workbooks.Open("C:\Path\To\ExcelFile1.xlsx")
Set wb2 = Workbooks.Open("C:\Path\To\ExcelFile2.xlsx")
Set ws1 = wb1.Sheets("Sheet1")
Set ws2 = wb2.Sheets("Sheet1")

'Get the last row of data in each sheet
lastrow1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
lastrow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row

'Loop through each row in ExcelFile2.xlsx
For i = 1 To lastrow2
foundMatch = False
'Loop through each row in ExcelFile1.xlsx
For j = 1 To lastrow1
'If there is a match on the first column, copy over additional columns
If ws2.Cells(i, 1).Value = ws1.Cells(j, 1).Value Then
foundMatch = True
'Copy over additional columns from ExcelFile2.xlsx
ws1.Cells(j, 2).Value = ws2.Cells(i, 2).Value 'Change 2 to the column number you want to copy over
ws1.Cells(j, 3).Value = ws2.Cells(i, 3).Value 'Change 3 to the column number you want to copy over
'Add more lines for additional columns as needed
Exit For
End If
Next j
'If there is no match in ExcelFile1.xlsx, add the row from ExcelFile2.xlsx
If Not foundMatch Then
lastrow1 = lastrow1 + 1
ws1.Cells(lastrow1, 1).Value = ws2.Cells(i, 1).Value
ws1.Cells(lastrow1, 2).Value = ws2.Cells(i, 2).Value 'Change 2 to the column number you want to copy over
ws1.Cells(lastrow1, 3).Value = ws2.Cells(i, 3).Value 'Change 3 to the column number you want to copy over
'Add more lines for additional columns as needed
End If
Next i

'Save and close the workbooks
wb1.Save
wb1.Close
wb2.Close

'Cleanup
Set ws1 = Nothing
Set ws2 = Nothing
Set wb1 = Nothing
Set wb2 = Nothing
End Sub


You will need to customize the file paths in the code to match the location of your files, and also update the column numbers to match the additional