none
Active Excel worksheet in Visual Basic | extracting & writing data from web RRS feed

  • Question

  • Hi there,

    For a hotel I'm trying to extract some data from booking.com. I'm trying to extract 5 pages (same arrival date, the departure date moves +1 day with every loop). The pages should go to 5 different worksheets called 1, 2, 3, 4, 5.

    The result so far is that

    -worksheet 1 is created

    -data is pulled and written

    My problem

    -data is written in the worksheet that contains the URL's [worksheet (URL15-07) @ ("$B$1)] but not in worksheet 1, that in my opinion should be the one that is active... but is not?

    -than after the second worksheet [2] has been created the script stops running... but that could be because something earlier went wrong... I guess?

    You would really help me out if you could explain what is wrong in the script below! Thank you in advance!

    Sub adds()

    'For i = 1 To 5
    For x = 1 To 5
    Worksheets("URL15-07").Select
    Worksheets("URL15-07").Activate
    mystr = "URL;http://www.booking.com/hotel/nl/atlas.html?group_adults=2&checkin_monthday=1&checkin_year_month=2015-7&checkout_monthday=2&checkout_year_month=2015-7"
    'mystr As String
    mystr = Cells(x, 1)
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x
    Worksheets(x).Select
    Worksheets(x).Activate

     With ActiveSheet.QueryTables.Add(Connection:= _
            mystr _
            , Destination:=Range("$B$1"))
            '.CommandType = 0
            .Name = _
            "atlas.en-gb.html?checkin_monthday=1&checkin_year_month=2015-7&dist=0&group_adults=2&type=total&"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
       
    Next x
    'Next i
     
    End Sub

    Thursday, June 18, 2015 8:09 PM

Answers

  • I'm assuming it should be something like this.  Change the URL, of course.

    Sub ImportWebData()
    
    j = 1
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    
    With Sheets("Source")
    
       RowCount = 2
       Do While .Range("A" & RowCount) <> ""
          CellName = .Range("A" & RowCount)
          url = CellName
    
          'get web page
          IE.Navigate2 url
          Do While IE.readyState <> 4 Or _
             IE.Busy = True
             DoEvents
          Loop
    
            Set DestSh = ActiveWorkbook.Worksheets.Add
            DestSh.Name = j
        
                Sheets(j).Select
                Cells.Select
                Selection.Delete Shift:=xlUp
                Range("A1").Select
                With ActiveSheet.QueryTables.Add(Connection:= _
                    "URL;" & CellName, Destination:=Range("$A$1"))
                    .Name = CellName
                    .FieldNames = True
                    .RowNumbers = False
                    .FillAdjacentFormulas = False
                    .PreserveFormatting = True
                    .RefreshOnFileOpen = False
                    .BackgroundQuery = True
                    .RefreshStyle = xlInsertDeleteCells
                    .SavePassword = False
                    .SaveData = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .WebSelectionType = xlEntirePage
                    .WebFormatting = xlWebFormattingNone
                    .WebPreFormattedTextToColumns = True
                    .WebConsecutiveDelimitersAsOne = True
                    .WebSingleBlockTextImport = False
                    .WebDisableDateRecognition = False
                    .WebDisableRedirections = False
                    .Refresh BackgroundQuery:=False
                End With
                
                j = j + 1
                
        Sheets("Source").Select
        RowCount = RowCount + 1
        
        Loop
        
    End With
    IE.Quit
    
    End Sub
    

    In my sample, I actually have the below links in A2:A6

    http://www.google.com/finance
    http://finance.yahoo.com/
    http://www.bloomberg.com/
    http://www.cnn.com/
    http://www.cnbc.com

    Just put your 5 links in those 5 cells.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Jurrijan-NL Friday, June 19, 2015 9:23 AM
    Friday, June 19, 2015 12:58 AM

All replies

  • Jurrijan-NL,

    Worksheet(1) is the first sheet in the workbook. Is a index.

    Worksheet("1") is the sheet with name "1".

    When you activate worksheet(x), with x = 1, you are selecting the first sheet of the plan, instead the plan named "x"..

    Try to use another sheet name, like "a", "b", "c" or anything else.


    • Proposed as answer by André Santo Thursday, June 18, 2015 8:20 PM
    Thursday, June 18, 2015 8:20 PM
  • Thanks André Santo. I understand your answer and added / inserted an extra string to the code like this:

    'mystr As String
    mystrwsn = "x night"
    'mystrwsn As String
    mystr = Cells(x, 1)
    mystrwsn = Cells(x, 2)
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = mystrwsn
    Worksheets(mystrwsn).Select
    Worksheets(mystrwsn).Activate

     With ActiveSheet.QueryTables.Add(Connection:= _
            mystr _
            , Destination:=Range("$A$1"))

    Result when running :

    The worksheet is created with name "1 night" [which is indeed the value of mystrwsn = Cells(x, 2)]

    -sheet is selected

    -sheet is activated

    However now an error occurs @ the line:

    With ActiveSheet.QueryTables.Add(Connection:= _
            mystr _
            , Destination:=Range("$A$1"))

    which seemed an ok part of the script before (as data was pulled and written to worksheet(1) [index]), but not anymore to the newly created worksheet called [1 night]...mmm

    Any help? Would be very appreciated. Thank you!

    Thursday, June 18, 2015 9:38 PM
  • I'm assuming it should be something like this.  Change the URL, of course.

    Sub ImportWebData()
    
    j = 1
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    
    With Sheets("Source")
    
       RowCount = 2
       Do While .Range("A" & RowCount) <> ""
          CellName = .Range("A" & RowCount)
          url = CellName
    
          'get web page
          IE.Navigate2 url
          Do While IE.readyState <> 4 Or _
             IE.Busy = True
             DoEvents
          Loop
    
            Set DestSh = ActiveWorkbook.Worksheets.Add
            DestSh.Name = j
        
                Sheets(j).Select
                Cells.Select
                Selection.Delete Shift:=xlUp
                Range("A1").Select
                With ActiveSheet.QueryTables.Add(Connection:= _
                    "URL;" & CellName, Destination:=Range("$A$1"))
                    .Name = CellName
                    .FieldNames = True
                    .RowNumbers = False
                    .FillAdjacentFormulas = False
                    .PreserveFormatting = True
                    .RefreshOnFileOpen = False
                    .BackgroundQuery = True
                    .RefreshStyle = xlInsertDeleteCells
                    .SavePassword = False
                    .SaveData = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .WebSelectionType = xlEntirePage
                    .WebFormatting = xlWebFormattingNone
                    .WebPreFormattedTextToColumns = True
                    .WebConsecutiveDelimitersAsOne = True
                    .WebSingleBlockTextImport = False
                    .WebDisableDateRecognition = False
                    .WebDisableRedirections = False
                    .Refresh BackgroundQuery:=False
                End With
                
                j = j + 1
                
        Sheets("Source").Select
        RowCount = RowCount + 1
        
        Loop
        
    End With
    IE.Quit
    
    End Sub
    

    In my sample, I actually have the below links in A2:A6

    http://www.google.com/finance
    http://finance.yahoo.com/
    http://www.bloomberg.com/
    http://www.cnn.com/
    http://www.cnbc.com

    Just put your 5 links in those 5 cells.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Jurrijan-NL Friday, June 19, 2015 9:23 AM
    Friday, June 19, 2015 12:58 AM