+ Reply to Thread
Results 1 to 2 of 2

Help with code to scrape API when error occurs

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-20-2015
    Location
    NB, Canada
    MS-Off Ver
    365
    Posts
    164

    Help with code to scrape API when error occurs

    Hello

    I have this code that was kindly written for me. I have used it quite often with much success. Yesterday it was running and came back with a Run-time error (see pic 1) I'm confident this error came from the API source (probably from the amount of calls 12,000 total) and not my code. I am however, able to re-start the code directly after, and pull more data until it crashes again.

    Here's a briefing of what's happening:

    The code goes to the API page in column A and saves the JSON file in the directory in column B with the file name in column C.
    The code then loops through all the rows. (Column D isn't relevant for the code, but its where I list all the IDs needed for each page/call.)

    What is wondering is two fold. First, can the code be manipulated to restart if this error occurs, and secondly can it pick up where it left off in the loop?

    I will attach a couple of pictures and a sample file, along with the code.

    Thanks for your time, and input,
    Joe

    Sub update_game_id()
    
        
        Dim objWHTTP As Object
        Dim strPath As String
        Dim arrData() As Byte
        Dim lngFreeFile As Long
        Dim rangeA As Range
        Dim dirpath As String
        Dim filepath As String
    
        On Error Resume Next
            Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
            If Err.Number <> 0 Then
                Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
            End If
        On Error GoTo 0
        
     For Each rangeA In Range("a1", Range("a1").End(xlDown))
        strPath = rangeA
        dirpath = rangeA.Offset(0, 1)
        filepath = dirpath & rangeA.Offset(0, 2)
        
        objWHTTP.Open "GET", strPath, False
        objWHTTP.send
        arrData = objWHTTP.responseBody
        
        If Len(Dir(dirpath, vbDirectory)) = 0 Then
            MkDir (dirpath)
        End If
        
        lngFreeFile = FreeFile
        Open filepath For Binary Access Write As #lngFreeFile
            Put #lngFreeFile, 1, arrData
        Close #lngFreeFile
    Next rangeA
    
        Set objWHTTP = Nothing
        Erase arrData
        
        Beep
        MsgBox "Done"
            
    End Sub
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Help with code to scrape API when error occurs


    Hello,

    a sample in this thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code to display message box if error occurs
    By telhearne1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2022, 04:15 AM
  2. [SOLVED] VBA Code to email multiple people and code to go to the next email if error occurs
    By pmeltzers in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-17-2018, 12:31 PM
  3. VBA Code to scrape screen web page
    By torrmel9701 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2014, 09:41 PM
  4. VB code to scrape a few things from a page
    By cfcMalky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2014, 04:31 AM
  5. Scrape Data Using VBA From IE to Excel - Help with code
    By Galleon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2014, 11:00 AM
  6. [SOLVED] Run-time error '9': subscript out of range - error occurs on multiple computers except one
    By BrettE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 11:19 PM
  7. Screen Scrape Code
    By bpeyton in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2013, 02:57 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1