I'm trying to open a file on my network drive that is in HTML format, then select all, copy all and paste into my excel workbook. How can I do this in a macro?
The best bet would be - instead of using a macro - to set Excel to constantly or manually poll a data source for info. For instance, in Excel 2007, you'd click on the "Data" tab and choose to get external data "From Text". In the "Import Text File" dialog, navigate to the folder you want, choose "All Files" from the file type drop-down box, and select your HTML document. Walk through the steps of the "Text Import Wizard" and click "Finish". On the "Import Data" dialog, click on "Properties" (you can also do this after the setup is complete) and deselect the "Prompt for file name on refresh" checkbox. If you want, you can also set an automatic poll of the data in the "Refresh every __ minutes" checkbox. Finally, click "OK" and "OK". You're done!
To manually refresh, click the "Refresh All" button up top. Good luck!