Not all rows of data from csv file is retrieved in excel-CodePudding

So i'm working with my colleagues code and right now he was written up a macro that gets data from an api that is in a csv format. The code is supposed to gather this data, enter it into a new workbook then gather the data from the workbook, putting it into an array and place it into its final worksheet. The problem is that the data is entered into a new workbook fine but when it comes time to collect the data to put it into the final worksheet only the first 500 rows of data are copied over to the final worksheet when there is 50000 rows of data. here is the vba code

Sub UpdateAllData()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


Dim strEnappsysPriceForecastAPI As String


Dim arrEnappsysPriceForecast() As Variant 'Enappsys DA Forecast Price API Data


strEnappsysPriceForecastAPI = Range("EnappsysPriceForecast_API")

'-------
'EnappsysPriceForecastData
'-------

strWorkbook = strEnappsysPriceForecastAPI

Workbooks.Open strWorkbook

arrEnappsysPriceForecast = Range("A1:Z50000")

ActiveWorkbook.Close

Range("EnappsysPriceForecastImport") = arrEnappsysPriceForecast

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

Sheets("CONFIG").Select
MsgBox ("All Available Data Updated")

End Sub

Any help is greatly appreciated thank you.

CodePudding user response：

This should be a bit more robust:

Sub UpdateAllData()
    Const NUM_COLS As Long = 26 'how many columns we're copying
    Dim strEnappsysPriceForecastAPI As String, wb As Workbook
    Dim data As Variant, lr As Long, wsData As Worksheet
    
    GoFast
    
    Set wb = Workbooks.Open(Range("EnappsysPriceForecast_API").Value)
    Set wsData = wb.Worksheets(1)
    lr = LastUsedRow(wsData)    'last row of data
    If lr > 0 Then              'have data?
        data = wsData.Range("A1").Resize(lr, NUM_COLS).Value
        With Range("EnappsysPriceForecastImport")
            .ClearContents      'clear previous data
            .Cells(1).Resize(UBound(data, 1), NUM_COLS).Value = data
        End With
    End If 'have any data
    
    wb.Close
    
    GoFast False
    
    ThisWorkbook.Worksheets("CONFIG").Select
    MsgBox "All Available Data Updated"

End Sub

'maximize code speed by turning off unneeded stuff
'******** must reset !!!!
Sub GoFast(Optional bYesNo As Boolean = True)
  With Application
      .ScreenUpdating = Not bYesNo
      .Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
  End With
End Sub

'Return the last used row on a worksheet (or zero if the sheet is empty)
Function LastUsedRow(ws As Worksheet) As Long
    Dim f As Range
    Set f = ws.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
                    LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, MatchCase:=False)
    If Not f Is Nothing Then LastUsedRow = f.Row 'else zero
End Function