PivotCharts (SOLVED) and Internet Explorer Automation

bj6671

New Member
Joined
Jul 25, 2007
Messages
16
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]
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

bj6671

New Member
Joined
Jul 25, 2007
Messages
16
I have to give my thread a bit of a bump.
The project is due to complete today, and no one has even been able to offer me the hope that a solution even exists. :cry:

Can someone throw me a bone?
 

bj6671

New Member
Joined
Jul 25, 2007
Messages
16
PARTIALLY SOLVED!
I finally figured out problem 1.
For anyone who comes across this in their own search, here is the solution.
What confused me was I was seeing what I needed as a column, when the chart was seeing it as a POINT!

Dim Pcm As OWC10.ChPoints
Me.ChartSpace.Charts(0).SeriesCollection(0).Interior.Color = vbBlue
Set Pcm = Me.ChartSpace.Charts(0).SeriesCollection(0).Points '.GetValue
Pcm("Fred").Interior.Color = vbRed

This sets all the columns on the bar chart blue, then changes the one representing "Fred" to red.
Note, I'm using OWC10, you might be using OWC11. I don't know why I'm using OWC10. When I had referenced OWC11, I kept getting mismatches. I finally found a way to ask my pivot chart what version it was and it said it was 10. So I changed my reference and it has been working great since.

Now if someone could help with making IE do what I want...

---------------
All pivot chart stuff completely solved, I found a MS article called Off the Chart Access and it told me where to find the OWCVBA10.CHM file.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,917
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top