Hello everyone,
I have routine that loops through the cells in the column "K" with stock symbols and opens corresponding to them files in the folder. I'd like to modify code so that during runtime it would show progress in the status bar. Any help would be appreciated. Thank you in advance.
Code
Sub DownloadHistorPrices()
Const directory As String = "C:\VBA\" 'Directory where all corresponding to stocks CSV files stored
Const FileExt As String = ".csv" 'File extensions
Dim cell As Range
Dim myfile As String
Dim wb As Workbook
On Error Resume Next 'MIGHT BE HIDING AN ERROR CAUSING YOUR PROBLEM
With ThisWorkbook.Worksheets("Sheet1")
For Each cell In .Range("K1:K10000") 'Column with the list of stocks
If cell.Value <> "" Then
.Range("S5").Value = cell.Value 'Cell S5 contains current stock symbol
Call GetYahooDataFromJSON 'This macros download historical price data
'Cell S5 = value = stock symbol
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = True
Application.DisplayAlerts = False
ThisWorkbook.Sheets("Sheet1").Range("A2", Range("A2").End(xlDown).End(xlToRight)).Copy
myfile = directory & Cells(5, 19).Value & FileExt 'Path to the current CSV file to be opened
Set wb = Application.Workbooks.Open(Filename:=myfile)
If Not wb Is Nothing Then 'IF FILE EXISTS THEN
wb.ActiveSheet.Range("B2").PasteSpecial 'Destination range in the opened CSV file
wb.Close True
Else
' cell.Offset(, 2).Value = "File not found for " & cell.Value
cell.Offset(, 2).Value = cell.Value
End If
End If
Next cell
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub
