Import data from different webpages in one in MS Excel sheet using VBA

Question

I am using VBA code in MS Excel for importing some data from three different webpages. At present I am able to import data in separate excel sheet for each webpage and further joining them in one sheet using another VBA. VBA code is as under:

Sub GetTable()

     Dim ieApp As InternetExplorer
     Dim ieDoc As Object
     Dim ieTable As Object
     Dim clip As DataObject

     '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 "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/LoginAction.do?hmode=loginPage"
     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
    .getElementById("userId").setAttribute "value", "rlbdgs"
    .getElementById("userPassword").setAttribute "value", "123"

    '~~> This will select the 2nd radio button as it is `0` based
    .getElementsByName("userType")(1).Checked = True

    .getElementById("hmode").Click
     End With
     Do While ieApp.Busy: DoEvents: Loop
     Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

     'now that we’re in, go to the page we want
     ieApp.Navigate "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/GeneralReportAction.do?hmode=drillDown25And26And30GeneralReport&kioskOrManual=K&val=26&wherePart=ZONE_CODE_C=-IR-&lobby=BSL&type=B&startDate=&endDate=&traction=ELEC"
     Do While ieApp.Busy: DoEvents: Loop
     Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

     'get the table based on the table’s id
     Set ieDoc = ieApp.Document
     Set ieTable = ieDoc.all.Item("report-table")

 'copy the tables html to the clipboard and paste to the sheet
 If Not ieTable Is Nothing Then
    oHTML = ""
    For i = 0 To ieTable.Length - 1
        oHTML = oHTML & ieTable.Item(i).outerHTML
    Next i
    Set clip = New DataObject
    clip.SetText "<html>" & oHTML & "</html>"
    clip.PutInClipboard
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
 End If

    'now that we’re in, go to the page we want
     ieApp.Navigate "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/GeneralReportAction.do?hmode=drillDown25And26And30GeneralReport&kioskOrManual=K&val=26&wherePart=ZONE_CODE_C=-IR-&lobby=AQ&type=B&startDate=&endDate=&traction=ELEC"
     Do While ieApp.Busy: DoEvents: Loop
     Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    'get the table based on the table’s id
     Set ieDoc = ieApp.Document
     Set ieTable = ieDoc.all.Item("report-table")

 'copy the tables html to the clipboard and paste to the sheet
 If Not ieTable Is Nothing Then
    oHTML = ""
    For i = 0 To ieTable.Length - 1
        oHTML = oHTML & ieTable.Item(i).outerHTML
    Next i
    Set clip = New DataObject
    clip.SetText "<html>" & oHTML & "</html>"
    clip.PutInClipboard
    Sheet2.Select
    Sheet2.Range("A1").Select
    Sheet2.PasteSpecial "Unicode Text"
 End If

    'now that we’re in, go to the page we want
     ieApp.Navigate "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/GeneralReportAction.do?hmode=drillDown25And26And30GeneralReport&kioskOrManual=K&val=26&wherePart=ZONE_CODE_C=-IR-&lobby=KYN&type=B&startDate=&endDate=&traction=ELEC"
     Do While ieApp.Busy: DoEvents: Loop
     Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

    'get the table based on the table’s id
     Set ieDoc = ieApp.Document
     Set ieTable = ieDoc.all.Item("report-table")

 'copy the tables html to the clipboard and paste to the sheet
 If Not ieTable Is Nothing Then
    oHTML = ""
    For i = 0 To ieTable.Length - 1
        oHTML = oHTML & ieTable.Item(i).outerHTML
    Next i
    Set clip = New DataObject
    clip.SetText "<html>" & oHTML & "</html>"
    clip.PutInClipboard
    Sheet3.Select
    Sheet3.Range("A1").Select
    Sheet3.PasteSpecial "Unicode Text"
 End If


     'close 'er up
     ieApp.Quit
     Set ieApp = Nothing


 'combine
 Dim J As Integer
On Error Resume Next
Sheets(1).Select
 Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
 Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
 Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
 Next

 End Sub

Is it possible to modify the VBA code so that data from each webpage will be imported in same sheet without using VBA for combinethe sheet after importing?

ServerS ServerS · Accepted Answer · 2014-08-21T12:36:58

Yes. In your first if statement, you have Sheet1. In your second, you have Sheet2. If you change the second and beyond to Sheet1, then you must change the range so that it does not overwrite the first data in the sheet. It might look something like this:

Sheet1.Select
Sheet1.Range("A1").Select
Sheet1.PasteSpecial "Unicode Text"

The second one might look like this:

Sheet1.Select
Sheet1.Range("A200").Select
Sheet1.PasteSpecial "Unicode Text"

EDIT:

Try this in the first if statement:

Sheet1.Select
Sheet1.Range("A1").Select
Sheet1.PasteSpecial "Unicode Text"
Dim length As Integer
length = selection.rows.count

In the second and further if statements, try this:

Sheet1.Select
Sheet1.Range("A" & length + 1).Select
Sheet1.PasteSpecial "Unicode Text"
length = length + selection.rows.count

Import data from different webpages in one in MS Excel sheet using VBA

1 Answers