Problems with URL addressing

[Schnappa]

All

Still continuing with my project, and been trying to work this one for most of the day, with many attempts and frustrations. I though this bit would be easy, but alas - no such luck.

I have the cursor in Cell B1 (blank). Cell A1 is the result of my previous post's formula, which is url address in the form of

http://www.awebsite.com.au/2007/10/31/pt01.html

(example). I am trying to get excel to open this url, screen scrape a portion of the webpage using the excel 'data from web' function, and dump it into cell B1 (I then need to repeaat the process for as many races as I have - but I can get that bit ok).

This works well if the url never changes and is in the same place day after day. I recorded a macro that looks at the process and noticed that excel records the url statically and if you use the same macro day after day, you get the same results, no matter the url in cell A1.

I have also noted that you cannot copy via control c, the cell itself and paste it into the webbrowser used for data from web. You have to highlight the entire address and paste it - compunding the problems.

I have tried to work around this with no real success (see below). My question is - am I making life hard for myself or is this just an impossibility?

Thanks again for everyones help thus far. Here is my code - butchered macro recording. This attempt is trying to copy the contents of cell a1 into a variable to be used in the Connection:= section i.e replace "URL; http:/xxxxxxx". I keep getting errors

Cheers

GV

Sub Macro11()
'
' Macro11 Macro
'

Dim URLVal As String
ActiveCell.Offset(0, -1).Select
URLVal = ActiveCell.Select
MsgBox URLVal
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;

http://www.awebsite.com.au/2007/10/31/PR01.html",

Destination:=Range( _
"$B$1"))
.Name = "PR01_4"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[ClulessChris]

Just a stab in the dark really but how about:

URLVal = Cstr(ActiveCell.Value)
With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & URLVal , Destination:=Range( _
        "$B$1"))

Everybody is somebodys Nutter.

 

[Schnappa]

Thanks for that CC

Tried that already - the system is not too happy with it I am afraid.

Back to the drawing board it seems

Cheers

GV