We help IT Professionals succeed at work.

saveas wbesite html page into our PC at  VBA excel

duncanb7
duncanb7 used Ask the Experts™
on
Dear Expert,

I would like to save the wbesite as html file and save it in my PC .How Can I can do
that after the following code sub, "savewebsite". That is same operation when we save the website in IE window to our local PC dir and file  but now it is saving it at
VBA excel

THe question-1, how to save the file by saving  myie.document.innerhtml to example.html file when navigate is successfully  ?
The question-2 how to use myIe.execWB  OLECMDID_SAVEAS  to save the website
into html file on PC
The question-3, What or Why is different using myIE.doucment.innerhtml and execWB to save the website into file ?

Please advise
Duncan


Sub savewebsite()
Dim myIE As SHDocVw.InternetExplorer  
Set myIE = CreateObject("InternetExplorer.Application")
with myIE
.Visible = True
 .navigate "www.yahoo.com"   
Do Until Not .Busy And .readyState = READYSTATE_COMPLETE Loop
    save code here
End with    
End sub
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
How to use, the execWb command in VBA ?  Please also advise it ,

Thanks
Ducan

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
OLECMDID_OPEN = 1
OLECMDID_NEW = 2
OLECMDID_SAVE = 3
OLECMDID_SAVEAS = 4
OLECMDID_SAVECOPYAS = 5
OLECMDID_PRINT = 6
OLECMDID_PRINTPREVIEW = 7
OLECMDID_PAGESETUP = 8
OLECMDID_SPELL = 9
OLECMDID_PROPERTIES = 10
OLECMDID_CUT = 11
OLECMDID_COPY = 12
OLECMDID_PASTE = 13
OLECMDID_PASTESPECIAL = 14
OLECMDID_UNDO = 15
OLECMDID_REDO = 16
OLECMDID_SELECTALL = 17
OLECMDID_CLEARSELECTION = 18
OLECMDID_ZOOM = 19
OLECMDID_GETZOOMRANGE = 20
OLECMDID_UPDATECOMMANDS = 21
OLECMDID_REFRESH = 22
OLECMDID_STOP = 23
OLECMDID_HIDETOOLBARS = 24
OLECMDID_SETPROGRESSMAX = 25
OLECMDID_SETPROGRESSPOS = 26
OLECMDID_SETPROGRESSTEXT = 27
OLECMDID_SETTITLE = 28
OLECMDID_SETDOWNLOADSTATE = 29
OLECMDID_STOPDOWNLOAD = 30
OLECMDID_ONTOOLBARACTIVATED = 31
OLECMDID_FIND = 32
OLECMDID_DELETE = 33
OLECMDID_HTTPEQUIV = 34
OLECMDID_HTTPEQUIV_DONE = 35
OLECMDID_ENABLE_INTERACTION = 36
OLECMDID_ONUNLOAD = 37
OLECMDID_PROPERTYBAG2 = 38
OLECMDID_PREREFRESH = 39
OLECMDID_SHOWSCRIPTERROR = 40
OLECMDID_SHOWMESSAGE = 41
OLECMDID_SHOWFIND = 42
OLECMDID_SHOWPAGESETUP = 43
OLECMDID_SHOWPRINT = 44
OLECMDID_CLOSE = 45
OLECMDID_ALLOWUILESSSAVEAS = 46
OLECMDID_DONTDOWNLOADCSS = 47
OLECMDID_UPDATEPAGESTATUS = 48
OLECMDID_PRINT2 = 49
OLECMDID_PRINTPREVIEW2 = 50
OLECMDID_SETPRINTTEMPLATE = 51
OLECMDID_GETPRINTTEMPLATE = 52
OLECMDID_PAGEACTIONBLOCKED = 55
OLECMDID_PAGEACTIONUIQUERY = 56
OLECMDID_FOCUSVIEWCONTROLS = 57
OLECMDID_FOCUSVIEWCONTROLSQUERY = 58
OLECMDID_SHOWPAGEACTIONMENU = 59
OLECMDID_ADDTRAVELENTRY = 60
OLECMDID_UPDATETRAVELENTRY = 61
OLECMDID_UPDATEBACKFORWARDSTATE = 62
OLECMDID_OPTICAL_ZOOM = 63
OLECMDID_OPTICAL_GETZOOMRANGE = 64
OLECMDID_WINDOWSTATECHANGED = 65
OLECMDID_ACTIVEXINSTALLSCOPE = 66



OLECMDEXECOPT_DODEFAULT = 0
OLECMDEXECOPT_PROMPTUSER = 1
OLECMDEXECOPT_DONTPROMPTUSER = 2
OLECMDEXECOPT_SHOWHELP = 3


Top Expert 2010
Commented:
You can request the website source directly using InternetReadFile() example provided in code section.
Example usage:
ViewSource "http://www.google.com/", "c:\source.txt" ' or .html

Option Explicit
 
Private Const WININET_API_FLAG_SYNC = &H4&
Private Const INVALID_HANDLE_VALUE = (-1)
Private Const CREATE_ALWAYS = &H2&
Private Const GENERIC_WRITE = &H40000000

Private Declare Function CreateFileW Lib "kernel32" (ByVal lpFileName As Long, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Declare Function WriteFile Lib "kernel32" (ByVal hFile As Long, ByVal lpBuffer As Long, ByVal nNumberOfBytesToWrite As Long, lpNumberOfBytesWritten As Long, ByVal lpOverlapped As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function InternetOpenW Lib "wininet" (ByVal lpszAgent As Long, ByVal dwAccessType As Long, ByVal lpszProxyName As Long, ByVal lpszProxyBypass As Long, ByVal dwFlags As Long) As Long
Private Declare Function InternetOpenUrlW Lib "wininet" (ByVal hInternet As Long, ByVal lpszUrl As Long, ByVal lpszHeaders As Long, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long
Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal lpBuffer As Long, ByVal dwNumberOfBytesToRead As Long, lpdwNumberOfBytesRead As Long) As Long
Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInternet As Long) As Long
Private Declare Function InternetQueryDataAvailable Lib "wininet" (ByVal hFile As Long, lpdwNumberOfBytesAvailable As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long
 
 
Public Sub ViewSource(ByVal szUrl As String, ByVal szFile As String)
  
  Dim Buffer() As Byte ' raw buffer.
  Dim hOpen As Long
  Dim hConn As Long
  Dim hFile As Long
  Dim dwBytes As Long
  Dim dwWrittenBytes As Long
  Dim dwReadBytes As Long
  
  ' Create and overwrite the file with write access.
  hFile = CreateFileW(StrPtr("\\?\" & szFile), GENERIC_WRITE, 0, 0, CREATE_ALWAYS, 0, 0)

  ' If this fails check rights and permissions.  This call will succeed if the
  ' application hasn't been manifested using AsInvoker in this case check the following
  ' locations C:\Users\Username\AppData\Local\VirtualStore
  
  If hFile = INVALID_HANDLE_VALUE Then
    
    Debug.Print Err.LastDllError
    
    Exit Sub ' _leave
    
  End If
  
  ' Open connection force sync download.
  hOpen = InternetOpenW(0, 1, 0, 0, WININET_API_FLAG_SYNC)
  
  ' Establish connection to URL.
  hConn = InternetOpenUrlW(hOpen, StrPtr(szUrl), 0, 0, 0, 0)
  
  Do
  
    If InternetQueryDataAvailable(hConn, dwBytes, 0, 0) Then
    
      ' Allocate the amount that is immediatley available.
      ReDim Buffer(dwBytes) As Byte
 
    Else
    
      ' Allocate 4KB chunk if the amount available fails.
      dwBytes = 4096
      
      ReDim Buffer(dwBytes) As Byte
      
    End If
    
    If InternetReadFile(hConn, VarPtr(Buffer(0)), dwBytes, dwReadBytes) Then
      
      ' At this point we can write the bytes to the file.
      WriteFile hFile, VarPtr(Buffer(0)), dwReadBytes, dwWrittenBytes, 0
      
    Else
    
      Exit Do ' _leave
    
    End If
    
  Loop Until dwReadBytes = 0
  
  ' perform cleanup.
  InternetCloseHandle hConn
  InternetCloseHandle hOpen
  CloseHandle hFile
  
  ' free memory
  Erase Buffer
  
End Sub

Open in new window

Author

Commented:
Dear eql1044
It works fine at www.google.com but when I use the function of
ViewSource(ByVal szUrl As String, ByVal szFile As String)  to access my bank account
webpage, it is failed even after succefully login the bank account. The error message is as follows
================================================
"Session Time Out
Either the system is temporarily unavailable or you have exceeded the time needed to complete "
=================================================
I might know the actual reason, that might be becaues the myIE.object is set
for login object is different object created from the function of Viewsource with different URL. In other words, create two objects,one by my savewebsite() and
other one is from Viewsource(). The last one is no permission from bank to browser
other bank wbepage.

Sub savewebsite()
Dim myIE As SHDocVw.InternetExplorer  
Set myIE = CreateObject("InternetExplorer.Application")
with myIE
.Visible = True
 .navigate "www.mybank.com
.document.all("username")="AAAAA"
.document.all("password")="abcsde"
.documnet.all("login").click
Do Until Not .Busy And .readyState = READYSTATE_COMPLETE Loop
    viewsource(http://my-otherbank-webpage", "C;\test.html")
End with    
End sub
 -----------------------------------------------------------------------------------------------
SO, now I try to use   string variable to store the .documment.innerhtml
in the webpage I want to grab after login and then use write function to write
to a html file such as code follows and it is working succesfully, anyway, thanks
for your triggering to the final answer. If possible, I want to use  execWB cmmand
to do the similar operation. Is it possible?  Please advise, I want to see
which way is better and faster to grab the data.  

BR
Duncan

' The new function  of savewbeiste() is working with writefile() to grab  innerhtml into D: \test.html file
--------------
Sub new-savewebsite()
Dim myIE As SHDocVw.InternetExplorer  
Set myIE = CreateObject("InternetExplorer.Application")
with myIE
Dim resulthtml as string
Dim filelocation As String

.Visible = True
 .navigate "www.mybank.com
.document.all("username")="AAAAA"
.document.all("password")="abcsde"
.documnet.all("login").click
Do Until Not .Busy And .readyState = READYSTATE_COMPLETE Loop
.naviate "http://my-otherbank-webpage"
Do Until Not .Busy And .readyState = READYSTATE_COMPLETE Loop
resulthtml = .document.innerhtml
filelocation="D:\test.html"
    Call writefile(resulthtml, filelocation)
End with    
End sub

Sub WriteFile(ByRef resulthtml As String, byref filelocation as string)
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory
Dim fnum As Variant
myfile = location
'set and open file for output
fnum = FreeFile()
Open myfile For Output As fnum
'write project info and then a blank line. Note the comma is required
Write #fnum, resulthtml
Write #fnum,
Close #fnum
End Sub

 

Author

Commented:
Dear Egl1044,

Suppose it should be working for viewsource() if the function can
use the myIE object I created after the login my bank account  since I think two function of  savewebiste() and new-savewebsite() is using similar approach.

Any idea let viewsource() is also working with myIE object I created  ?

Duncan
 
Top Expert 2010

Commented:
Yeah that should work as well... I don't think there is an easy solution to view web source using wbExec.  You might be able to download the entire document but I don't know if it would prompt save As dialog there is flag for disabling prompts but don't know if that would actually work for newer operating systems considering the security risks involved.

Author

Commented:
Anyway, thanks, if more idea
please send it to me for review