Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I was using MS Office 7 and macro was running faster. However ever since I moved to office 365 64bit, Macro became dead slow to fetch data. I use below code to pull stock data from a site, where I used to pull 150 stock data within 5 to 10 min office 07. It takes more than 15 min to fetch single stock data in office 360. Is there any solution for excel in office 365?. Any advise will be much helpful.

Sub TestCode()

Dim qt As QueryTable
Dim URL As String
Dim intI As Integer
'This is the direct link, but I use variable data in loop

URL = "https://www1.nseindia.com/products/dynaContent/equities/equities/histscrip.jsp?symbolCode=233&symbol=TITAN&symbol=TITAN&segmentLink=17&symbolCount=1&series=ALL&dateRange=1month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE"

Set qt = Sheet1.QueryTables.Add(Connection:="URL;" & URL, Destination:=Sheet1.Cells(1, 1))
        With qt
            .RefreshOnFileOpen = True
            .FieldNames = True
            .WebSelectionType = xlSpecifiedTables
            .WebTables = 1
            .Refresh BackgroundQuery:=False
        End With

For Each qt In Sheet1.QueryTables
    qt.Delete
Next qt

    For intI = ActiveWorkbook.Connections.Count To 1 Step -1
         ActiveWorkbook.Connections.Item(intI).Delete
    Next
    
End Sub


What I have tried:

I tried to change few settings; however issue is still persist.
Posted
Updated 2-Dec-20 5:57am
Comments
Richard Deeming 2-Dec-20 12:22pm    
"I was using MS Office 7 ..."

Are you sure about that? MS Office v7.0 was commonly called Microsoft Office 95, which was released in August 1995.

Upgrading to a version 25 years newer is unlikely to be trivial.
Sunil T 2-Dec-20 12:29pm    
Sorry, It was Office 2010
Dave Kreskowiak 2-Dec-20 13:55pm    
This sounds like it's more appropriate to call Microsoft Support on it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900