VB to Data update

[Codicesegreto]

I'm trying to create a macro that updates the data.

Example imagining that I have acquired on the excel sheet the data contained in a txt file that goes from 2015/6/4 to 2018/4/30 and subsequently we update the excel sheet with a file TXT containing data from 2015/6/4 to 2018/5/11, but we wanted to update in Excel only the missing data ranging from 2018/4/30 to 2018/5/11 ...


How can it be done ?

___
Antonio

 

[offthelip]

You haven't said where any of the data is , so I have assumed that your existing data is on sheet1 with the dates in column A. I have assumed that the new data is on sheet2 with a header which I use to determine how many columns to copy accross and with the dates in column A

Sub test()
Dim outarr() As Variant
With Worksheets("sheet2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
lastcol2 = .Cells(1, .Columns.Count).End(xlToLeft).Column
datar = Range(.Cells(1, 1), .Cells(lastrow2, lastcol2))
End With
With Worksheets("sheet1")
lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
lastdate = Range(.Cells(lastrow1, 1), .Cells(lastrow1, 1))
frst = True
For i = 2 To lastrow2
 If datar(i, 1) > lastdate Then
  ' we have found the first date with new data
   If frst Then
    ReDim outarr(1 To lastrow2 - i + 1, 1 To lastcol2)
    frst = False
    indi = 1
   End If
   For k = 1 To lastcol2
      ' copy the data
      outarr(indi, k) = datar(i, k)
   Next k
   indi = indi + 1
 End If
Next i
 
Range(.Cells(lastrow1 + 1, 1), .Cells(lastrow1 + indi - 1, lastcol2)) = outarr
End With
End Sub

 

[Codicesegreto]

You haven't said where any of the data is , so I have assumed that your existing data is on sheet1 with the dates in column A. I have assumed that the new data is on sheet2 with a header which I use to determine how many columns to copy accross and with the dates in column A

Sub test()
Dim outarr() As Variant
With Worksheets("sheet2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
lastcol2 = .Cells(1, .Columns.Count).End(xlToLeft).Column
datar = Range(.Cells(1, 1), .Cells(lastrow2, lastcol2))
End With
With Worksheets("sheet1")
lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
lastdate = Range(.Cells(lastrow1, 1), .Cells(lastrow1, 1))
frst = True
For i = 2 To lastrow2
 If datar(i, 1) > lastdate Then
  ' we have found the first date with new data
   If frst Then
    ReDim outarr(1 To lastrow2 - i + 1, 1 To lastcol2)
    frst = False
    indi = 1
   End If
   For k = 1 To lastcol2
      ' copy the data
      outarr(indi, k) = datar(i, k)
   Next k
   indi = indi + 1
 End If
Next i
 
Range(.Cells(lastrow1 + 1, 1), .Cells(lastrow1 + indi - 1, lastcol2)) = outarr
End With
End Sub

Many thanks, I try it now even though I think it will go slow. I'll let you know

 

[Codicesegreto]

Many thanks, I try it now even though I think it will go slow. I'll let you know

I tried to create the macro but I could not make it work.
I forward you the files I probably made mistakes in writing your code.
NewAnalysis_virgin is structured with the buttons

1. Upload data: acquires data from the txt file and places it in the History folder starting from line 32000
2. Delete data: delete the History
3. Update data: must import the last data contained in the txt (Example imagining that I have acquired on the excel sheet the data contained in a txt file that goes from 2015/6/4 to 2018/4/30 and subsequently we update the excel sheet with a file TXT containing data from 2015/6/4 to 2018/5/11, but we wanted to update in Excel only the missing data ranging from 2018/4/30 to 2018/5/11 ...)

I also want to tell you that in the History file the latest updated data should not exceed 32000 row

https://www.dropbox.com/sh/719gcnwyod3wdnp/AADJQLxzEVVm5Qft4ep5t1Yla?dl=0

In this folder you will find the files

• NewAnalysis_virgin
• Txt Data from 2015.6.4 to 2018.4.30
• Txt Data from 2015.6.4 to 2018.5.11

----
Antonio