none
Get data from web to create database in excel RRS feed

  • Question

  • Dear All

    How can I download to excel and refresh automaticly some data from a web

    (SEE LINKS) to receive database like this:

    BRAND:   MODEL:   STORE:   PRICE:  LINKS   (NOT IN DATA BASE)
    Panasonic  Panasonic TX-42AS600E ZADOWOLENIE.pl 1919,00 http://www.ceneo.pl/28867491 
    Panasonic  Panasonic TX-42AS600E MALL.pl  1919,00
    ... ... ... ...
    THAN   NEXT PRODUCT:
    Panasonic  Panasonic TX 40AS640E MALL.pl 2399,00 http://www.ceneo.pl/30199325 
    Panasonic  Panasonic TX 40AS640E MIX MEDIA 2116,99
    ... ... ... ...

    Any suggestions?

    G.




    Tuesday, August 19, 2014 3:29 PM

Answers

  • Hi,

    >>I am wondering is a way to copy all this queries in to one excel workbook and refresh all of them together? Becouse every new created querry opens in new workbook and there is no possibility to copy a sheet from one book to another. <<

    In fact, there is no Copy/Move method of QueryTable Object to copy the queries from one workbook to another directly in Excel.

    But we can copy a sheet from one workbook to another manually or programmatically. To do that manually, you could open a new blank workbook as the target workbook, and then select the target workbook after right clicking "Move or Copy" in the Sheet tab and check the "Create a copy" as the picture below. Then the worksheet can be copy to the target workbook successfully.

    If you want to copy the sheets automatically, you could record a macro about copying a worksheet to another and put all the workbooks into a folder. Then write a code to loop to open every workbook in the folder one by one and copy the worksheet which contains Web Query to the target workbook. Please note that the target workbook should be opened.

    After putting all the Web Queries into one workbook, you could write a code to refresh all the queries automatically. In this sample, it loops through all the queries in all worksheets of the active workbook and then refresh them one by one with QueryTable.Refresh Method.

    Sub RefreshQueryTables()
    Dim qt As QueryTable
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
        For Each qt In ws.QueryTables
            Debug.Print qt.Name
            qt.Refresh
        Next
    Next
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 25, 2014 7:49 AM
    Moderator

All replies

  • Hi,

    Excel is a tool to help us to store and analyze some data, but it cannot be used to create database. If you want to create a database, I suggest you using Access.

    In addition, with Excel, you can create or run a Web query to retrieve text or data from a Web page. Depending on your needs, you can retrieve data that is refreshable. More details please refer to Get external data from a Web page. You could set the property as followed to make the Web Query refresh automatically.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 20, 2014 6:52 AM
    Moderator
  • HI

    Thanks for suggestions. What I done is to follow the instruction from the link:

    "Right-click the Web page, and then click Export to Microsoft Excel on the shortcut menu. This displays the New Web Query dialog box."

    After the refreshable web query was created I made some vlookup formulas to get the data from the query to my database looking like the one above. And it was working :)

    Then I created other querries to get my database filled up. And then I found a problem.

    I am wondering is a way to copy all this queries in to one excel workbook and refresh all of them together? Becouse every new created querry opens in new workbook and there is no possibility to copy a sheet from one book to another. When I clik "Move or copy.." I cannot chose a file where other querry is place. Why? Any work around?

    Is there a macro or option to refresh all web querries in a given workbook?

    Wednesday, August 20, 2014 12:00 PM
  • There are several ways to do this.  Maybe you want a Worksheet Event to fire the code?

    http://dmcritchie.mvps.org/excel/event.htm

    You want to right-click the Sheet and click 'View Code' and paste your code into the Windows that opens.


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


    • Edited by ryguy72 Wednesday, August 20, 2014 9:15 PM
    Wednesday, August 20, 2014 9:15 PM
  • Thanks ryguy72

    But I couldn't find what I want.

    Still do not know how to place all web queries in one workbook. Why there is no easy way to copy a query from one workbook to another? Why there is no option to chose existing workbook when creating new query?

    I am using the procedure:

    "Right-click the Web page, and then click Export to Microsoft Excel on the shortcut menu. This displays the New Web Query dialog box."

    After this all queries open i separate workbooks. That is not I want.

    Let's leave the refreshable all queries macro at this moment. I will try to record at the end.

    thanks for any help

    Thursday, August 21, 2014 8:16 AM
  • How about this, record a macro to do what you want, and share it here.  When you post back, describe EXACTLY what you want to accomplish. For instance, you wrote 'refresh automatically'.  Based on what criteria?  What logic?

    We'll take it from there.


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

    Thursday, August 21, 2014 2:22 PM
  • Hi,

    >>I am wondering is a way to copy all this queries in to one excel workbook and refresh all of them together? Becouse every new created querry opens in new workbook and there is no possibility to copy a sheet from one book to another. <<

    In fact, there is no Copy/Move method of QueryTable Object to copy the queries from one workbook to another directly in Excel.

    But we can copy a sheet from one workbook to another manually or programmatically. To do that manually, you could open a new blank workbook as the target workbook, and then select the target workbook after right clicking "Move or Copy" in the Sheet tab and check the "Create a copy" as the picture below. Then the worksheet can be copy to the target workbook successfully.

    If you want to copy the sheets automatically, you could record a macro about copying a worksheet to another and put all the workbooks into a folder. Then write a code to loop to open every workbook in the folder one by one and copy the worksheet which contains Web Query to the target workbook. Please note that the target workbook should be opened.

    After putting all the Web Queries into one workbook, you could write a code to refresh all the queries automatically. In this sample, it loops through all the queries in all worksheets of the active workbook and then refresh them one by one with QueryTable.Refresh Method.

    Sub RefreshQueryTables()
    Dim qt As QueryTable
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
        For Each qt In ws.QueryTables
            Debug.Print qt.Name
            qt.Refresh
        Next
    Next
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 25, 2014 7:49 AM
    Moderator
  • Thanks Luna

    The way you descriped of copy sheets with web queries not working becouse you can copy querry in this way only to new file. (example book 1, book 2) If I have open my "target file". I can not see this in a Move or Copy window.

    Strange but true.

    Anyway I have found a workaround To place queries in one workbook:

    1. Open a webpage you want to get data from in Internet Explorer

    2. Select all or part of the page

    3. Copy selection with CTRL C

    4. Open a target workbook and click wheather you want paste with formatting / without formatting

    5. When the data pasted on a bottom right corner of selction you will see:

    6. Choose refreshable Web Query

    7. Done (You can repeat the action to other queries and paste them in a target work book)

    Anyway the macro goes well so I mark as an answer.

    Thanks a lot

    Monday, August 25, 2014 8:33 AM
  • Do you want to list web pages in an array, loop through each one, and import that data for that page into a new sheet?

    'Import Everything From a Web Page:
    
    Sub Test()
        Dim IE As Object
         
        Set IE = CreateObject("InternetExplorer.Application")
        With IE
            .Visible = True
            .Navigate "http://www.aarp.org/" ' should work for any URL
            Do Until .ReadyState = 4: DoEvents: Loop
                 
                x = .document.body.innertext
                x = Replace(x, Chr(10), Chr(13))
                x = Split(x, Chr(13))
                Range("A1").Resize(UBound(x)) = Application.Transpose(x)
                 
                .Quit
            End With
             
    End Sub
    



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

    Monday, August 25, 2014 1:45 PM