georgecomstock
New Member
- Joined
- May 27, 2011
- Messages
- 18
Hi, running excel 2013 on a 64bit machine.
I have a workbook which, via the use of a few vba modules, scrapes web pages, pastes them into a worksheet and then parses the data which is then saved as single sheet csv files. The code below is how I get the data into excel. I suspect it is not the most efficient but it is the only way I know how. I wonder if this is the cause of the problem and offer it for inspection on that basis. If it is not the cause then what is I wonder?
The problem that I have is that even before any of the code in the modules has been run excel is so slow as to be all but unusable. That is, there is a delay of many seconds before excel responds to any mouse clicks or movement. If I try to step through the code again there is a delay of many seconds.
When the code is running it runs well, it takes about 6 seconds to get, paste, parse and save each web page, which seems acceptable to me, but it is when the code needs editing or amending that excel is slow to the point of exasperation.
If anyone can help I will be most grateful. Thanks.
I have a workbook which, via the use of a few vba modules, scrapes web pages, pastes them into a worksheet and then parses the data which is then saved as single sheet csv files. The code below is how I get the data into excel. I suspect it is not the most efficient but it is the only way I know how. I wonder if this is the cause of the problem and offer it for inspection on that basis. If it is not the cause then what is I wonder?
Code:
Public Sub PasteScrapedData(ByVal ws As Worksheet, urls As String, startid As Long, endid As Long)
Dim urlstring As String
For i = startid To endid
urlstring = urls & i
Call ClearContents
ws.Activate
sheetname = i
With ActiveSheet.QueryTables.Add(Connection:=urls & i, Destination:=Range("$A$1"))
'.CommandType = 0
.Name = _
"result"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
courseok = CheckCourseExists()
If courseok = False Then
GoTo LineLabel
End If
Call PutHeadings
Call GetRunners
Call GetDate
Call GetCourse
Call GetRaceClass
Call GetRaceAge
Call GetRaceDistance
Call GetRaceValues
Call GetRaceTime
Call GetRaceType
Call GetHorseName
Call GetDistanceBeaten
Call GetHeadGear 'this deals with the headgear and the horses weight
Call CalcWeight
Call GetTrainer
Call GetJockeys
Call PutFinishingPos
Call GetNarrative 'to show narrative on scraped page requires a button click
Call SaveAsCSV(sheetname)
LineLabel:
Next i
PutRaceId (sheetname)
End SubWhen the code is running it runs well, it takes about 6 seconds to get, paste, parse and save each web page, which seems acceptable to me, but it is when the code needs editing or amending that excel is slow to the point of exasperation.
If anyone can help I will be most grateful. Thanks.
