1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Formatting not retained when refreshing from a cube

Discussion in 'Business Applications' started by MessagesforDebi, Jan 18, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    We have a cube database and uses pivot tables in Excel 2010 to extract data. I create the extracts on spreadsheets and set up formats (highlights, decimals, centering, shading) and save the files. Each time I refresh, some of the formatting is lost, which means, I need to reformat and save. It is not all the formatting, just some that is lost and is seems over time, pieces of the formatting gets saved, but not all. I also have files that the format that I have saved is changed totally to some random format (ie black shading). I have about 100 extracts and the problem is consistent. My files have been sent to others who refresh off the same cube and their format changes are maintained. I was told by the cube people that my problem is with my excel, not the cube.
    Our IT people do not know where in excel the problem is, so have offered this site as the solution - any help you can provide would be appreciated.
     
  2. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Since you have an IT department, I would ask them to go to the Control Panel > Add Remove Programs > Select Microsoft Office > Change and then select Repair option.
     
  3. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    The IT people did the change you recommended and it did not make any difference. The problem is still there.
     
  4. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    With an IT Dept. for support, if the issue has not been identified as yet, it may be helpful if you log in to another workstation and test it under your ID. If it works correctly there, then there can be no dispute about the issue being with your workstation. Backing up, then Replacing or reloading your workstation will be quickest resolution in this case.
     
  5. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    This is what we have done. The IT guy did the repair and there was no change. He brought over a fresh computer and I signed on and opened the file and refreshed and the same thing happened. I send my file to another person in my group and when they opened the file and refreshed, the same thing happened to them. There are no other people that I work with that have a problem like this with their files and refreshing from the cube. The IT guy said, it is not a system thing. He said it seems to be a excel workbook issue. I had tried (again) doing a fresh creation of a report from the cube and it does the same thing. Could there be something in the workbook defaults that might be causing this? I could not find the xbook.xls or master template for the workbook.
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Make sure your format is not left as "General".
    Change the whole column's format to anything other than General will prevent excel from guessing a new format for every entry.
     
  7. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    Where do I check for the "general format"
     
  8. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    Where do I check for the "general format". I have already saved specific format (shading, fonts, decimals) etc.
     
  9. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    From here, it is sounding like 2 processes. The First being the actual import, where the data is selected and can be formatted as part of the data retrieval. This is where the 'General' format mentioned above comes in. In this process, anyone using the same query should have the same data presented to them in the same way. The second part, is the pretty it up part. This is making it look the way it should look after the import. This should be the part that is lost on others, because this formatting does not exist in the original query, where the formats are initially set, and what they will revert to if the query is redone. Now the question becomes, are the queries you generate exactly the same as those created by someone else when used by yet another person? Are there any written guidelines to do the procedure in the office? Often times there are, but over time people can develop their own methods, and the necessary procedures get lost. Please - I am not implying anything - I am working blind and trying to identify scenarios that can cause what you are experiencing. The computer has been eliminated, now the procedure must be looked at under a microscope.
     
  10. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    The files we have are both shared and created personally. We do not have any protocol/restrictions re changing the original format. The files are ours to adjust etc to match our clients needs. In the first instance, I would obtain an workbook that has been created and I would adjust the formatting, maybe some of the info required and save it. When I refresh, the data, structure etc is okay, but in some cases, only a part of the formatting was picked up (never has all the format been realized). For one file, it seemed each time, a bit more was "remembered" and some parts of the formatting just never has been retained. I thought it was the sequencing of saving and refreshing, but in looking at other users of the same files, they could just make the format change (ie reduce the decimals) and refresh without even saving and the format was updated. If there were "protected cells", then I think that would make sense that my format would not be accepted. But this did not apply to other users of the same files.

    I have also created brand new queries from the cube and the same thing happens. I make my selections from the pivot table, set up my columns, do my formatting and refresh and not all the format is retained each time I refresh. I need to go back and redo it and save it. The content - ie structure, data etc is okay, it is just the formatting that is impacted. For a couple of my files, it is not that just the format is omitted, it is actually changed. Instead of light shading, it shades with black and totally covers all the data.

    The IT guy does not see it as a excel program problem. As no other analysts have this issue, the only thing I can think of, is my workbook template. I did look at the default selections, but I am not sure which of the items would impact "saving formats when refreshing".

    Thanks for your help.
     
  11. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    MessagesforDebi: So sorry this sounds and feels and is so redundant for you. I do IT work, and like this one, I get surprised by issues that are not normally ones IT would take the reins for. In these cases, we need exact answers to exact questions. The steps can get very tedious. (Imagine "Bill is a co-worker)
    If you and "Bill" compare items under File>Options - are they identical? Especially add-Ins.
    Are Data Connections, especially from Data > Existing Connections identical?
    If you and "Bill" create exactly the same query, and send it to each other, does it run perfectly for you, and with the problem for "Bill" before it is sent, and after it is received?
    If you and "Bill change seats, and do it again (without Logging off and on) same question?
    If it fails, and you both stay at the other computers, and log off then on, and try again - what then>
    On your replaced computer, if you look at My Computer > Local Disk C > Users (or Documents and Settings) does it show the user name of you and your IT person, or also other IDs (That would indicate the computer was not reloaded with the OS and software.)
    What exactly are some formats that are being lost? What requested format > what does it become ?
    I am hoping that if you choose to answer some of these questions, a few of the really superior techs here will recognize something and jump up with the answer for you.
    Thank you so much for your time and patience.
     
  12. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    Thanks for your help - I will go through the steps - should have some time today. Thanks again and will let you know the outcome.
     
  13. MessagesforDebi

    MessagesforDebi Thread Starter

    Joined:
    Jan 18, 2013
    Messages:
    8
    I have booked some time with a coworker tomorrow, so hopefully, we will get some answers for you. Thanks for waiting and don't forget about me.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Formatting retained refreshing
  1. bj nick
    Replies:
    8
    Views:
    802
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085801

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice