Web Query Macro

[rodrigo_solares]

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!!!

 

[Norie]

Do you actually have a sheet called GUS8301?

And if you do does cell B3 on it contain a valid url/value/whatever?

 

[rodrigo_solares]

GUS8301

Yes,

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

Thanks

 

[rodrigo_solares]

Any help??

 

[rodrigo_solares]

Could somebody please help me??

 

[Norie]

rodrigo

What exactly is in the cell?

 

[rodrigo_solares]

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!!

 

[Norie]

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?

 

[rodrigo_solares]

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

 

[rodrigo_solares]

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!!!!