Visit Site Address Macro

[Drus]

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??

[Drus]

*Sigh*

I hope this is an easy code to create, cause I am total noob at this.

[Drus]

I have included the excel worksheet im working with.

[Drus]

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

Please Login or Register  to view this content.

[Drus]

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

Please Login or Register  to view this content.

[Drus]

The latest code works:

Please Login or Register  to view this content.

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)

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

[Drus]

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

Please Login or Register  to view this content.

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

[Drus]

I have tried to replace "http://www.microsoft.com" with the code above, however it appears a message saying "Error, expresion expected".

[Drus]

No succes. I really need help

[shg]

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

Please Login or Register  to view this content.

You might also look at the links.

[Drus]

Sweet!

Thank you so much. I'll get to work with this code immeadiatly.

[Charles]

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.

Please Login or Register  to view this content.

[Drus]

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

[Charles]

Hi,

In Module1

[Drus]

Hey uhmm

My excel worksheet is 2007 in spanish, therefore starting from left counting to the right, which number is the "Tools" tab?

[Charles]

If you want I can post the workbook.
Do not know if info is private let me know and I'll post it.

[Drus]

I found what you meant by Tools -> Reference -> Microsoft Html Lib..


Code seems ok, but it is not working. I have in module1 some other code. Maybe that's what is making all the trouble.


EDIT:

I uploaded the new workbook so you can see it. Columns are all the same.

[Charles]

Hi,

The attached is what I came up with. It, however does not contain the Function code supplied on this forum. I modified my original code.

[Drus]

I get an error display #1004.

Error in the method of paste special of the class range. (I translated this from spanish to english.)

[Charles]

Hi,

Where does the debug show for the error?
Which workbook did you test the code with? The one I supplied or the one you posted on the forum?

Sorry you did say rror was for the Paste.
Still which workbook did you use

[snb]

For a working example look here.

[Drus]

Dear Charles, and everyone else viewing this thread,

I have finally integrated the code that you sent me with that worksheet. It is working perfectly!!! This thread is solved.

However I am missing to add the button you had in the previous worksheet you sent me.


This is my final post. I have attached the final worksheet (it just missing the "Url" button.


Many thanks.

[Charles]

Thanks for letting us know that your project is done, you can add the button and attach it to the code.

[Drus]

Sorry I don't know how to create a button in excel.

I would appreciate you explained it to me, rather than sending me the worksheet with it. So I can learn for future projects.

[Charles]

Hi,

If you are using 2007 click the "Developer Tab"
Click "Design Mode"
Click "Insert"
You will see a drop down
You will also see in the header for the dropdown "Form Control"
Click on the "Button" which is the top left button in the Form Control
Now on you worksheet you can click any place that you want and a button will appear and when it does it will show a display box for you to assign the button too. Select the Macro and then click Ok. You should now be able to run the macro from that button.
Also deselect The Design Mode, you really do not need to select it.

[Drus]

I see. I will try to do as you say.

I found 2 things:

The macro actually works,

I believe I may have missplaced in the macro code the location of the output cell which it is only copying "http://services.runescape.com/m=avatar-rs//chat.gif " from a cell, therefore the url returned is not the correct one.

Maybe you could look at it.

Also I have an observation, the loop of the macro will make it run until it finds a cell with no "display name" content?

[Drus]

All issues have been solved. Thanks to Charles and everyone else who helped me.

One final attachment 100% functional at your disposal. Thread will be marked as solved.