PDA

View Full Version : Solved: Make NewWebQuery with URL-variable.



omnibuster
04-27-2009, 02:54 PM
Hi.
I was search in Google & different VBA forums but dont get help! :banghead:
I hope somebody help to find anver for this problem.


Option Explicit
Sub Netti()
Dim LastRow As Long, mycheck As String 'Boolean
Dim i As Long, a As Long
Dim y As Variant, rCtr As Long, ie As Object, SWs As SHDocVw.ShellWindows
Dim Adres As String, MyUrl As String 'url As String
Sheets("Sheet1").Select
LastRow = Range("A1").End(xlDown).Row
For i = 1 To LastRow
Range("A" & i).Activate
mycheck = Range("T1").Value
If Range("A" & i).Value = mycheck Then
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Set SWs = New SHDocVw.ShellWindows
For a = SWs.Count - 1 To 0 Step -1
Adres = CStr(SWs(a).LocationURL)
If Adres Like "http*" Then
Range("AK1").Hyperlinks.Add Anchor:=Range("AK1"), Address:=Adres, TextToDisplay:=Adres
Exit For

End If
Next a
Set SWs = Nothing
Exit For
' Now Range("AK1").Value=http://www.XXXXXX.com/yyyyyy/?currency=EUR&pg=details&instrument=AA5334344403&list=2

'How GET NR "AA5334344403" & Put in Range("K3")"AA0000000000"

'Range("AK3").Value = http://aaaaaaaaa.a-failides.aa/shlist/show.php?isin=AA0000000000&lang=et (http://aaaaaaaaa.a-failides.aa/shlist/show.php?isin=AA0000000000&lang=et)

'NewRange("AK3").Value Should be = 'http://aaaaaaaaa.a-failides.aa/shlist/show.php?isin= AA5334344403&lang=et

Set ie = Nothing
End If
Next i
'Make WebQuery
Sheets("Sheet1").Select
MyUrl = Range("AK3").Value
With Worksheets("Sheet2").QueryTables.Add(Connection:="URL;" & MyUrl, Destination:=Worksheets("Sheet2").Range("A1"))
' .Name = "New Query"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.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
End Sub

JONvdHeyden
04-28-2009, 03:15 AM
What's the question? :aw

omnibuster
04-28-2009, 05:34 AM
WebSite1.URL : http://www.XXXXXX.com/yyyyyy/?currency=EUR&pg=details&instrument=AA5334344403&list=2 (http://www.XXXXXX.com/yyyyyy/?currency=EUR&pg=details&instrument=AA5334344403&list=2)

Get "AA5334344403"

WebSite2.URL: http://aaaaaaaaa.a-failides.aa/shlist/show.php?isin=AA0000000000&lang=et (http://aaaaaaaaa.a-failides.aa/shlist/show.php?isin=AA0000000000&lang=et)

Replace "AA000000000" to "AA5334344403"

omnibuster
05-04-2009, 01:51 PM
Website owner put in the website Captha test & manipulate with queristring using VBA impossible.