I need help to retrieve the text in the IE address bar to a excel cell using VBA

[robles74]

Hi everyone, I'm looking over the internet to find some way to get the URL from certain Internet Explorer window (I need to have several IE windows open at the same time).

I see the post Geting URLs from internet explorer and I try to modify just to get the text from the address bar in IE but I can't.

Looks like some easy for everyone, but is not for me.

I have lots of IE windows but I need just the text dropped in a certain cell into a excel worksheet, the address bar looks like "http://xxxxxx.xxxxxx.com/xxxxxx/xxxxr/xxxxxxxx/image.asp?SetID=050525807877" without quotes, and I need just the last 12 digits, the page changes as soon I hit the "Process" button and gave me another image with a different number but the sintax is the same just change the last 12 digits.

I don't know if someone can to help me, but if you can it will be really appreciated.

Thanks in advance and best regards.

[Leith Ross]

Hello robles74,

Bienvenido al foro!

Para ayudar a usted, por favor envíe su código y cargar su libro.

[robles74]

Hi again I forgot to send the archive.

[robles74]

Hi Leith, due a some restrictions I can't send you the "real" file, but I make a test one.

The idea is "simple" (yea sure :-)) I have several internet explorer windows open at the same time, then I need to extract the address from certain page and paste them in a excel cell.

As you can see the task looks easy for everyone but not for me, I'm not a programmer but I build some macros to do more fast and efficient my work but right now I'm "stuck" dealing with this.

The task is to "get", "retrieve" or "copy" the URL from the address bar, as you can see the address is the same every time just changes at the end, I need to extract with a macro the last 12 digits (in the example the last 2 digits), and then copy the result in a specific cell in the excel workbook.

I don't have deep (read practically any) knowledge in VBA and this is the reason of my request. I hope you can review the file and help me.

I'll really appreciate any help from you or any one in the community.

Thanks and best regards.
excelforumimage.JPG

[Leith Ross]

Hello Robles74,

This macro will list the URL for tab open in Internet Explorer starting at cell "A2" on "Sheet2". You can change the sheet and cell to where you want the output.

Sub ListURLs()

    Dim n       As Variant
    Dim oShell  As Object
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        Set Wks = Worksheets("Sheet2")
        
        Set Rng = Wks.Range("A2")
        
        Set oShell = CreateObject("Shell.Application")
            
        For n = 0 To oShell.Windows.Count - 1
            If oShell.Windows(n).Name = "Internet Explorer" Then
                Rng.Offset(n, 0).Value = oShell.Windows(n).LocationURL
            End If
        Next n
                    
End Sub

[robles74]

Hello Leith.

I try with the code but nothing happens, by the way I already have the "Microsoft Internet Controls" reference "checked", because how I say previously I read some threads about how to get the URL's and in everyone ask for this reference active, I don't know if this matter right now.

Thank you very much, I really appreciate your help.

[Leith Ross]

Hello Robles74,

Tal vez yo no entendía su ejemplo. ¿Quieres mostrar una página web de una dirección URL?

[robles74]

Hi Leith, I'm unable to send you the web page, but we are freeze to do/apply this idea due a some restriction with the "mainframe" owner. By this reason I can't to continue with this task.

The idea to pull the text from the address bar in the internet explorer window should be cancelled by now.

I really appreciate your help and now I need your help to "close" this thread.

Again thank you very much and best regards.