Get double pages when trying to Copy Web page to a worksheet

[rfaris]

Please help! I have a workbook that has a sheet with a list names and an associated hyperlink. I am wanting to design a macro to select each name, open the web page, select and copy the page and paste it into a new sheet. Here is the code that I have so far. It works for 1 or 2 names but then fails because it pulls up the same page twice.
Any help would be appreciated!

Please Login or Register  to view this content.

[rfaris]

Sorry about breaking the rules. I will be more carful in the future. Do I need to start another thread or does the current one look OK. Thanks

[Leith Ross]

Hello rfaris,

Try out this version of your macro.

Please Login or Register  to view this content.

[rfaris]

Hello Leith Ross,
Thanks for responding. I tried your code but I only get fragmented text from the web page. I was hoping for the whole page in HTML format.
Rfaris

[Leith Ross]

Hello rfaris,

Sorry, I misunderstood what you wanted. Do you want to copy the web page HTML code to a worksheet then?

[Leith Ross]

Hello rfaris,

Sorry, I misunderstood what you wanted. Do you want to copy the web page HTML code to a worksheet then?

[rfaris]

Hello Keith Ross,
Yes, I am wanting the sheet to display the web exactly like it appears. I apologize for not being more clear with my request.
Thanks,
rfaris

[Leith Ross]

Hello Rfaris,

Do you have a sample of what the output should look like and/or a site I could visit?

[rfaris]

Hello Leith Ross,
Attached is a sample of what I am trying to achieve. Shown are the last four names on the list. I hope this helps.
Thank You,
rfaris

[Leith Ross]

Hello Hello Rfaris,

Thanks for posting that. Again, my mental view of the page layout was different. Now I can create a macro to do want you expect.

[Leith Ross]

Hello Hello Rfaris,

Sorry for the delay. I wanted to be sure the macro would fit with your needs. This is what I have done. The macro now takes a single argument when called. The argument is a cell with a link to the player. The macro opens Internet Explorer, minimizes it, selects all the data, copies it to clipboard, and pastes it to the new sheet. The new sheet name is taken from the cell. Only numbers and periods are used in the name. The macro checks if the sheet exists. If it does then you are asked if you want to replace it. when macro finishes, it closes Internet Explorer.

Here is working code. This should be copied and pasted into a separate VBA module in the workbook. Be sure you call the macro from Excel instead of the VB Editor becuase you may receive a prompt about the worksheet. If you are in the VB Editor then the prompt will display and the macro will appear to be hung.

Please Login or Register  to view this content.

Test Macro

Please Login or Register  to view this content.

[rfaris]

Hello Leith Ross,
Wow!! what a well designed Macro. This is better than I was looking for. Thanks very much

Sincerly,
rfaris

[Leith Ross]

Hello rfaris ,

You're welcome.