Hi All Experts out there,
I need your help badly to resolve my issue with this macro (I've got this from the other thread and hoping it will work as part of my macro procedures). I'm very new in this subject matter and could not identify the solution to this after spending severals hours or even days trying some other options and doing research in the internet.
Here's the macro, which is part of the bigger macro I am developing. This is supposed to be the second part of the bigger macro which is copying the data from the specified URL to excel and then third part of the bigger macro will use these copied data.
Sub CopyWebpageData()
'Need references to Microsoft HTML Object Library and Microsoft Internet Controls
Const cURL = "https://apilifestyle.com.au"
Const cUsername = "*****"
Const cPassword = "*****"
Dim IE As InternetExplorer
Dim doc As HTMLDocument
Dim LoginForm As HTMLFormElement
Dim UserNameInputBox As HTMLInputElement
Dim PasswordInputBox As HTMLInputElement
Dim SignInButton As HTMLInputButtonElement
Dim HTMLelement As IHTMLElement
Dim qt As QueryTable
Set IE = New InternetExplorer
IE.Visible = True
IE.Navigate cURL
'Wait for initial page to load
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
Set doc = IE.Document
'Get the only form on the page
Set LoginForm = doc.forms(0)
'Get the User Name textbox and populate it
'< input name="ctl00$ct$UserName" type="text" maxlength="30" id="ctl00_ct_UserName" style="width:160px;" />
Set UserNameInputBox = LoginForm.elements("ctl00$LoginControl1$txtLogin")
UserNameInputBox.Value = cUsername
'Get the password textbox and populate it
'< input name="ctl00$ct$Password" type="password" maxlength="30" id="ctl00_ct_Password" style="width:160px;" />
Set PasswordInputBox = LoginForm.elements("ctl00$LoginControl1$txtPassword")
PasswordInputBox.Value = cPassword
'Get the form input button and click it
'< input type="submit" name="ctl00$ct$uxBtnLogin" value="Sign In" o n c l i c k="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ct$uxBtnLogin", "", true, "Login", "", false, false))" id="ctl00_ct_uxBtnLogin" />
Set SignInButton = LoginForm.elements("ctl00$LoginControl1$btnLogin$implementation$field")
SignInButton.Click
'Wait for the new page to load
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
'Get the HTML document of the new page
Set doc = IE.Document
IE.Navigate "https://apilifestyle.com.au/offers/financial-services/advice" & ActiveCell.Value
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
ActiveWorksheet.Select
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End Sub
The macro perfectly works until the website is opened (but data is not copied to excel). After that, it gives me this error: "Runtime error '-2147467259 (80004005)': Automation error Unspecified error, and it highlights the "Set doc = IE.Document" in the script.
I hope you can help me here please.
Thanks,
Randy
I need your help badly to resolve my issue with this macro (I've got this from the other thread and hoping it will work as part of my macro procedures). I'm very new in this subject matter and could not identify the solution to this after spending severals hours or even days trying some other options and doing research in the internet.
Here's the macro, which is part of the bigger macro I am developing. This is supposed to be the second part of the bigger macro which is copying the data from the specified URL to excel and then third part of the bigger macro will use these copied data.
Sub CopyWebpageData()
'Need references to Microsoft HTML Object Library and Microsoft Internet Controls
Const cURL = "https://apilifestyle.com.au"
Const cUsername = "*****"
Const cPassword = "*****"
Dim IE As InternetExplorer
Dim doc As HTMLDocument
Dim LoginForm As HTMLFormElement
Dim UserNameInputBox As HTMLInputElement
Dim PasswordInputBox As HTMLInputElement
Dim SignInButton As HTMLInputButtonElement
Dim HTMLelement As IHTMLElement
Dim qt As QueryTable
Set IE = New InternetExplorer
IE.Visible = True
IE.Navigate cURL
'Wait for initial page to load
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
Set doc = IE.Document
'Get the only form on the page
Set LoginForm = doc.forms(0)
'Get the User Name textbox and populate it
'< input name="ctl00$ct$UserName" type="text" maxlength="30" id="ctl00_ct_UserName" style="width:160px;" />
Set UserNameInputBox = LoginForm.elements("ctl00$LoginControl1$txtLogin")
UserNameInputBox.Value = cUsername
'Get the password textbox and populate it
'< input name="ctl00$ct$Password" type="password" maxlength="30" id="ctl00_ct_Password" style="width:160px;" />
Set PasswordInputBox = LoginForm.elements("ctl00$LoginControl1$txtPassword")
PasswordInputBox.Value = cPassword
'Get the form input button and click it
'< input type="submit" name="ctl00$ct$uxBtnLogin" value="Sign In" o n c l i c k="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ct$uxBtnLogin", "", true, "Login", "", false, false))" id="ctl00_ct_uxBtnLogin" />
Set SignInButton = LoginForm.elements("ctl00$LoginControl1$btnLogin$implementation$field")
SignInButton.Click
'Wait for the new page to load
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
'Get the HTML document of the new page
Set doc = IE.Document
IE.Navigate "https://apilifestyle.com.au/offers/financial-services/advice" & ActiveCell.Value
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
ActiveWorksheet.Select
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End Sub
The macro perfectly works until the website is opened (but data is not copied to excel). After that, it gives me this error: "Runtime error '-2147467259 (80004005)': Automation error Unspecified error, and it highlights the "Set doc = IE.Document" in the script.
I hope you can help me here please.
Thanks,
Randy