+ Reply to Thread
Results 1 to 2 of 2

VBA Procedure too large error

Hybrid View

  1. #1
    Registered User
    Join Date
    01-09-2020
    Location
    India
    MS-Off Ver
    2007
    Posts
    2

    VBA Procedure too large error

    I am using the VBA code for importing HTML data in Excel Sheet based on the cell value. For each row, I am using repetitive code. This code is working fine up to 94 rows and after 94th row, it giving error VBA Procedure Too Large. I want this code to work for more than 600 rows. Please help. Sample sheet is attached.
    Attached Files Attached Files

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,978

    Re: VBA Procedure too large error

    You need to refactor the code. Try something like this:

    Sub GetTable()
    
       Dim ieApp As InternetExplorer
       Dim url As String
       Dim myPoints As String
    
       'create a new instance of ie
       Set ieApp = New InternetExplorer
    
       'you don’t need this, but it’s good for debugging
       ieApp.Visible = True
    
       'assume we’re not logged in and just go directly to the login page
       ieApp.Navigate "https://icms.indianrail.gov.in/reports/"
       Do While ieApp.Busy: DoEvents: Loop
       Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    
       Set ieDoc = ieApp.Document
    
       'fill in the login form – View Source from your browser to get the control names
       With ieDoc.forms(0)
          .UserId.Value = "abcdefg"
          .Password.Value = "123456"
          .submit
       End With
       Do While ieApp.Busy: DoEvents: Loop
       Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    
       Dim n As Long
       For n = 2 To 600
          Get_IE_Output ieApp, Sheet1.Cells(n, "B").Value, Sheet1.Cells(n, "D")
       Next n
       ieApp.Quit
    End Sub
    Sub Get_IE_Output(ieApp As InternetExplorer, trainNo, outputRange As Range)
    'now that we’re in, go to the page we want
       ieApp.Navigate "https://icms.indianrail.gov.in/reports/ReportServlet?reportAction=Utility&reportType=LocoCurrStatus&subAction=main"
       Do While ieApp.Busy: DoEvents: Loop
       Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
       Dim ieDoc As Object
    
       'get the table based on the table’s id
       Set ieDoc = ieApp.Document
       With ieDoc.forms(0)
          .trainNo.Value = trainNo
          .startDate.Value = Format(Date - 1, "dd-mmm-yyyy")
          .submit
       End With
       Do While ieApp.Busy: DoEvents: Loop
       Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    
       Set ieDoc = ieApp.Document
       Dim ieTable As Object
       Set ieTable = ieDoc.all.Item("TABLE_6")
    
       'copy the tables html to the clipboard and paste to teh sheet
       If Not ieTable Is Nothing Then
          Dim clip As DataObject
          Set clip = New DataObject
          clip.SetText "" & ieTable.outerHTML & ""
          clip.PutInClipboard
          With outputRange.Worksheet
             .Select
             outputRange.Select
             .PasteSpecial "Unicode Text"
          End With
       End If
    
    End Sub
    Everyone who confuses correlation and causation ends up dead.

+ 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. Too large Procedure Error
    By Biplab1985 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2017, 10:11 AM
  2. [SOLVED] Procedure too large error
    By luizmachado in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2016, 03:36 PM
  3. [SOLVED] Procedure Too Large Error VBA
    By Rudidw in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-19-2015, 06:18 AM
  4. Excel Macro Procedure Too Large Error - Help
    By sanjaydutta2002 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-29-2015, 05:25 AM
  5. [SOLVED] Procedure Too Large Error...
    By ekf23 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-11-2014, 10:53 AM
  6. Replies: 2
    Last Post: 06-06-2008, 12:54 PM
  7. [SOLVED] Importing HTML data into excel
    By Mike in forum Excel General
    Replies: 0
    Last Post: 01-31-2006, 10:55 PM

Tags for this Thread

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