Link to home
Start Free TrialLog in
Avatar of bsharath
bsharathFlag for India

asked on

Any script which can fill the time sheet

Hi,

I need to fill the time sheet every day .Is there a way to enable keystoke that can open the intranet site need 3 click that can finish the job.

Any way to automate this.

regards
Sharath
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Hi Sharath,

Can you please provide more information? Your question as it stands is a little vague.

Regards,

Wayne
Avatar of bsharath

ASKER

Hi webtubbs,

I need to go to a intranet website every day to fill in the details on how many hrs i have worked for the day.
The Hrs is the same and we have a favorites set where in when we click it the details come automatically.
What i mean is all the settings is preset.So need a script which can open that webpage.Instruct it to click these buttons as defined (Or where ever text found as define Click on it) At last save settings and close Ie.

Hi, you could try something like this, but you'd need to identify the "names" of the controls that hold data, and click a button:
'=============
strURL = "http://mysite.com"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
Wend

objIE.Document.ALL("hours").Value = 8
objIE.Document.ALL("name").Value = "Full Name"
objIE.Document.ALL("Submit").Click

Set ObjIE = Nothing
'=============

Regards,

Rob.
Rob where should i give the clicks.
As first it need to click "Load Defaults" (Button)
And then change the duration to 9 hrs and 00 min (Drop down menu)
and then click  "Save Duration" (Button)
You will have to "view source" on the code, and find the names of the those controls, but once you have those, change this:
objIE.Document.ALL("hours").Value = 8
objIE.Document.ALL("name").Value = "Full Name"
objIE.Document.ALL("Submit").Click

to this, replacing the names in the quotes with those you obtain from the page:
objIE.Document.ALL("btnLoadDefaults").Click
objIE.Document.ALL("hours").Value = "9 hrs and 00 min"
objIE.Document.ALL("btnSave").Click

Regards,

Rob.
Rob i have put the Source here can you please check.

http://www.filepatio.com/7304
I dont know if the uploaded file will help...
I get this...
---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      1
Error:      Object required: 'objIE.Document.ALL(...)'
Code:      800A01A8
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
The uploaded file looks like the code of the parent page.  Does it load another page within this, that has the buttons you are after?

Rob.
Rob its in a split sheets.
Can you see this
http://www.filepatio.com/7305
Try these lines:
objIE.Document.ALL(""btnLoadDefaults"").Click
objIE.Document.ALL("sltSTHrs").Item("9").Select
objIE.Document.ALL("sltSTMins").Item("0").Select
objIE.Document.ALL("sltETHrs").Item("17").Select
objIE.Document.ALL("sltETMins").Item("0").Select
objIE.Document.ALL("btnSaveSettings").Click

I'm not sure about the time ones, I haven't tried auto-setting a list box before....

Regards,

Rob.
Can i have the full code Rob...
'=============
strURL = "http://mysite.com"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
Wend

objIE.Document.ALL(""btnLoadDefaults"").Click
objIE.Document.ALL("sltSTHrs").Item("9").Select
objIE.Document.ALL("sltSTMins").Item("0").Select
objIE.Document.ALL("sltETHrs").Item("17").Select
objIE.Document.ALL("sltETMins").Item("0").Select
objIE.Document.ALL("btnSaveSettings").Click

Set ObjIE = Nothing
'=============

Regards,

Rob.
I get this Rob...
---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      22
Error:      Expected ')'
Code:      800A03EE
Source:       Microsoft VBScript compilation error

---------------------------
OK  
---------------------------
Ooops, cut and paste problem, change this line
objIE.Document.ALL(""btnLoadDefaults"").Click

to this:
objIE.Document.ALL("btnLoadDefaults").Click

Rob.
Rob i get this...

---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      1
Error:      Object required: 'objIE.Document.ALL(...)'
Code:      800A01A8
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------

I know without you seeing what it is its difficult to do it....
Hmmmm, yeah.....you may have to replace the
objie.Document.ALL(""btnLoadDefaults"").Click
bit with something like
objIE.Document.timesheeform.btnLoadDefaults.Click

but I'm not sure what the "timesheetform" form name actually is....I'll have another look tomorrow....

Regards,

Rob.
Ok Rob

Now i get this.

---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      1
Error:      Object doesn't support this property or method: 'objIE.Document.timesheeform'
Code:      800A01B6
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
In the Source
the first button to be clicked is this

basis" text="Make Default" type="submit" /> 
              <input name="btnLoadDefaults" id="btnLoadDefaults" type="submit" class="greybtn_big" onMouseOut="this.className='greybtn_big';" title="Loads the timecard(s) detail set as default into the
Hmmm, what about this:
objIE.document.getElementById('iframe_time_entry').btnLoadDefaults.Click

or this
objIE.top.frames['iframe_time_entry'].document.all.btnLoadDefaults.Click

or this
objIE.top.frames['iframe_time_entry'].btnLoadDefaults.Click

Regards,

Rob.
I get this Rob for all the 3

---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      1
Error:      Object required: ''iframe_time_entry''
Code:      800A01A8
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
What about:
objIE.document.frames['iframe_time_entry'].btnLoadDefaults.Click

you need to get the reference to the right object in the window.....

Regards,

Rob.
I get this.

---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      1
Error:      Object required: ''iframe_time_entry''
Code:      800A01A8
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
Rob
you need to get the reference to the right object in the window.....
How can i find this.
Hmmm, OK, try this:

'==============
strURL = "http://yoursite/time_sheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
Wend

objIE.document.frames("iframe_time_entry").document.all.btnLoadDefaults.Click
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "9"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "0"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "17"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "0"
objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.Click

Set ObjIE = Nothing
'==============

Regards,

Rob.
I get this ROb...

---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      1
Error:      Access is denied.

Code:      80070005
Source:       (null)

---------------------------
OK  
---------------------------


Rob did you have time for the Users creation..
Whoa!  Strange....do you need to log into the site first?  Are you sure you have
strURL set exactly to what you would type in the address bar?

Rob.
Rob i am just changing the Strurl to the website.
It opens the website after which i get the error.
Once it opens it has to first click the "Load the defaults"

Now when i ran it i got this.

---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      10
Char:      1
Error:      Member not found.

Code:      80020003
Source:       (null)

---------------------------
OK  
---------------------------
Try this....making sure to change strURL back to your site's URL....
Does it actually appear to click the button?

'=============
strURL = "http://yoursite/timesheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
      WScript.Sleep 100
Wend

WScript.Sleep 1000

objIE.document.frames("iframe_time_entry").document.all.btnLoadDefaults.Click
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "9"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "0"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "17"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "0"
objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.Click

Set ObjIE = Nothing
'=============

Regards,

Rob.
Rob i get this...
---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      13
Char:      1
Error:      Access is denied.

Code:      80070005
Source:       (null)

---------------------------
OK  
---------------------------

Any help with the User's and Mailbox creation...
Candru had directed me to some website where i could get the code .Can you please look into it and help me in tweaking it to my environment please...
That's strange.  Why would access be dened?  Does the page display?  Can you do the above steps manually?

What if you comment out both of the "click" lines, and just leave the time values....do those boxes then display those times?

Regards,

Rob.
Yes Rob the page opens after which i get this error...
I commented these 2 line.
'objIE.document.frames("iframe_time_entry").document.all.btnLoadDefaults.Click
'objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.Click

I get this...
---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      14
Char:      1
Error:      Access is denied.

Code:      80070005
Source:       (null)

---------------------------
OK  
---------------------------
This is obviously some sort of security issue....I'm running this on my WinXP SP2 machine, with IE 6 with SP2.

Can you try it on the same, or even a Windows 2000 box?  Perhaps the Enhanced Internet Security of your Windows 2003 box is preventing this from working?

Regards,

Rob.
Hi Rob...I just tested in a Xp machine also the same error is coming...
First can the code just click. (Load defaults)
After this we can try the Hrs...
None of it will work without overcoming this Access Denied issue....it appears to be a "cross-frame" scripting issue with iFrames, security related, particularly, it seems, to a cross-domain reference....
I'm looking into it, but don't know much about it.....

Regards,

Rob.
Ok Rob shall wait...Any help with the excel user creation...Q...
Sharath, both of these files that make up this website....are they in the same virtual folder on the webserver, or are they located on different webservers?

Rob.
Its on a single webserver Rob...
That's what I thought....very strange...I uploaded both of those file to the same folder on my webserver, and don't have a problem.....I'll keep looking....

Rob.
Sharath, can you please try putting the URL address of your site for the time sheet into your "Trusted Sites" list in Tools --> Internet Options --> Security

Rob.
Rob now i dont get any error.
I am using this script.


'=============
strURL = "http://Sitename
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
      WScript.Sleep 100
Wend

WScript.Sleep 1000

objIE.document.frames("iframe_time_entry").document.all.btnLoadDefaults.Click
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "9"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "0"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "17"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "0"
objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.Click

Set ObjIE = Nothing
'=============

It just opens the page but no click or does not click the Load defaults button...
Hmmm, so we're getting closer.....if you run this:

'===============
strURL = "http://members.optusnet.com.au/lisaquinlivan/time_entry/timesheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
      WScript.Sleep 100
Wend

WScript.Sleep 1000

'objIE.Document.frames("iframe_time_entry").document.all.btnLoadDefaults.Click
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "12"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "14"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "30"
'objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.Click

Set ObjIE = Nothing
'===============

It won't click the buttons, but does it at least change the times to 12:30 and 14:30?

Rob.
Yes Rob it has changed the Time...
OK, good....so it's working.....so, if you try again, uncomment those, or maybe have to wait for the LoadDefaults to work....try this:

'===============
strURL = "http://members.optusnet.com.au/lisaquinlivan/time_entry/timesheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
      WScript.Sleep 100
Wend

WScript.Sleep 1000

objIE.Document.frames("iframe_time_entry").document.all.btnLoadDefaults.Click
While objIE.Busy
      WScript.Sleep 100
Wend
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "12"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "14"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.Click
While objIE.Busy
      WScript.Sleep 100
Wend

Set ObjIE = Nothing
'===============

Regards,

Rob.
Rob do you want me to try with this link
"http://members.optusnet.com.au/lisaquinlivan/time_entry/timesheet.htm"
Or my link?

In your link i get as
Method Not Allowed
The requested method POST is not allowed for the URL /lisaquinlivan/time_entry/time_entry.aspx.


--------------------------------------------------------------------------------

Apache/1.3.27 Server at members.optusnet.com.au Port 80

With my link i get it to just change the time but load defaults button is not clicking...
Oh yeah, sorry, that was my site.....Optusnet won't allow me to run asp code, so the Load Defaults doesn't work......

I'm surprised it's not clicking the button for your site......I'll have another look at it tomorrow.....

Regards,

Rob.
Sharath, try this, again changing strURL:

'=========
strURL = "http://yoursite/timesheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
      WScript.Sleep 100
Wend

WScript.Sleep 1000

'objIE.Document.frames("iframe_time_entry").document.all.btnLoadDefaults.click()
objIE.Document.frames("iframe_time_entry").document.all.save_defaults.value = 1
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "12"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "14"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "30"
'objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.click()

Set ObjIE = Nothing
'=========

If that doesn't work, I'm wondering if you uncomment the .click lines again, if they work this time, just because of the brackets on the end....

What version of which browser are you using?

Regards,

Rob.
Or even this:

'============
strURL = "http://yoursite/timesheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
      WScript.Sleep 100
Wend

WScript.Sleep 1000

objIE.Document.frames("iframe_time_entry").document.all.btnLoadDefaults.fireEvent("onClick")
objIE.Document.frames("iframe_time_entry").document.all.save_defaults.value = 1
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "12"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "14"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.fireEvent("onClick")

Set ObjIE = Nothing
'============

Regards,

Rob.
I tried all the 3 ways but none cam up.

I have Ie 7.0 installed.
The last one changed the time and was the same .The other ones used to change as you defined and come back to normal
Is there any chance you could try this on IE6?

Rob.
Rob i check in a ie 6 machine also but it does not click the Defaults button
Sharath, can you try this:

'============
strURL = "http://yoursite/timesheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.Busy
      WScript.Sleep 100
Wend

WScript.Sleep 10000

objIE.Document.frames("iframe_time_entry").document.all.btnLoadDefaults.click
objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "9"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "00"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "17"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "00"
objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.click

Set ObjIE = Nothing
'============

All I did here was increase the Sleep time to 10 seconds.....
I have had the opportunity to upload it to another site, which happened to be much slower.
As a result, I have realised that even though we have the
   While objIE.Busy
part, this doesn't account for the extra load time of the iFrame documents.  So I increased the wait to 10 seconds, which was more than enough time, and then the button got clicked.....

Fingers crossed....

Regards,

Rob.
I get this .


---------------------------
Windows Script Host
---------------------------
Script:      C:\Documents and Settings\sharathr\Desktop\Timesheet.vbs
Line:      14
Char:      1
Error:      Object doesn't support this property or method: 'objIE.Document.frames(...).Document.all.btnLoadDefaults'
Code:      800A01B6
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
Huh, that seems to be a backwards step!  Did you make sure strURL was right?

I'll come back on Monday for another go!

Rob.
URL is correct...
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Now the load defaults is working

Now next it has to click"L2 Support" Hyperlink and change the time to total 9.00 and "Save and close"
OK cool.  Where is the "L2 Support" hyperlink?  Is it on another page that I don't have?

If you look in the source for that page, and search for
L2 Support
can you tell me what the href value is for that hyperlink?  I need to know whether it runs a function, or just loads another page....

Also, for that same hyperlink, where the href is, does it have an "id" tag, or a "name" tag?  I'm concerned we won't be able to "click" a hyperlink programmatically....

Regards,

Rob.
Thanks a lot Rob for all the help...
I think the above script is ok... As i had a option there where i can save all the details.When load defaults is selected all is done.
OK fair enough.  I just got something working though I think....you can try this if you want:

'===============
strURL = "http://yoursite/timesheet.htm"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate strURL
objIE.Visible = True

While objIE.ReadyState <> 4
      WScript.Sleep 100
Wend

'objIE.Document.frames("iframe_time_entry").document.all.btnLoadDefaults.Click
While objIE.ReadyState <> 4
      WScript.Sleep 100
Wend

'Source: https://www.experts-exchange.com/questions/22045830/VBA-to-open-web-page-find-text-and-click-on-text's-URL-link.html
Set colATags = objIE.Document.frames("iframe_time_entry").document.all.tags("A")

For intHRef = 0 To colATags.Length
      If InStr(colATags.Item(intHRef).InnerHTML, "L2 Support") > 0 Then
            colATags.Item(intHRef).Click
            Exit For
      End If
Next

While objIE.ReadyState <> 4
      WScript.Sleep 100
Wend

objIE.Document.frames("iframe_time_entry").document.all.sltSTHrs.Value = "12"
objIE.Document.frames("iframe_time_entry").document.all.sltSTMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.sltETHrs.Value = "14"
objIE.Document.frames("iframe_time_entry").document.all.sltETMins.Value = "30"
objIE.Document.frames("iframe_time_entry").document.all.btnSaveSettings.Click
While objIE.ReadyState <> 4
      WScript.Sleep 100
Wend

Set objIE = Nothing
MsgBox "Done"
'===============

Regards,

Rob.
Thanks Rob... Any help with the Excel user creation part...
Yeah, I've been busy...sorry....I'll try to have a look tomorrow afternoon....I have a four hour training session in the morning....

Rob.
Ok Rob...Thanks...