Cheque Issue Register Format In Excel

0 views

Skip to first unread message

Nicandro Aaronson

unread,

Aug 3, 2024, 12:04:56 PM8/3/24

to awsindema

We are running Office 365 Pro Plus and the SAS Add-in 7.1 for Microsoft Office (32-bit). We have had a few reports from users where the SAS tab does not appear in Excel. It shows up in other Office applications, but not Excel. If we go into the disabled add-ins and try to re-enable the add-in, it just will not re-enable.

Re-installing the SAS add-in does not fix the problem. Reinstalling Office will bring the SAS tab back in Excel, but it ends up getting disabled again, usually very quickly, and we are back into the same boat. At this point, I am not sure if this is a SAS issue or an Office issue, so I am pursuing both avenues.

I thought I'd send you a checklist of some things that I've tried before that have helped get the add-in loaded. Some you may have already tried, some may be things that you can try. Hopefully one of them gets it working for you.

3. If it appears in the Disabled list, go to the "Manage" combo box at the bottom and choose Disabled Items, then click Go... Click on the SAS Add-In in the list of items to re-enable, and then Close.

5. If it does NOT load, go back to File->Options->add-ins->COM addins->Go... In this dialog if you select the SAS Add-in, there will be a load behavior at the bottom. If there was an error during loading, it will put it here. This will let us know if Excel is at least trying.

4. Run: SwitcherUtility reset // This will reset information in the current user portion of the registry. You want to do this as the user who is running Office, so that user's registry settings are reset, not the administrators.

D. Are you in a virtualized environment? I've seen cases where each time a user logs in, their add-in gets disabled. This was because their registry was getting "reset" each time they logged in, and the values that were being reset had the add-in disabled. It doesn't sound like this is your case, but if it is, it's something to talk about with your system administrator.

4. This is essentially using the vsto file to register the add-in instead of our own process. I don't usually suggest this, but when nothing else seems to be working to get the add-in enabled, this will often give a more helpful reason for the failure.

The link that you posted seems to be an old article as my version of Excel does not have a Tools menu. However, if I go to File > Options > Add-ins, I can see that there are no disabled application add-ins. If I drop down the Manage box and go to COM Add-ins, the SAS Add-in 7.1 for Microsoft Office checkbox is cleared. Unfortunately, if I check that box and click the OK button, it does nothing. The add-in will not re-enable. This is what I meant in my original post. Sorry if that was not clear.

Thus far, the only way I have been able to re-enable the add-in is to do a full reinstall of Office. That was okay the first time, since I thought it might just be a one-off situation. But now that the problem has recurred for the same user on the same machine, obviously doing repeated reinstalls of Office is not going to cut it.

I have a theory based on my reading of other posts with the same issue. I think the reason that I cannot get the SAS add-in to load is that it was disabled. Initially, I did not realize the difference between disabled add-ins and those that just are not loaded. So I was going to the COM add-ins to try to load the SAS add-in, and I suspect it was not loading because it was disabled.

Unfortunately, before coming to that realization, I managed to make things worse. Now the SAS add-in does not appear at all in the list of COM add-ins. I think that I will reinstall SAS in order to get it back, and then if the issue persists I will verify whether or not the add-in is disabled before I try to load it.

I highly recommend trying the SwitcherUtility.exe tool (in your AMO installation directory; ex. C:\Program Files (x86)\SASHome\x86\SASAddinforMicrosoftOffice\7.1) to get the SAS Add-in for Microsoft Office loading again. (I'd certainly try that before re-installing.) It is typically used to switch between versions of the add-in (when you have more than one version installed), however it re-registers the add-in, which usually fixes up problems with it not appearing.

Here is a screenshot of the utility:

Thanks. I came across the Switcher utility while doing some reading this morning in the support forums. I did give it a try, but it did not make the add-in reappear. That said, I did not know about it re-registering the add-in, so that is helpful information. I have added the Switcher utility to my ever growing SAS notes file for help with future troubleshooting.

I think that my previous troubleshooting efforts were on the wrong track, and that I ended up making the issue worse, so I elected the remove and reinstall all the SAS components, just to get back to a clean slate. The SAS tab is now back and everything seems to be running fine. If the problem returns, like it did before, I will have more of an idea of what to look at, and will definitely try all the things that you mention before doing anything else.

We use SAS EG or SAS Studio to connect to a Enterprise Server so have no base sas on local Windows PC. Before we got Office 365, it was an available option to add in that com and could see the SAS menu for the add-in.
What I'm trying to figure out is how to help users and administrators to know what the next steps are!

1. Run the SwitcherUtility.exe from the add-in's installation directory. This will set up the registry so that Excel can find the add-in. If you look at item "C" from the accepted solution on this discussion, it lists some arguments that you can pass in that will help make sure that the registry gets updated properly.

2. Re-install the add-in. Perhaps some files are missing or the registry just needs to be wiped clean and then re-registered when you do the install. Hopefully this isn't needed, step #1 should be able to fix most registry issues.

Other things you may need to consider is if you installed the proper bitness of the add-in. It must match the bitness of Office, not the bitness of the OS. Also, you have to be running the desktop version of Excel, not the web version. The web version doesn't support COM add-ins.

In Excel, I have highlighted the column and selected Format Cells.... In this dialog box, I have selected the Date, selected English (United States) as the locale and chosen the matching date format from the list. I hit OK and try to sort the data by this column.

The problem: Excel does not want to recognize dates as dates, even though through "Format cells - Number - Custom" you are explicitly trying to tell it these are dates by "mm/dd/yyyy". As you know; when excel has recognized something as a date, it further stores this as a number - such as "41004" but displays as date according to format you specify. To add to confusion excel may convert only part of your dates such as 08/04/2009, but leave other e.g. 07/28/2009 unconverted.

Alternative 0 : use LibreOffice Calc. Upon pasting data from Patrick's post choose Paste Special (Ctrl+Shift+V) and choose Unformatted Text. This will open "Import Text" dialog box. Character set remains Unicode but for language choose English(USA); you should also check the box "Detect special numbers". Your dates immediately appear in the default US format and are date-sortable. If you wish the special US format MM/DD/YYYY you need to specify this once through "format Cells" - either before or after pasting.

One might say - Excel should have recognised dates as soon as I told it via "Cell Format" and I couldn't agree more. Unfortunately it is only through step 1 from above that I have been able to make Excel recognize these text strings as dates. Obviously if you do this a lot it is pain in the neck and you might put together a visual basic routine that would do this for you at a push of a button. It can be as simple as this VBA code in Excel:

Update on leading apostrophe after pasting: You can see in the formula bar that in the cell where date was not recognised there is a leading apostrophe. That means in the cell formatted as a number (or a date) there is a text string that program thinks - you want to preserve as a text string. You could say - the leading apostrophe prevents the spreadsheet to recognise the number. You need to know to look in formula bar for this - because the spreadsheet simply displays what looks like a left-aligned number. To deal with this, select the column you want to correct, choose in menu Data Text to Columns and click OK. Sometimes you will be able to specify the data type, but if you have previously set the format of the column to be your particular data type - you will not need it. The command is really meant to split a text column in two or more using a delimiter, but it works like a charm for this problem too. I have tested it in Libreoffice, but there is the same menu item in Excel too.