Results 1 to 1 of 1

Thread: Temp save - Old code

  1. #1
    Junior Member
    Join Date
    Sep 2017
    Posts
    1
    Rep Power
    0

    Temp save - Old code

    Old scraping algorithm.



    Code:
    Sub Get_Odds_Data(ByRef CurrentSheet As String)
        
        Dim URL As String
        Dim XMLreq As Object
        Dim HTMLdoc As Object
        Dim OddsTable As Object
        Dim tableRows As Object
        Dim tableCell As Object
        Dim dest As Range
        Dim OddsData As Variant
        Dim HTMLrow As Integer, i As Integer, C As Integer
        Dim AString As String
        Dim AnInt As Integer
        
        
    
        Set dest = Sheets(CurrentSheet).Cells(31, 1)
        dest.Parent.Activate
        
        On Error GoTo keeppreviousread
    
        URL = Sheets(CurrentSheet).Cells(1, 27)
        Set XMLreq = CreateObject("MSXML2.XMLhttp")
    
        HTMLrow = 0
        
            With XMLreq
                Debug.Print Now, URL
                .Open "GET", URL, False
                .send
                Set HTMLdoc = CreateObject("HTMLFile")
                HTMLdoc.body.innerHTML = .responseText
            End With
            
            'Extract  table into array
            
            Set OddsTable = HTMLdoc.GetElementById("t1") '
            Set tableRows = OddsTable.Rows
            ReDim OddsData(1 To tableRows.Length - HTMLrow + 1, 1 To tableRows(HTMLrow).Cells.Length)
                   
            i = 1
            While HTMLrow < tableRows.Length
                C = 1
                AString = tableRows(HTMLrow).Cells(0).innerText
                If IsNumeric(Mid(AString, 1, 1)) Then
                     FirstCell = True
                     For Each tableCell In tableRows(HTMLrow).Cells
                        If FirstCell <> True Then                      ' ignore first cell in row
                            OddsData(i, C) = tableCell.innerText
                            C = C + 1
                        End If
                        FirstCell = False
                     Next
                     i = i + 1
                End If
                HTMLrow = HTMLrow + 1
    
            Wend
            
            Worksheets(CurrentSheet).Range("A31:AA60").Clear
            'Copy array to sheet cells
            Set dest = dest.Offset(0, 0)
            dest.Resize(UBound(OddsData, 1), UBound(OddsData, 2)).Value = OddsData
            DoEvents
            Set XMLreq = Nothing
            Set HTMLdoc = Nothing
            Set OddsTable = Nothing
            Set tableRows = Nothing
            Set tableCell = Nothing
      
            Exit Sub
    keeppreviousread:
    Sheets(CurrentSheet).Cells(25, 2) = "xxxxxxxx"
            
    End Sub
    __________________________________________________ ____________
    Test
    VBA Range.Sort with arrays. Alternative for simple use
    .


    I am considering a fairly simple VBA Array alternative approach to a simple use of the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/off...cel.range.sort

    In an important file of mine, I currently use the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/off...cel.range.sort
    It lets me sort some long rows of data where the order is based on the values in one of the columns , then for any rows which have the same value in that column , those similar rows would be further sorted by another column. For the VBA Range.Sort Method the syntax terminology would say that I am using pseudo code of this sort of form
    MyRange.Sort Key1:=use column1 , __ , Key2:=use columnx , __ , __ , __ ,
    You can use up to 3 “Keyxs” in the simple single code line.
    In other words, if you have some identical values in the columns used to define the final list order, you can sort further using the values in a second column to determine the order in the group of identical values in the first column which you used. In the simple single line use of the available VBA Range.Sort Method, you can do that one more time.##

    Here is a simple graphical illustration of what I am talking about: Consider this spreadsheet range:
    Before:-
    _____[/color ( Using Excel 2007 32 bit )
    Row\Col
    G
    H
    I
    J
    K
    13
    Was G13 C Was I13 Was J13 Was K13
    14
    Was G14 a Was I14 Was J14 Was K14
    15
    Was G15 g Was I15 c e
    16
    Was G16 g Was I16 b Was K16
    17
    Was G17 g Was I17 c f
    18
    Was G18 G Was I18 c Was K18
    19
    Was G19 f Was I19 Was J19 Was K19
    Worksheet: Sorting
    I will run this simple procedure, ( which is in a normal code module. When I run the procedure the worksheet with Name “Sorting” is active).
    Code:
    Sub RangeSortExample()
     range("G13:K19").Sort Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending, Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending, MatchCase:=False
    End Sub
    Here is a break down of what that routine does:
    The first key ,
    Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending , MatchCase:=False
    , results in this
    _____ ( Using Excel 2007 32 bit )
    Row\Col
    G
    H
    I
    J
    K
    13
    Was G14 a Was I14 Was J14 Was K14
    14
    Was G13 C Was I13 Was J13 Was K13
    15
    Was G19 f Was I19 Was J19 Was K19
    16
    Was G15 g Was I15 c e
    17
    Was G16 g Was I16 b Was K16
    18
    Was G17 g Was I17 c f
    19
    Was G18 G Was I18 c Was K18
    Worksheet: Sorting
    The above screen shows that we have all ordered ( based on the “B” columns of that range , ( which is the column H of the spreadsheet). But note, we have duplicates in column 2 with values of g in the last 4 rows.
    This next part ,
    Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, MatchCase:=False
    , then sorts those last 4 rows using column J values.
    _____ ( Using Excel 2007 32 bit )
    Row\Col
    G
    H
    I
    J
    K
    13
    Was G14 a Was I14 Was J14 Was K14
    14
    Was G13 C Was I13 Was J13 Was K13
    15
    Was G19 f Was I19 Was J19 Was K19
    16
    Was G16 g Was I16 b Was K16
    17
    Was G15 g Was I15 c e
    18
    Was G17 g Was I17 c f
    19
    Was G18 G Was I18 c Was K18
    Worksheet: Sorting
    In the above screen shot we see that we now have 3 rows containing all gs in the second column, and all cs in the forth column.
    The final term is, noting that we are using xlDescending ,
    Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending
    So the last 3 rows are resorted such as to give in those last 3 rows a descending order:
    _____ ( Using Excel 2007 32 bit )
    Row\Col
    G
    H
    I
    J
    K
    13
    Was G14 a Was I14 Was J14 Was K14
    14
    Was G13 C Was I13 Was J13 Was K13
    15
    Was G19 f Was I19 Was J19 Was K19
    16
    Was G16 g Was I16 b Was K16
    17
    Was G18 G Was I18 c Was K18
    18
    Was G17 g Was I17 c f
    19
    Was G15 g Was I15 c e
    Worksheet: Sorting


    For simplicity I chose to be case insensitive ( so like g = G __ - _ MatchCase:=False )
    Some other argument options are available with the available VBA Range.Sort Method. I will not consider those.
    ## Furthermore, If you add the options on other lines then use a .Apply code line to apply the sort, then you can have more than 3 “Keys

    I am only considering a comparison to the simple single line using minimal options similar to the worked example

    Why do an array alternative?.
    The .Sort Method appears to be regarded as a good way to do these things, and often a solution for sorting arrays is based on pasting into a worksheet Range , then using the Range.Sort on that and then capturing the sorted range back into the original range

    But I was just interested out of interest to have an alternative.

    Various sorting methods, mathematical ways to sort..
    The various methods of sorting seem immense and need a good understanding of advanced mathematics. I can’t begin to discuss any of that. I will use exclusively the simply “Bubble sort method” , which I will explain from the simplest form, and then adapt to our case

    The next posts will go through the simplest Bubble sort theory as applied to a simple 2 dimensional array, and then progress in the following posts to a Function to do similar to the simple case of a VBA Range.Sort Method with the possibility to add keys, for sorting further when values in the initial column used for sorting are identical
    For ease of explanation I will refer to the first dimensions in an array as the rows, and the second dimensions as columns , pseudo such that an array could be thought of as a spreadsheet of values. For example a 2 dimensional array of 5 x 2 could be pictorially considered as 5 rows x 2 columns:



    r1, c1 r1, c2
    r2, c1 r2, c2
    r3, c1 r3, c2
    r4, c1 r4, c2
    r5, c1 r5, c2
    Last edited by DocAElstein; 02-18-2019 at 11:27 PM. Reason: Code Tags

Similar Threads

  1. Replies: 1
    Last Post: 02-19-2015, 03:49 AM
  2. How To Save Macro To Personal Workbook
    By NITIN SHETTY in forum Excel Help
    Replies: 1
    Last Post: 04-07-2013, 01:07 PM
  3. Save Processed Files Into Different Another Folder
    By DARSHANKmandya in forum Excel Help
    Replies: 1
    Last Post: 03-22-2013, 07:10 PM
  4. Date References to save files using VBA Code
    By mrmmickle1 in forum Excel Help
    Replies: 3
    Last Post: 11-28-2012, 05:48 PM
  5. Save Chart As A Picture VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-14-2011, 04:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •