| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste an HTM file with Vba
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy and Paste an HTM file with Vba
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy and Paste an HTM file with Vba
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy and Paste an HTM file with Vba
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy and Paste an HTM file with Vba
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 |
|
#6
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
