It's near the end of the day and I've made no headway on these two problems:
1:
I have a PivotChart subform which displays workload by employee. I have another table which has which employees are on vacation. I can easily query by day and return a list of employees that are off on a given day then put that list into an array. what I would like to do is take that array and change the columns represented by those employees to a different color (vbRed) to indicate that someone on vacation has work loaded to them and it needs moved. What has been stumping me is I can't find the vb command to change a single column to a different color. This pivotchart only has one series, the horizontal row details the emloyee and the vertical axis represents how much work they have. I can click one column with the mouse and click the paintcan to change it's color, I'm looking for a way to do that in code. If you can supply a code snippet on how to change the color of a pivot table bar based on it's "name" or "caption" or something similar. Please keep in mind they are all in the same series, so this won't work:
Me.ChartSpace.Charts(0).SeriesCollection(0).Interior.Color = RGB(255, 0, 0)
It changes all of the columns at once.
2:
I have code that exports a query into a tab delimited text file. That file has to be submitted via a web form. To get to this web form, I have to visit a login page, click a link to get to one form, then click a button to get to the final form, change a combo box on the web form, then input the path and click submit. These pages have to be visited in order, and to get to the next page you HAVE to activate the link. Simply having the browser jump to the next URL doesn't work and the security disables the final form. You can get to the first page with FollowHyperlink, or through adding the URL to a command button, or by adding a Web Control to the access form. I can't get "SendKeys" to work consistantly. I have code that works 10% of the time.
Dim objIE As InternetExplorer
Dim dteWait As Date
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Application.Visible = True
objIE.Navigate "http://www.intranetpage.com/startpage.cgi"
dteWait = Now
Do Until Now > dteWait + 0.0001
Loop
Call SendKeys("^f", True)
dteWait = Now
Do Until Now > dteWait + 0.000005
Loop
Call SendKeys("link~", True)
dteWait = Now
Do Until Now > dteWait + 0.000005
Loop
Call SendKeys("{ESC}{TAB}~", True)
dteWait = Now
Do Until Now > dteWait + 0.00001
Loop
Call SendKeys("{TAB}{TAB}{TAB}{TAB}{TAB}~", True)
When this doesn't work, it ISN'T a timing issue. The wait statements are just there to give the computer time to bring up the pages (if there is a better way of waiting without the loop, let me know).
What fails 90% of the time is Sendkeys. Sometimes it sends them, othertimes it doesn't. Mostly it is the ctrl-f ("^f") that doesn't work. At one point in time, I had about 15 calls to ctrl-f in different ways (^f, ^F, ^(f), ^(F), ^^f, ^ff, ^f, ^f, ^f, ^f, ^f, etc...) and the find page would still only come up maybe 20% of the time. It is supposed to bring up the page, ctrl-f to find the work "link" then closes the find box, highlights the link and presses enter to follow it, then tabs down to the next button and presses enter. I haven't botherd writing the last part of this as it already rarely works.
The main problem is that if that combo box isn't changed to the correct setting, it does a lot worse then just not work, it will delete data about the work orders instead. If I can get the automation to work just far enough to make sure that option is changed, that will be enough.
Is there a better way of automating IE from Access? When I tried using a Web Control on the form, the sendkeys seemed to work a lot more often, however Enter didn't follow a highlighted link or activated a button. Is there a way to have it click instead? Or better yet someway to indicate to it which link to follow?[/b]
1:
I have a PivotChart subform which displays workload by employee. I have another table which has which employees are on vacation. I can easily query by day and return a list of employees that are off on a given day then put that list into an array. what I would like to do is take that array and change the columns represented by those employees to a different color (vbRed) to indicate that someone on vacation has work loaded to them and it needs moved. What has been stumping me is I can't find the vb command to change a single column to a different color. This pivotchart only has one series, the horizontal row details the emloyee and the vertical axis represents how much work they have. I can click one column with the mouse and click the paintcan to change it's color, I'm looking for a way to do that in code. If you can supply a code snippet on how to change the color of a pivot table bar based on it's "name" or "caption" or something similar. Please keep in mind they are all in the same series, so this won't work:
Me.ChartSpace.Charts(0).SeriesCollection(0).Interior.Color = RGB(255, 0, 0)
It changes all of the columns at once.
2:
I have code that exports a query into a tab delimited text file. That file has to be submitted via a web form. To get to this web form, I have to visit a login page, click a link to get to one form, then click a button to get to the final form, change a combo box on the web form, then input the path and click submit. These pages have to be visited in order, and to get to the next page you HAVE to activate the link. Simply having the browser jump to the next URL doesn't work and the security disables the final form. You can get to the first page with FollowHyperlink, or through adding the URL to a command button, or by adding a Web Control to the access form. I can't get "SendKeys" to work consistantly. I have code that works 10% of the time.
Dim objIE As InternetExplorer
Dim dteWait As Date
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Application.Visible = True
objIE.Navigate "http://www.intranetpage.com/startpage.cgi"
dteWait = Now
Do Until Now > dteWait + 0.0001
Loop
Call SendKeys("^f", True)
dteWait = Now
Do Until Now > dteWait + 0.000005
Loop
Call SendKeys("link~", True)
dteWait = Now
Do Until Now > dteWait + 0.000005
Loop
Call SendKeys("{ESC}{TAB}~", True)
dteWait = Now
Do Until Now > dteWait + 0.00001
Loop
Call SendKeys("{TAB}{TAB}{TAB}{TAB}{TAB}~", True)
When this doesn't work, it ISN'T a timing issue. The wait statements are just there to give the computer time to bring up the pages (if there is a better way of waiting without the loop, let me know).
What fails 90% of the time is Sendkeys. Sometimes it sends them, othertimes it doesn't. Mostly it is the ctrl-f ("^f") that doesn't work. At one point in time, I had about 15 calls to ctrl-f in different ways (^f, ^F, ^(f), ^(F), ^^f, ^ff, ^f, ^f, ^f, ^f, ^f, etc...) and the find page would still only come up maybe 20% of the time. It is supposed to bring up the page, ctrl-f to find the work "link" then closes the find box, highlights the link and presses enter to follow it, then tabs down to the next button and presses enter. I haven't botherd writing the last part of this as it already rarely works.
The main problem is that if that combo box isn't changed to the correct setting, it does a lot worse then just not work, it will delete data about the work orders instead. If I can get the automation to work just far enough to make sure that option is changed, that will be enough.
Is there a better way of automating IE from Access? When I tried using a Web Control on the form, the sendkeys seemed to work a lot more often, however Enter didn't follow a highlighted link or activated a button. Is there a way to have it click instead? Or better yet someway to indicate to it which link to follow?[/b]
