| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy, paste without file name referenced after paste | Excel Discussion (Misc queries) | |||
| How can I allow only copy of a file not cut and paste? | Excel Discussion (Misc queries) | |||
| copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
| copy/paste from one file to another without file name reference | Excel Discussion (Misc queries) | |||
Hybrid Mode
