Solved

Excel 2007 Refresh to odc MsAccess Connection losing conditional formatting - need VBA solution

Posted on 2010-11-07
10
917 Views
Last Modified: 2012-05-10
I am having trouble after the data refreshes in 2007 with the conditional formatting.  I have the developer "EnableFormatConditionsCalculations" set to True.  I also have External Data Properties Data Formatting and Layout with the Preserve Column Cell Formatting checked.  I've tried all of the "if the number of rows in the data range changes upon refresh" options.

I have 2 columns which are using the provided icon sets "3 arrows colored" and 1 column using the "3 traffic lights rimmed"

The worksheet columns are returning number data:  ie 100 for green arrow, < 100 and > 0 for yellow arrow and <=0 for red arrow

Ive tried some of the other code I found in the solutions however I cannot seem to modify it to make my situation work.  I placed a developer command button on my spreadsheet but I am lost getting Excel code to work.

Any help would be appreciated
0
Comment
Question by:mspcindc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 2

Expert Comment

by:briwagner
ID: 34083803
Have you tried the recalculation Hot Keys.

Shift+F9 Recalculates Active Worksheet.
CTRL+ALT+Shift+F9 Forces Excel to rebuild the dependency tree and forces recalculation.

Regards,

Brian
0
 

Author Comment

by:mspcindc
ID: 34084484
This did not seem to do anything to my spreadsheet.  Any other ideas?
0
 
LVL 2

Expert Comment

by:briwagner
ID: 34085081
Can you post a copy of the spreadsheet. Be advised that anything posted here is public, so do not post proprietary data.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:mspcindc
ID: 34089472
The spreadsheet is extremely proprietary however I think I have a better clarification for my problem.  The table linked is a pivot table.  My conditional formatting remains on all charts and tables links which  are not a pivot table.  I have seen that there are issues with the pivot table in other posts.  I am just not sure how to address it.  Do you still need my report?  
0
 
LVL 2

Expert Comment

by:briwagner
ID: 34089785
How are you setting up the conditional Formatting? There should be choices under Apply Rule To: for applying to all cells showing "Field Name" Values. Have you tried selecting these? If I just choose the cells in the pivot and refresh the formatting is only on the cells I selected originally. If I change the rule to All cells showing "Field Name" values, it changes with the refresh.

Brian
ScrnShot.jpg
0
 

Author Comment

by:mspcindc
ID: 34089913
I  hate to be an idiot - but I do not see the top part of the edit formatting rule where you chose the "All cells showing "Sum... on my spreadsheet.  Do I need to check a different box to have this portion appear?
0
 
LVL 2

Expert Comment

by:briwagner
ID: 34092093
Are the formatted cells actually in the pivot table or are they copies linked to the pivot table? If so, does the number of cells with data stay the same?
0
 

Author Comment

by:mspcindc
ID: 34094255
Hmm.  I do not know what to tell you.  I was preparing a table that I could attach for public viewing and made a copy of the spreadsheet for when I lost all of my formatting.  When I hit refresh this time - I did not lose my formatting.  My guess is that I may be experiencing a memory issue.  I have a Toshiba Portege R600 with an Intel Core 2 Duo CPU U9400 @ 1.40 GHz with 3.00GB of memory using the 32bit Windows Vista.  I should have enough memory however I have another spreadsheet that has multiple connections back to the access database which is starting to show issues by not redrawing the charts unless you click on them and then only half the chart shows up.  

This spreadsheet has been losing my conditional formatting for weeks - why it has decided not to do it this morning - I do not know.  I appreciate your time - If you think that this is all caused by memory and the conditional formatting is the last to fire, please let me know.  I am using the data connection wizard - odbc dsn connection to my database instead of queries because it is a deliverable.
If there is a more memory efficient way to connect, please let me know.

Thank you

Kathy
0
 
LVL 2

Accepted Solution

by:
briwagner earned 500 total points
ID: 34095243
In a situation where the problem is not always occurring, it could be caused by a multitude of things. Yes it could be memory related if you you normally have several large Excel files open and then didn't this morning. It could be the order of the steps you take to create and refresh the sheet.

You mention copying the sheet. Did you refresh the new copy or the old? Do you always copy the sheet first? Did you do anything different this morning from what you have done in the past?

My best recommendation for conserving memory when pulling data into Excel is to limit the data pull to only the data you need. Though I have a sheet that pulls back almost 900000 rows into a Pivot table and it is working fine.

If it continues to work with out issue, just calk it up to good living and don't question it, just go with it. It is nice when life throws one your way for free. :)


Regards,

Brian
0
 

Author Closing Comment

by:mspcindc
ID: 34123134
Thank you very much
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question