UseLessFuel
New Member
- Joined
- Dec 22, 2012
- Messages
- 37
Hi.
I occasionaly download a .csv file which contains exactly one year of 1-minute interval data, held in 8 columns (A to H) and copy the data that I do not already have into a Master .xlsx file. The csv data looks like this:
<tbody>
</tbody>
The Master .xlsx file looks the same (same variables in same columns) but I only need to append the latest data (based on the timestamp) from the csv file into the xlsx file, which is typically the last 43,000 rows of the csv data, at a time.
I was kindly provided with VBA code (below) which has worked, but I think the loop takes too long to execute when there is a large number of rows to copy. Does anyone know of a more efficient way to append only the latest csv data into an xlsx file?
'Appends new data to Master files. You need to have the Master file open
'as well as the downloaded csv file
'
Sub Append()
Dim curworkbook As Workbook
Dim curworksheet As Worksheet
Dim wb As Workbook
Dim ws As Worksheet
Dim Curlastrow As Long
Dim CSVlastrow As Long
Curlastrow = Cells(Rows.Count, 1).End(xlUp).Row
'
'Copy the filename of the Workbook,and overwrite SH005 Trial VBA.xlsx, below
'
Set curworkbook = Workbooks("SH005 Trial VBA.xlsx")
'
'Copy the name of the Worksheet by double clicking the worksheet tab and pressing Ctrl C.
'Now paste it on top of Master data, below
'
Set curworksheet = Worksheets("Master data")
'
'You should not have to change the Workbook and Worksheet filenames below as
'they are all the same when you first download the csv
'
Set wb = Workbooks("CSVReport.csv")
Set ws = wb.Worksheets("CSVReport")
CSVlastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
maxdate = Application.WorksheetFunction.Max(Range(Cells(2, 1), Cells(Curlastrow, 1)))
For i = 2 To CSVlastrow
If ws.Cells(i, 1).Value > maxdate Then
ws.Cells(i, 1).EntireRow.Copy Destination:=curworksheet.Cells(Curlastrow + 1, 1)
Curlastrow = Curlastrow + 1
End If
Next i
End Sub
I occasionaly download a .csv file which contains exactly one year of 1-minute interval data, held in 8 columns (A to H) and copy the data that I do not already have into a Master .xlsx file. The csv data looks like this:
| A | B | C | D | E | F | G | H | |
| 1 | Timestamp | Z1 | Z2 | Out | Flo | EnC | Enp | OpMo |
| 2 | 09/05/2017 14:50 | 17 | 18 | 10 | 30 | 8 | 24 | water |
| 3 | 09/05/2017 14:51 | 17 | 17 | 10 | 28 | 9 | 27 | water |
<tbody>
</tbody>
The Master .xlsx file looks the same (same variables in same columns) but I only need to append the latest data (based on the timestamp) from the csv file into the xlsx file, which is typically the last 43,000 rows of the csv data, at a time.
I was kindly provided with VBA code (below) which has worked, but I think the loop takes too long to execute when there is a large number of rows to copy. Does anyone know of a more efficient way to append only the latest csv data into an xlsx file?
'Appends new data to Master files. You need to have the Master file open
'as well as the downloaded csv file
'
Sub Append()
Dim curworkbook As Workbook
Dim curworksheet As Worksheet
Dim wb As Workbook
Dim ws As Worksheet
Dim Curlastrow As Long
Dim CSVlastrow As Long
Curlastrow = Cells(Rows.Count, 1).End(xlUp).Row
'
'Copy the filename of the Workbook,and overwrite SH005 Trial VBA.xlsx, below
'
Set curworkbook = Workbooks("SH005 Trial VBA.xlsx")
'
'Copy the name of the Worksheet by double clicking the worksheet tab and pressing Ctrl C.
'Now paste it on top of Master data, below
'
Set curworksheet = Worksheets("Master data")
'
'You should not have to change the Workbook and Worksheet filenames below as
'they are all the same when you first download the csv
'
Set wb = Workbooks("CSVReport.csv")
Set ws = wb.Worksheets("CSVReport")
CSVlastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
maxdate = Application.WorksheetFunction.Max(Range(Cells(2, 1), Cells(Curlastrow, 1)))
For i = 2 To CSVlastrow
If ws.Cells(i, 1).Value > maxdate Then
ws.Cells(i, 1).EntireRow.Copy Destination:=curworksheet.Cells(Curlastrow + 1, 1)
Curlastrow = Curlastrow + 1
End If
Next i
End Sub
