none
Combine multiple Web Queries into sheet1 RRS feed

  • Question

  • hi every one, this is my first post, and im a newbie in vba

    ive been trying to import some web data from a site, but strugling to format then correctly cause the source is not formated to fit excel (the first 3rows are not correct columns), and also MERGING the data *they get overwriten :(

    [http://lbma.oblive.co.uk/table?metal=gold&year=2009&type= monthly]   -----give Monthly Averages for year 2009
    [http://lbma.oblive.co.uk/table?metal=gold&year=2010&type= monthly]   ----  for each year 2009-2017

    http://lbma.oblive.co.uk/table?metal=gold&year=2011&type=daily   ; ------give Daily Averages for year 2009

    i need this Data in 2diferent sheets, therefore:

    1. SHEET1 =Monthly would list all the years 2009-2017 merged and formated
    2. SHEET2 =Daily would list all the years 2009-2017 merged and formated

    i have tried editing the IQY webquery file adding just a row of the http source , but didnt work . 

    WEB
    1
    http://lbma.oblive.co.uk/table?metal=gold&year=2009&type=monthly
    '& http://lbma.oblive.co.uk/table?metal=gold&year=2010&type=monthly    [added the second source but doesnt work]
    
    Selection=AllTables
    Formatting=None
    PreFormattedTextToColumns=True
    ConsecutiveDelimitersAsOne=True
    SingleBlockTextImport=True
    DisableDateRecognition=True
    DisableRedirections=True

    i tried  EDITING some macros i found in the forum but FAIL FAIL , i cant get to make it

    Sub webqt()
    
     Dim C As Range
     Dim strName As String
     Dim strConnectString
     Dim QT As QueryTable
     Dim aYears As Range
     
      
     'you can enter MSFT,GE,GM... to Range(A1:A5) of sheet1
     Set aYears = Worksheets("sheet1").Range("A1:A5")
      Application.ScreenUpdating = False
    
    'Do a loop to change parameter
     For Each C In aYears
     strName = C.Value
     strConnectString = _
     "URL;http://lbma.oblive.co.uk/table?metal=gold&year=" & C.Value & "&type=monthly"
     ThisWorkbook.Worksheets.Add
     ActiveSheet.Name = strName
    
    ''http://lbma.oblive.co.uk/table?metal=gold&year=2010&type=monthly
    
    
     ' Define a Web Query,you can get the code below by recording a Macro
     Set QT = ActiveSheet.QueryTables.Add _
     (Connection:=strConnectString, Destination:=Range("A1"))
    
     With QT
         .CommandType = 0
            .Name = "Monthly"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
     End With
    
     Next
      Application.ScreenUpdating = True
     End Sub
    
    

    any help would be appreciated. 

    Monday, November 27, 2017 6:31 PM

Answers

  • HI,

    For the specific year, are you able to import into sheet manually, if you are, you could import them into different sheets, and then merge them into the same sheet by code.

    The Import is the built-in function from Excel, you could not achieve your current request with the built-in api.

    Regards,

    Tony


    Help each other

    • Marked as answer by elementi007 Wednesday, December 6, 2017 12:21 PM
    Tuesday, December 5, 2017 11:42 AM

All replies

  • Hi elementi007,

    Thanks for posting in our forum.

    Then here we mainly focus on general issues about Excel user interface. Since your query is more related to using VBA code in Excel, I'll move your thread to the following dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Tuesday, November 28, 2017 2:46 AM
  • Hi elementi007,

    If you manually import data from web page by Data->From Web->Enter URL, will you get expected result?

    I suggest you try code below:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        ActiveWorkbook.Queries.Add Name:="LBMA Gold Prices", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""http://lbma.oblive.co.uk/table?metal=gold&year=2010&type=monthly""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Timestamp: 2014-03-31 10:33:00 +0100"", type date}, {""Timestamp: 2014-03-31 10:33:00 +01002"", type text}, {""USD AM"", type number}, {""USD PM"", type number}, {" & _
            """GBP AM"", type number}, {""GBP PM"", type number}, {""EUR AM"", type number}, {""EUR PM"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""LBMA Gold Prices"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [LBMA Gold Prices]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "LBMA_Gold_Prices"
            .Refresh BackgroundQuery:=False
        End With
        ActiveWindow.SmallScroll Down:=-9
        Range("B6").Select
    End Sub

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 30, 2017 8:47 AM
  • Hi Tao,

    thanks for the reply, 

    i do Import Data thru [Data->From Web->Enter URL] but in need to merge multiple years in same sheet!

    and the formating is not imported right *first 3 rows are shifted to b column

    ps. the VBa Script does a Run-time error 438 ?

    Tuesday, December 5, 2017 10:36 AM
  • HI,

    For the specific year, are you able to import into sheet manually, if you are, you could import them into different sheets, and then merge them into the same sheet by code.

    The Import is the built-in function from Excel, you could not achieve your current request with the built-in api.

    Regards,

    Tony


    Help each other

    • Marked as answer by elementi007 Wednesday, December 6, 2017 12:21 PM
    Tuesday, December 5, 2017 11:42 AM
  • all rgith, i will try.

    thanks

    Wednesday, December 6, 2017 12:21 PM