Thread: Visit Site Address Macro

I need some tips. I want a macro that


1. Visit a internet site whose address is the value of a specified cell.

2. (When this adress is visited, the site automatically changes the adress.)

3. Then copy the new address from the site a paste it on a specified cell.


=============================

The macro should apply only to the cell range F2:F4002 (already given adress) and paste new adress in cell range B2:F4002.



Any ideas on how to do this??

I have included the excel worksheet im working with.

I am trying this code but it appears something is wrong, something is not working, it is colored red:

Sub internet()
a=shell("c:\archivos de programa\iexplore")
appactivate a
sendkeys "{F4}", true
sendkeys "{"http://www.exceluciones.com/portal"}", true
sendkeys "{ENTER}", true
end sub

Hmmm... I found this other code for running url with macro:...

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub test()
ShellExecute 0&, vbNullString, "http://www.microsoft.com", vbNullString, vbNullString, vbNormalFocus
End Sub

The latest code works:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub test()
ShellExecute 0&, vbNullString, "http://www.microsoft.com", vbNullString, vbNullString, vbNormalFocus
End Sub

However I would like this information: "http://www.microsoft.com" be substituted by placing a the value of a specified cell.

I have investigated this far:


Macros: Getting Cell Information

Invariably, macros written for use within the Calc application will need to access the contents of the cells on a spreadsheet. This tip is an introduction to the various available methods.

The three methods we will look at are getCellByPosition, getCellRangeByPosition and getCellRangeByName

The function that is first encountered for most people is getCellByPosition. In the sample below, we access cell A1 (on Sheet1)

Sub getCellInfo

   'get the first sheet of the spreadsheet doc 

   xSheet = ThisComponent.Sheets(0)

 

   'Get value of Cell A1 

   A1_value = xSheet.getCellByPosition(0,0).value 

  

   print A1_value  

 

End Sub

The second example shows the use of getCellRangeByName and may be easier to use - because the cells are referenced by the traditional column/row identifiers that are displayed along each axis. However, for applications requiring looping through an array of cells, getCellByPosition is easier to use.

Sub getCellInfo

   'get the first sheet of the spreadsheet doc 

   xSheet = ThisComponent.Sheets(0) 

   

   'Get value of Cell A3

   A3_value = xSheet.getCellRangeByName("A3").value 

  

   print A3_value



End Sub

The next example shows how getCellInfo grabs an array of cells - myTable. A subsequent call to getCellByPosition for the myTable object is relative to the origin of this array.

Sub getCellInfoByRange

   Dim myTable as Object

 

   'get the first sheet of the spreadsheet doc 

   xSheet = ThisComponent.Sheets(0) 

 

   'Grab array A3:A5

   myTable = xSheet.getCellRangeByName("A3:A5") 

   A5_value = myTable.getCellByPosition(0,2).value

   print A5_value

 

End Sub

The final method that needs discussion is getCellRangeByPosition and the example below illustrates it's use. It is equivalent in functionality to the previous example.

Sub getCellInfoByRange

   Dim myTable as Object

 

   'get the first sheet of the spreadsheet doc 

   xSheet = ThisComponent.Sheets(0) 



   'Grab array A3:A5

   myTable = xSheet.getCellRangeByPosition(0,2,0,4) 

   A5_value = myTable.getCellByPosition(0,2).value

   print A5_value



End Sub

I just don't know how to substitute it or add the macro inside the other macro. Any advice?

I found this other code, it should help me dealing with my problem...

Sub Macro1()
Msgbox("This is Macro1")
Call Macro2    'This calls for Macro2 to run
End Sub

It is supposedly to run another macro from within a macro you need to use the Call statement.

See if you can adapt this. The .Option(1) is the address returned by the site.

Function IsValidURL(sURL As String) As Variant
    ' shg 2009
    ' Requires a reference to Microsoft WinHTTP Services

    ' http://msdn.microsoft.com/en-us/library/aa384081(v=VS.85).aspx
    ' http://msdn.microsoft.com/en-us/library/aa384106(v=VS.85).aspx
    ' http://msdn.microsoft.com/en-us/library/aa384072(v=VS.85).aspx
    ' http://msdn.microsoft.com/en-us/library/aa383887(v=VS.85).aspx (Status codes)

    On Error GoTo Oops
    With New WinHttpRequest
        .Open "GET", sURL
        .Send

        Select Case .Status
            Case 200: IsValidURL = IIf(InStr(1, .Option(1), sURL, vbTextCompare) = 1, _
                                      "OK", False)
            Case 403: IsValidURL = "Forbidden"
            Case 404: IsValidURL = "Not Found"
            Case 410: IsValidURL = "Gone"
            Case 503: IsValidURL = "Service Unavailable"
            Case Else: IsValidURL = False
        End Select
        Exit Function
    End With

Oops:
    IsValidURL = False
End Function

You might also look at the links.

Drus,

This is what I came up with. Hope it helps.
You must make sure you have "Microsoft HTML Object Lib" selected in the "Tools, Ref"
for this code to work.

Sub UrlCopyTest()
Dim IE As Object
Dim i As Long, lrow As Long
Dim Myurl As String
lrow = Sheets("Registro").Range("B4002").End(xlUp).Row
Set IE = CreateObject("InternetExplorer.Application")
For i = 2 To lrow
    If Sheets("Registro").Cells(i, 2).Text <> "" Then
    
        Myurl = Sheets("Registro").Cells(i, 6).Text
        IE.Navigate Myurl
        '' use send key to capture the url address ''
        'wait for page to load
        Do Until IE.ReadyState = 4: DoEvents: Loop
        Application.SendKeys "{Tab}", True
        Application.SendKeys "^C", True
        Sheets("Registro").Cells(i, 3).PasteSpecial Paste:=xlPasteAll
        IE.Quit
    End If
Next i
    
End Sub

Thank you Charles!

A quick question: Where do I save this code? Inside "module1" or "workbook" or "sheet1" etc? Which is it? And if I have other macros save in my workbook how do I keep em' separate?

Sincerely,
Andrew

Hi,

In Module1