I am trying to spool one excel report from website by giving From date and To Date are as inputs iam able to give enter dates with my code but unable to clicking on export button
Below is the HTML source code for Export Report Button
What language is this? It doesn't look much like VBA.
By the way, looping around a DoEvents is a busy wait, which is a terrible thing to be doing. In this case, it will only be for a few seconds, so it will just suck power briefly, but it's still a terrible thing to do. However, the better option depends on which language you are using.
set btn = browser.document.getElementById("btnExportXLS")
btn.Click
that way you can break and see if btn is a valid element object
you could also try to submit the form containing the button
as the button has a mousedown event, you may also need to fire that in addition to the click
you need to look at what the form submit does and also the isTxtwithfocus event
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
. not getting any error but excel report is not getting generate.. and there is no page load problem. if see at HTML code of button. there is onclick function**"Validate()"** in that. my guess iss after validating form with this function then only excel file will get spool
thanks for the reply.. have tried your code, not getting error but excel file not getting generate.. there ia function " validate" in that.. please advise how to call or execute that function.
Code:
set btn = browser.document.getElementById("btnExportXLS")
btn.Click
my guess iss after validating form with this function then only excel file will get spool
there is an example of fireevent in your original code, you can test this with the events for the button to see if any make a difference
my guess some of the events are to prevent automated download of the files
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i tested some code, i was able to click the download button, but the download was blocked internet explorer
if i clicked the button manually i was presented with a download open /save dialog which would also be difficult to automate
you can test to see if it helps at all
Code:
Set btn = browser.document.getElementById("btnExportXLS")
btn.fireevent ("onmousedown")
btn.fireevent ("onclick")
btn.Click
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Thanks for the code, its working perfect now excel file is getting spooled. one more help on the same is any possibility to manipulate open /save dialog popup, i want to click on open button. thanks in advance.
i have successfully done this previously in vb6, you may need to have a separate process with a timer to find the window when it opens and click the button as required
it may be possible to do this within vba using a timer, but i am not sure if the timer will fire while the file save is open as a modal dialog
see if you can find any previous example in this forum
edit:
if you search on findwindow in this forum you will get many hits (some nothing to do with this topic, but many are related), with ideas and code that does or used to work, but no longer does, same applies to any code i post here, it may not work on later versions of internet explorer, which may use windowless controls, i have no idea what version you are using, or are likely to upgrade to at any time
i doubt that you can get the dowload url directly to download the excel without the dialog
Last edited by westconn1; Dec 10th, 2018 at 03:50 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Thanks for the reply, i am using send keys for the same, and iam able to click on open button with send keys but and its working but one problem after clicking open button
what alert are you getting?
is it an excel alert or someother?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
does the downloaded file open in the same instance of excel or a separate instance?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
yes its open same instance of excel, and i have other code need to run after this file got open.. is ther any way once file open then only my rest of the code should run.
do you call displayalerts = false before downloading the file? as i did not see it in your original code i could not tell when you are calling it
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
try moving your call to displayalerts, to the top of the procedure, before you try go to the website
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Thanks for the help, have run my code at your end,
Have place displayalerts but nothing is got.
oShell.SendKeys "%{O}" --- >>After this code, excel file should open but immediately below code is running
Application.Wait (Now + TimeValue("0:00:01"))
Dim ws As Worksheet --->> From this line code should start after excel open only
Dim flg As Integer
For Each ws In ActiveWorkbook.Worksheets
If InStr(ws.Name, "User") Then
ws.Activate
flg = 1
Exit For
End If
Next ws
If flg <> 1 Then
MsgBox "There is no -User Wise Productivity Detailed- sheet exist in this workbook"
Exit Sub --- >> after existing sub excel is opening.
End If
Last edited by devendra.dvm; Dec 11th, 2018 at 07:05 AM.
i do not know if it will help, but you could try doevents after the sendkeys or possibly a timer delay (which is different to application.wait) either an API timer or application.ontime, to see if any of thosewill allow the workbook to load before the running further code
with either sort of timer, you would have to move the last part of the code to the timer
i would try doevents then applicaion.ontime
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
I have change my code bit, used send keys to click on save button instead of click on open button, then i will open last saved file from download folder, now its working fine... thanks a lot for the help and for your time.. please don't mind if i ask any help in future..
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
url = "https://taskmf.karvymfs.com/customercare/login.aspx"
Set browser = CreateObject("internetexplorer.application")
browser.Visible = True
browser.navigate (url)
Do
DoEvents
Loop Until browser.readystate = 4
browser.document.getElementById("txtUserName").Value = "91473"
browser.document.getElementById("txtPassword").Value = "Karvy@12"
browser.document.getElementById("selFund").selectedIndex = "21"
browser.document.getElementById("btnSubmit").Click
Do While browser.busy: Loop
If browser.locationurl = url Then
Set warn = browser.document.getElementById("lbldisplay")
MsgBox warn.innerhtml
browser.Quit
Exit Sub
End If
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Thanks for the code, have tried your code but its prompting even i entered correct password and exiting sub, i have changed your code bit still no luck.