BoatwrenchV8
02-05-2012, 12:16 PM
Hello All,
I am trying to find a way to use VBA in Word 2010, with minimal user effort, to:
1. Open a web page at a website location
(Example: www.somesite.com/A.htm (http://www.somesite.com/A.htm))
2. Save the web page to a new file name and as text format.
(Example: SomeSitePage[DateAccessed][SerialNumber].txt)
3. Perform operations 1 and 2 again, looping thru a pre defined list of websites.
(Example: www.somesite.com/A.htm (http://www.somesite.com/A.htm), www.somesite.com/B.htm (http://www.somesite.com/B.htm), www.somesite.com/C.htm (http://www.somesite.com/C.htm), … www.somesite.com/Z.htm (http://www.somesite.com/Z.htm))
4. Open each file, extract the data required and paste it into one single file. The new single file would contain all the extracted data from all of the website text files.
(I have this part figured out already.)
5. The extracted data would be formatted into a 2 column by however long needed table.
(I have this part figured out already.)
Item number 2 is where I am completely stuck.
Code I have found and have been playing in the hopes of opening a page and saving it, without success is:
(post number 8, from: http://www.mrexcel.com/forum/showthread.php?t=67123)
Sub TESTING()
'Need to reference to Microsoft Internet Controls
Dim URL As String
URL = "http://www.mrexcel.com/board2/viewtopic.php?t=69685 (http://www.mrexcel.com/board2/viewtopic.php?t=69685)" 'for TEST
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Visible = True
IE.Navigate URL
Do While IE.ReadyState <> 4
DoEvents
Loop
SendKeys "%S"
IE.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT 'SaveAs
End Sub
The idea I had was to use code like this to open the file and select the save command in Internet Explorer (Ver 9) and save the file.
When I ran the code:
1. Internet explorer does open the site
2. The Save As dialog box does open
BUT the problem is:
1. The SendKeys command should send Alt+S and save the file but it doesn’t and behaves like the command is either ignored or was not sent.
The next problem would be figuring out how to change the file type and file name in the dialog box but I *think* that would be easy once the SendKeys problem is solved.
Thoughts? Suggestions? HELP?! Is there a better and easier approach to this?
Thank you in advance,
Rich
I am trying to find a way to use VBA in Word 2010, with minimal user effort, to:
1. Open a web page at a website location
(Example: www.somesite.com/A.htm (http://www.somesite.com/A.htm))
2. Save the web page to a new file name and as text format.
(Example: SomeSitePage[DateAccessed][SerialNumber].txt)
3. Perform operations 1 and 2 again, looping thru a pre defined list of websites.
(Example: www.somesite.com/A.htm (http://www.somesite.com/A.htm), www.somesite.com/B.htm (http://www.somesite.com/B.htm), www.somesite.com/C.htm (http://www.somesite.com/C.htm), … www.somesite.com/Z.htm (http://www.somesite.com/Z.htm))
4. Open each file, extract the data required and paste it into one single file. The new single file would contain all the extracted data from all of the website text files.
(I have this part figured out already.)
5. The extracted data would be formatted into a 2 column by however long needed table.
(I have this part figured out already.)
Item number 2 is where I am completely stuck.
Code I have found and have been playing in the hopes of opening a page and saving it, without success is:
(post number 8, from: http://www.mrexcel.com/forum/showthread.php?t=67123)
Sub TESTING()
'Need to reference to Microsoft Internet Controls
Dim URL As String
URL = "http://www.mrexcel.com/board2/viewtopic.php?t=69685 (http://www.mrexcel.com/board2/viewtopic.php?t=69685)" 'for TEST
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Visible = True
IE.Navigate URL
Do While IE.ReadyState <> 4
DoEvents
Loop
SendKeys "%S"
IE.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT 'SaveAs
End Sub
The idea I had was to use code like this to open the file and select the save command in Internet Explorer (Ver 9) and save the file.
When I ran the code:
1. Internet explorer does open the site
2. The Save As dialog box does open
BUT the problem is:
1. The SendKeys command should send Alt+S and save the file but it doesn’t and behaves like the command is either ignored or was not sent.
The next problem would be figuring out how to change the file type and file name in the dialog box but I *think* that would be easy once the SendKeys problem is solved.
Thoughts? Suggestions? HELP?! Is there a better and easier approach to this?
Thank you in advance,
Rich