web box address

[Atishoo]

Hi how do I return a web browser address into a cell as per the following
example??


Cells(ActiveCell.Row, "ar").Value = ActiveSheet.Shapes("WebBrowser2").Address

 

[Joel]

You have a few problems with the code below

1) A shape doesn't have an address. the location of the shape is determined
by 4 parameters which are points (or pixels) position.

left, top, width, height

2) If you have an Oleobject like a textbox, listbox, checkbox they have both
shape information and object information. The data inside the items will be
the Oleobjects.object while the picture type information will be shapes.

ActiveSheet.Oleobjects("WebBrowser2").object.Text - if item was a textbox

 

[Atishoo]

sorry i should have been more specific!
i meant the web address that the web box is currently viewing.

 

[Joel]

this will work. I'm not sure how to direrctly get the combox from the
command bar because it doesn't have a name. It is item 10 in my workbook but
not sure if that will always be the same item. You can also get all the web
addresses from the combobox if you need them.


Set WebBar = Application.CommandBars("Web").Controls
For Each Item In WebBar
If Item.Type = msoControlComboBox Then
webaddress = Item.Text
End If

Next Item

 

[Atishoo]

Is there no way to just return the web address in a cell?? i know how to set
the web address using navigate2! surely there must be a way to return the
address just as you would an objects value? then again maybe there isnt?

 

[Atishoo]

tried that last code and it seems to return the address of the whole excell
file (c:\folder\epb.xls) rather than the web page address

 

[Joel]

Not sure why you aren't getting the address in the web box. This code works
for me.

Sub text()

End Sub
webaddress = ""
Set WebBar = Application.CommandBars("Web").Controls
For Each Item In WebBar
If Item.Type = msoControlComboBox Then
webaddress = Item.text
Exit For
End If

Next Item
Cells(ActiveCell.Row, "ar").Value = webaddress

End Sub

 

[Atishoo]

thanks! still got same result might it be because im using a form webbrowser
not an active x webbrowser

 

[Joel]

Th eactive x form browser defaults to the workbook if not set to something
else.

 

[Atishoo]

Oh this problem is driving me nuts!
Maybe this might shed some light?
I used the folowing sub to create my browsers for me does this make a
difference to the way i should be retrieving the displayed web page?


Sub addbrowser()
'
' addbrowser Macro
'
'Application.CommandBars("Control Toolbox").Visible = True
Dim anOLEObj As OLEObject, BrowserPresent As Boolean
For Each anOLEObj In ActiveSheet.OLEObjects
BrowserPresent = BrowserPresent Or TypeName(anOLEObj.Object) =
"WebBrowser"
Next anOLEObj
If Not BrowserPresent Then
Set anOLEObj =
ActiveSheet.OLEObjects.Add(ClassType:="Shell.Explorer.2", Link:=False, _
DisplayAsIcon:=False, Left:=461.25, Top:=293.25, Width:=190.5,
Height _
:=95.25)
anOLEObj.Object.Name = "WebBrowser1"
End If
ActiveSheet.Shapes("WebBrowser1").OLEFormat.Object.Object.Navigate2 _
"http://www.dh.gov.uk"

'
End Sub

 

[Joel]

What is this statement doing?

BrowserPresent = BrowserPresent Or TypeName(anOLEObj.Object)
= "WebBrowser"

You can also open an Explorer object by simply doing this

anOLEObj.activate

 

[Atishoo]

Have got the browsers present at the top of my board and working great! they
just display as windows, no address bar or anything, i control the address by
the active row and want to do the opposite (set the address to the active row
via a command button that will deliver the address that the user has
navigated to in the column "ar" in the active row) thereby the user can set
whos info the browser shows automatically, if that makes sense! Only trouble
is that no matter how I twist it i keep getting the workbooks address on the
C drive instead! Might be defeated by this one!

 

[Joel]

Again the statement below isn't a good statement. You have two equal
statements with an OR . what is the Or doing. There is no "IF". Correct
this statement!

BrowserPresent = BrowserPresent Or TypeName(anOLEObj.Object)
= "WebBrowser"

 

[Atishoo]

But that statement is only part of a macro used to create and place the web
box it doesnt have any current role in my workbook, and it is not a working
part of the macro but a direction to the user, that statement aside the web
boxes are in place and working i just cant retrieve the web address

 

[Joel]

I think you are getting the filename as the default browser addrress because
yuo never changed it. some of you code is dependant on the value of
BrowserPresent (there is an IF stement using this value) and I'm not sure
what the value is set to. fix the obvious problems before you look at the
more difficult problems. You never know how bad lines of code are effecting
the rest of your code.

 

[Atishoo]

hm! didnt think of it that way! ill have a crack tonight!
thanks John

 

[Atishoo]

Nope its no blinkin good! kept it simple as possible! just added a browser
manually from the control toolbox to a blank workbook set the address via
navigate 2 on a workbook open event and still I cant get the bloomin
displayed site url to return!
what the heck am i doing wrong ??? i have an idea though! I work on the 4th
floor and its one heck of a drop for a computer!

 

[Atishoo]

Yeah!!
think I may not have explained myself at all properly Have just sussed it
this doe the trick:-

Cells(ActiveCell.Row, "ar").Value = Me.WebBrowser2.LocationURL

thanks John