Results 1 to 10 of 10

Web Query Macro

This is a discussion on Web Query Macro within the Excel Questions forums, part of the Question Forums category; Hello.. I'm using this macro in order to bring some data to two cells.. it's currently working great... Sheets.Add With ...

  1. #1
    New Member
    Join Date
    Mar 2007
    Posts
    44

    Default Web Query Macro

    Hello..

    I'm using this macro in order to bring some data to two cells.. it's currently working great...

    Sheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M" _
    , Destination:=Range("A1"))

    .Name = "fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = "6"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    Sheets("GUS8301").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(""Start run."",Sheet1!C[1],1,FALSE)=""Start run."",OFFSET(Sheet1!R[-1]C[1],MATCH(""Start Run."",Sheet1!C[1],0)-1,-1,1,1),FALSE)"
    Range("A2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete

    However, as you can see from the text in bold i'm currently telling the url instead of bringing it from a cell with an url. I want the macro to be able to select the url from a cell.

    I'm trying to use this solution, but i keep getting a Run-time erro 5:
    Invalid procedure call or argument.

    connstring = Range("GUS8301!B3").Value
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
    Destination:=Range("A1"))


    I'm a begginer at Macros with code, does anybody has an idea for a solution here??


    Thanks in advance!!!

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,203

    Default

    Do you actually have a sheet called GUS8301?

    And if you do does cell B3 on it contain a valid url/value/whatever?
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Mar 2007
    Posts
    44

    Default GUS8301

    Yes,

    My Sheet is called GUS8301.. and the cell does contain a valid url.

    Thanks

  4. #4
    New Member
    Join Date
    Mar 2007
    Posts
    44

    Default

    Any help??

  5. #5
    New Member
    Join Date
    Mar 2007
    Posts
    44

    Default

    Could somebody please help me??

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,203

    Default

    rodrigo

    What exactly is in the cell?
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    Mar 2007
    Posts
    44

    Default

    The cell has a hyperlink, so instead of typing it myself in the macro, i want it to select it from the cell and enter the link.

    With that link i extract a web query which i need for a cell holding a date.

    Hope this helps!!

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,203

    Default

    rodrigo

    If all you have in the cell is a hyperlink I've got a feeling it won't be any use in code.

    How exactly did you create the hyperlink?
    If posting code please use code tags.

  9. #9
    New Member
    Join Date
    Mar 2007
    Posts
    44

    Default

    The hyperlink comes from a link in a webpage that I'm getting using a web query with full html formatting.

  10. #10
    New Member
    Join Date
    Mar 2007
    Posts
    44

    Default

    Or is there a way to concatenate the web address inside the Web query...?

    I need to acces a webpage that only changes the last 10 digits... those digits can be found in a cell that i download from a web query...

    Any help here would be greatly appreciated.. this is the only step that is keeping me from finishing my macro...

    Thanks in advance!!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com