Copy and Paste an HTM file with Vba

G

Gwyndalf

I'm fairly new to Vba and have written some code that will open IE and allow
me to navigate to my chosen file. (I cannot use WebQuery to retrieve the
data as it fails to identify any there.) Manual Copy and paste does the
trick but I'm seeking to automate the process. Can anyone advise me how to
instruct IE to 'select all', 'copy' and then paste it into my workbook on the
sheet named 'Hands'.

Sub ListLinks()

Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim MyURL As String
' I need this to be variable/ user defined
MyURL = Application.GetOpenFilename()
Set IeApp = New InternetExplorer

'Make it visible
IeApp.Visible = True

'define the page to open
sURL = MyURL

'navigate to the page
IeApp.navigate sURL

'Pause the macro using a loop until the
'page is fully loaded
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE

Code needed here to copy and paste entire web page

Worksheets("Hands").Activate
Range("A1").Select
ActiveSheet.Paste

'Clean up
Set IeApp = Nothing

End Sub
 
O

oitbso

I'm fairly new to Vba and have written some code that will open IE and allow
me to navigate to my chosen file.  (I cannot use WebQuery to retrieve the
data as it fails to identify any there.)  Manual Copy and paste does the
trick but I'm seeking to automate the process.  Can anyone advise me how to
instruct IE to 'select all', 'copy' and then paste it into my workbook onthe
sheet named 'Hands'.

Sub ListLinks()

    Dim IeApp As InternetExplorer
    Dim sURL As String
    Dim IeDoc As Object
    Dim MyURL As String
    ' I need this to be variable/ user defined
    MyURL = Application.GetOpenFilename()
    Set IeApp = New InternetExplorer

    'Make it visible
    IeApp.Visible = True

    'define the page to open
    sURL = MyURL

    'navigate to the page
    IeApp.navigate sURL

    'Pause the macro using a loop until the
    'page is fully loaded
    Do
    Loop Until IeApp.readyState = READYSTATE_COMPLETE

    Code needed here to copy and paste entire web page

    Worksheets("Hands").Activate
    Range("A1").Select
    ActiveSheet.Paste

    'Clean up
    Set IeApp = Nothing

End Sub
Click to expand...

Usually, one of the following two constructions would accomplish
this...Ron

ie.ExecWB OLECMDID_SELECTALL, _ OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

or

ie.ExecWB 17, 2
ie.ExecWB 12, 0
 
G

Gwyndalf

Thanks Ron - works a dream - where did you find the number codes? - I have
searched for a reference to them but without any joy
 
O

oitbso

Thanks Ron - works a dream - where did you find the number codes? - I have
searched for a reference to them but without any joy







- Show quoted text -
Click to expand...

Found the number codes by hanging around here. I believe the two
constructions relate to late and early binding respectively. Related
to this, if VBA Tools-References (Microsoft Internet Controls) is
selected, then the "ExecWB OLECMDID" option works; if (Microsoft
Internet Controls) is not selected, then only the "ExecWB 17,2 / 12,0"
construction works.

BTW, when I was typing in the OLECMDID construction, I thought it was
going to break, so I inserted an unnecessary underscore. Here is the
correct construction...Ron

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
 
G

Gwyndalf

Ron

Think I may have found them!! Just for ref in case you might need any of
them. TY again for your assistance

Gwyn

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
 
O

oitbso

Ron

Think I may have found them!!  Just for ref in case you might need any of
them.  TY again for your assistance

Gwyn

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








- Show quoted text -
Click to expand...

Thanks Gwyn!..Ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy and Paste web page using Vba 4
Macro to print web page and close browser 6
loop until web page loaded using Netscape Navigator 2
How to automate web login? 0
VBA to load webnsite and pass details 2
VBA to open website and pass details 1
.Paste not working everytime 2
Launch IE and scrap data from the web site 1

Top