VBA to update one sheet from data stored in another workbook

Lorum

New Member
Joined
Aug 11, 2020
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Hello there!

How are you? I hope you can help me. I'm new in VBA and I have an issue I can't resolve. I'll try to explain what I need.

I have Workbook A and Workbook B.

Data in Workbook A is updated by clicking a button. It takes information from another Workbook B that is generated by an external program in extension .xlsx.

  1. DAY 1. I exported my Workbook B by using my external program.

DATEIDTYPETIME
11/08/202080205029BAT10:50
11/08/202080206789DAT10:51
16/08/202080205029BAT17:12
20/08/202080976534BAT23:42
21/08/202080212456TAT07:25

2. DAY 1. Then I run my macro and I get that data into my Workbook A. The Description column is treated later and is not included in Workbook B.

DATEIDDESCRIPTIONTYPETIME
11/08/202080205029BAT10:50
11/08/202080206789DAT10:51
16/08/202080205029BAT17:12
20/08/202080976534BAT23:42
21/08/202080212456TAT07:25

2. DAY 3. I entered and complete columns in Workbook A.

DATEIDDESCRIPTIONTYPETIME
11/08/202080205029Everything OKBAT10:50
11/08/202080206789RequestedDAT10:51
16/08/202080205029Everything OKBAT17:12
20/08/202080976534Nothing to doBAT23:42
21/08/202080212456RequestedTAT07:25

3. DAY 7. The program has generated a new Workbook B with new data. Some new rows could have been added in Workbook B, and even some cells in the TYPE column has been changed for one item already inserted in Workbook A.

DATEIDTYPETIME
11/08/202080205029BAT10:50
11/08/202080206789BAT14:07
11/08/202080206789DAT10:51
18/08/202080003567DAT13:18
20/08/202080976534TAT23:42
21/08/202080212456TAT07:25
22/08/202080009345BAT05:52
24/08/202080195642DAT11:24

Notice what happened:

  • A new line appeared in ROW 2 with same day, same ID but it is a different TIME, not necessarily sorted.
  • The ROW containing DATE 16/08/2020 and ID 80205029 has disappeared.
  • A new line appeared in ROW 4 with new data.
  • The TYPE cell has changed its value for ROW 5.
  • New lines appear at the end.
What I want is Worksheet A to be updated like this:


DATEIDDESCRIPTIONTYPETIME
11/08/202080205029Everything OKBAT10:50
11/08/202080206789BAT14:07
11/08/202080206789RequestedDAT10:51
18/08/202080003567DAT13:18
20/08/202080976534Nothing to doTAT23:42
21/08/202080212456RequestedTAT07:25
22/08/202080009345BAT05:52
24/08/202080195642DAT11:24

So I want the update to add new rows in the right order, not to delete the rows that have been previously inserted and have disappeared in the new Worksheet B exported, and to change the values of those cells that have changed in the TYPE column.

I have tried plenty of things, the last was a double FOR but it is bad coding.

VBA Code:
Sub Update()

Dim lastRowScr As Integer, lastRowLocal As Integer, nRowsSrc As Integer, nRowsLocal As Integer, x As Integer, _
y As Integer

    Application.ScreenUpdating = False
    
       Set closedBook = Workbooks.Open("C:\Users\mfortesg\Documents\Suivi d'Analyses AT\Projet - Automatisation\BO\BO.xlsx")
      
       lastRowScr = closedBook.Sheets(1).Cells.Find(What:="*", SearchDirection:=xlPrevious).Row - 2
       nRowsScr = lastRowScr - 16
      
       lastRowLocal = ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
       nRowsLocal = lastRowLocal - 2
      
       If nRowsLocal = 0 Then
      
            For x = 17 To lastRowScr
            
                y = x - 14
                        
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 1).Value = closedBook.Sheets(1).Cells(x, 1).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 2).Value = closedBook.Sheets(1).Cells(x, 2).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 3).Value = closedBook.Sheets(1).Cells(x, 3).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 4).Value = closedBook.Sheets(1).Cells(x, 4).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 5).Value = closedBook.Sheets(1).Cells(x, 17).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 6).Value = closedBook.Sheets(1).Cells(x, 11).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 7).Value = closedBook.Sheets(1).Cells(x, 10).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 8).Value = closedBook.Sheets(1).Cells(x, 26).Value
                ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 24).Value = Replace(closedBook.Sheets(1).Cells(x, 28).Value, ",", ":")
            
            Next x
            
            MsgBox ("Le Tableau d'Analyse AT a été mis à jour correctement.")
        
            Else
            
            For x = 17 To lastRowScr
    
                For y = x - 14 To lastRowLocal + 1
                    
                    If ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 1).Value = closedBook.Sheets(1).Cells(x, 1).Value And _
                       ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 2).Value = closedBook.Sheets(1).Cells(x, 2).Value And _
                       ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 24).Value = Replace(closedBook.Sheets(1).Cells(x, 28).Value, ",", ":") Then
                    
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 6).Value = closedBook.Sheets(1).Cells(x, 11).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(y, 5).Value = closedBook.Sheets(1).Cells(x, 17).Value
                        
                    ElseIf y = lastRowLocal + 1 Then
                                    
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 1).Value = closedBook.Sheets(1).Cells(x, 1).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 2).Value = closedBook.Sheets(1).Cells(x, 2).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 3).Value = closedBook.Sheets(1).Cells(x, 3).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 4).Value = closedBook.Sheets(1).Cells(x, 4).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 5).Value = closedBook.Sheets(1).Cells(x, 17).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 6).Value = closedBook.Sheets(1).Cells(x, 11).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 7).Value = closedBook.Sheets(1).Cells(x, 10).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 8).Value = closedBook.Sheets(1).Cells(x, 26).Value
                        ThisWorkbook.Sheets("Tableau d'Analyse AT").Cells(x - 14, 24).Value = Replace(closedBook.Sheets(1).Cells(x, 28).Value, ",", ":")
                    
                    End If
                    
                Next y
            
            Next x
            
            MsgBox ("Le Tableau d'Analyse AT a été mis à jour correctement.")
        
        End If
            
       closedBook.Close SaveChanges:=False

    
    Application.ScreenUpdating = True

End Sub

Can anyone help me? Please!

Thanks in advance.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,184
Members
410,731
Latest member
keobongmacao
Top