Copy and Paste an HTM file with Vba
On Apr 8, 5:12*am, Gwyndalf
wrote:
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
" wrote:
On Apr 7, 6:03 am, Gwyndalf
wrote:
Thanks Ron - works a dream - where did you find the number codes? - I have
searched for a reference to them but without any joy
" wrote:
On Apr 7, 3:14 am, Gwyndalf
wrote:
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
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- Hide quoted text -
- Show quoted text -
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- Hide quoted text -
- Show quoted text -
Thanks Gwyn!..Ron
|