none
Excel keeps crashing when I run this macro that I need to download data from a site. Office 2013 RRS feed

  • Question

  • I'm currently trying to run the below Macro code, but excel keeps crashing whenever I try to run it. It doesn't crash in all the loops but after running through the for loop for a while it just crashes and restarts. 

    Sub GetFinData()
        Dim j As Integer
        Dim stname As String
        Dim i As Integer
        Dim jpos As Integer
        Dim istart As Integer
        Dim iend As Integer
        Sheets("Inputs").Activate
        jpos = Cells(8, 2).Value
        istart = Cells(4, 2).Value
        iend = Cells(5, 2).Value
        
        
        Application.ScreenUpdating = True

    '    Set xls = CreateObject("Excel.Application")

        j = jpos
        For i = istart To iend
        'ssheet = "Stock Price"


    '    Range("A1").Select
        Sheets("BSEStockList").Activate
        stcode = Cells(i, 1).Value
        stname = Cells(i, 3).Value
        Cells(3, 7).Value = stcode
        Cells(3, 5).Value = stname
        'stname = Sheets("BSEStockList").Range("E3").Value
        'stcode = Sheets("BSEStockList").Range("G3").Value
        'SCSVLink = "http://real-chart.finance.yahoo.com/table.csv?s=" & amficode & "&a=" & startmonth & "&b=" & startdate & "&c=" & startyear & "&d=" & endmonth & "&e=" & enddate & "&f=" & endyear & "&g=" & options & "&ignore=.csv"
        SCSVLink = Sheets("BSEStockList").Range("G2").Value

        
        sfile = "exportKR2CSV.html"
        Application.Wait Now + TimeValue("00:00:02")
        Set wnd = ActiveWindow
       
       ' Sheets(ssheet).Cells.ClearContents
        
             
        Workbooks.Open Filename:=SCSVLink
        
        
        
        Windows(sfile).Activate
        ActiveSheet.Cells.Copy
        wnd.Activate
        Sheets("temp").Activate
            

        'GetFromClipBoard
        'Sheets("NAV").Paste
        'Workbooks("Book3").
        Sheets("temp").Range("A1").Select
        Sheets("temp").Paste
        'Sheets("Stock Price").Columns(1).ColumnWidth = 10
        Application.DisplayAlerts = False
        Windows(sfile).Close True
        Application.DisplayAlerts = True
        'Application.ScreenUpdating = True
        
          
         j = j + 1
       Sheets("FinData").Activate
        'Columns("A:A").Select
        'Columns("A:A").EntireColumn.AutoFit
        Range("A1").Select
        Cells(j, 1).Value = stname
        Cells(j, 2).Value = stcode
        Sheets("temp").Activate
        Range(Cells(4, 2), Cells(4, 13)).Select
        Selection.Copy
        Sheets("FinData").Activate

        Cells(j, 3).Value = "Sales"
        Range(Cells(j, 4), Cells(j, 14)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        j = j + 1
        Sheets("temp").Activate
        Range(Cells(8, 2), Cells(8, 13)).Select
        Selection.Copy
        Sheets("FinData").Activate
        Cells(j, 1).Value = stname
        Cells(j, 2).Value = stcode
        Cells(j, 3).Value = "Net Income"
        Range(Cells(j, 4), Cells(j, 14)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

        j = j + 1
        Sheets("temp").Activate
        Range(Cells(9, 2), Cells(9, 13)).Select
        Selection.Copy
        Sheets("FinData").Activate
        Cells(j, 1).Value = stname
        Cells(j, 2).Value = stcode
        Cells(j, 3).Value = "EPS"
        Range(Cells(j, 4), Cells(j, 14)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        j = j + 1
        Sheets("temp").Activate
        Range(Cells(17, 2), Cells(17, 13)).Select
        Selection.Copy
        Sheets("FinData").Activate
        Cells(j, 1).Value = stname
        Cells(j, 2).Value = stcode
        Cells(j, 3).Value = "FCF/Share"
        Range(Cells(j, 4), Cells(j, 14)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False



        j = j + 1
        Sheets("temp").Activate
        Range(Cells(38, 2), Cells(38, 13)).Select
        Selection.Copy
        Sheets("FinData").Activate
        Cells(j, 1).Value = stname
        Cells(j, 2).Value = stcode
        Cells(j, 3).Value = "ROE"
        Range(Cells(j, 4), Cells(j, 14)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
     

        j = j + 1
        Sheets("temp").Activate
        Range(Cells(39, 2), Cells(39, 13)).Select
        Selection.Copy
        Sheets("FinData").Activate
        Cells(j, 1).Value = stname
        Cells(j, 2).Value = stcode
        Cells(j, 3).Value = "ROC"
        Range(Cells(j, 4), Cells(j, 14)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        
        j = j + 1
        Sheets("temp").Activate
        Range(Cells(100, 2), Cells(100, 13)).Select
        Selection.Copy
        Sheets("FinData").Activate
        Cells(j, 1).Value = stname
        Cells(j, 2).Value = stcode
        Cells(j, 3).Value = "Debt/Equity"
        Range(Cells(j, 4), Cells(j, 14)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        Sheets("temp").Activate
        Range("A1:M500").Select
        Selection.ClearContents

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        ActiveWorkbook.CheckCompatibility = False
        
        ActiveWorkbook.Save

        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        ActiveWorkbook.CheckCompatibility = True
        
    Next i
    Application.ScreenUpdating = True
    Sheets("FinData").Activate
    Columns("A:A").EntireColumn.AutoFit


    End Sub


    Friday, August 26, 2016 3:28 PM

Answers

All replies