XlDialogFormatNumber

Add-in Express™ Support Service
That's what is more important than anything else

XlDialogFormatNumber
Activating a cell in a hidden worksheet stored in an xlam 
Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last 
Lennaert Goris


Guest


Hi support,

For our application we would like to work with numberformats input by the user. Excel has a very good built-in dialog for this and frankly, I don't feel much for recreating such a complicated form. The problem however is that this dialog deposits it's results in the active cell. Using the currently active cell would not be a clean solution so I am trying to figure out how to change the active cell to a hidden sheet in an xlam file that is loaded by our addin. At the moment I'm stuck and can't seem to get a handle on how to accomplish this.

My current attempts: 

Excel._Worksheet tempSheet = GetTempSheet();
tempSheet.Activate();
Excel.Range tempCell = (Excel.Range)tempSheet.Cells[1, 1];
tempCell.Select();
Marshal.ReleaseComObject(tempCell);



Excel._Worksheet tempSheet = GetTempSheet();
tempSheet.Activate();
Excel.Range tempCell = (Excel.Range)tempSheet.Cells[1, 1];
tempCell.Activate();
Marshal.ReleaseComObject(tempCell);



Excel._Worksheet tempSheet = GetTempSheet();
tempSheet.Select();
Excel.Range tempCell = (Excel.Range)tempSheet.Cells[1, 1];
tempCell.Select();
Marshal.ReleaseComObject(tempCell);



Excel._Worksheet tempSheet = GetTempSheet();
tempSheet.Select();
Excel.Range tempCell = (Excel.Range)tempSheet.Cells[1, 1];
tempCell.Activate();
Marshal.ReleaseComObject(tempCell);


I think I'm not getting how this should be working. Or should I also activate the workbook?

Kind regards,
Posted 24 Jul, 2012 10:18:28 Top
Eugene Astafiev


Guest


Hi Lennaert,

Of course, you need to show the hidden worksheet to a user. How can I reproduce the issue with the xlam file? Do you develop an Add-in Express based add-in?

Finally, what Excel version do you use?
Posted 24 Jul, 2012 12:04:07 Top
Lennaert Goris


Guest


Hi Eugene,

I saved the file as an Excel Addin by setting it's IsAddin property in the VBA editor to True. I suspect that this action is the reason Excel does not let me activate the worksheet and that what I am trying to do is just not possible. Just wanted to know for sure if I'm right in my assumption.

In case I'm wrong, please read on for a more detailed explanation of what I'm doing.

The current solution implemented is to turn off Application.ScreenUpdating, create a new workbook (which automatically sets Application.ActiveSheet to the first sheet in the new workbook) and use the active cell as a transition point. After getting the NumberFormat I close the newly created workbook without saving.

While this works, it sounds like a whole lotta actions to perform to retrieve a simple number format. As it stands, we will be needing an xlam file as a connection point for our Undo functionality. I was wondering if it would be possible to use a cell on a hidden sheet in the xlam file for Excel to store the NumberFormat so I can store it in my own object.

So basically I'm asking "Is it possible to set the ActiveCell property to a specific cell in a worksheet in an xlam file".

I'm using Add-In-Express, version 6.7.3062.0, developing with Visual Studio 2010 against Office 2010.

I have created two variables:
Excel._Workbook XlamFile;
Excel._Worksheet TempSheetInXlamFile;

These are assigned in the AddinModule_AddinStartupComplete() event like so: 


        private void AddinModule_AddinStartupComplete(object sender, EventArgs e) {
            XlamFile= ExcelApp.Workbooks.Open(@"PathToFile.xlam");
            TempSheetInXlamFile= (Excel.Worksheet)XlamFile.Sheets["TempSheet"];
        }


Is it possible to convince Excel to set the ActiveCell property to [TempSheet!A1]? And if so, how would I go about doing that?
Posted 24 Jul, 2012 13:26:51 Top
Eugene Astafiev


Guest


Hi Lennaert,

I have just tried to create a new workbook and save it as a .xlam file. Then I closed Excel and run the just saved file from the hard drive. A new instance of Excel is displayed without any workbook opened. It looks like .xlam files don't contain any data related to worksheets.
Posted 25 Jul, 2012 10:58:09 Top
Nicholas Hebb


Guest


I saw this posting, and since I have a function that does something similar, I thought I'd share. It just uses whatever cell is active, but reverts it to its original formatting before returning. Hopefully the code is self explanatory. 



private String GetCellFormat()
{
  string oldFormat = Stri ng.Empty;
  string newFormat = Stri ng.Empty;
  try
  {
    Excel.Range range = ExcelApp.ActiveCell;
    if (range != null && range.NumberFormat != null)
    {
      oldFormat = range.NumberFormat.ToS tring();
      object m = Type.Missing;
      ExcelApp.Dialogs[Excel.XlBuiltInDialog.xlDialogFormatNumber].Show(m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m);
      if (range.NumberFormat != null)
        newFormat = range.NumberFormat.ToS tring();
      range.NumberFormat = oldFormat;

      //System.Diagnostics.Debug.WriteLine(Stri ng.Format("Old Format: {0}
New Format: {1}", oldFormat, newFormat));
      System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
    }
    else
    {
      // display message
    }
  }
  catch //(Exception ex)
  {
    // handle as needed
  }
  return newFormat;
}
Posted 26 Jul, 2012 00:33:33 Top
Nicholas Hebb


Guest


Note, the CODE block is inserting spaces in the word "String". I tried modifying it, but it added them back when I saved.
Posted 26 Jul, 2012 00:38:18 Top
Eugene Astafiev


Guest


Hi Nicholas,

Thank you for sharing your code for other forum readers!

FYI I have noticed that you don't release underlying COM objects properly. For example: 

ExcelApp.Dialogs[Excel.XlBuiltInDialog.xlDialogFormatNumber].Show(m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m, m);


The Dialogs property returns an instance of the Dialogs class which should be released after. Also please note that you get an instance of the Dialog class using the square brackets. You can read more about this in the http://www.add-in-express.com/creating-addins-blog/2008/10/30/releasing-office-objects-net/ article on our technical blog.
Posted 26 Jul, 2012 06:01:37 Top
Nicholas Hebb


Guest


Good catch - thanks.
Posted 26 Jul, 2012 06:45:45 Top
Lennaert Goris


Guest


Brilliant, thnx. This piece of code is so much cleaner then my initial solution. I'm a happy camper :). Nice piece of code, I think this is a very elegant solution.

I've added my final implementation to the post as I've made a few modifications to the code that I think can benefit others.

The only real difference is that I've moved the definition of all COM objects to the top of the function so they can be released in a finally block. This way, the COM objects get released properly even if the code fails. 


private string GetNumberFormat() {
    // Create all COM variables outside of the try/catch block so we can release in a finally  
    Excel.Dialog xlDialogFormatNumber = null;
    Excel.Range activeCell = null;
    string chosenNumberFormat = string.Empty;
    string restoreNumberFormat = string.Empty;

    try {
        activeCell = AddinModule.CurrentInstance.ExcelApp.ActiveCell;
        if (activeCell != null && activeCell.NumberFormat != null) {
            restoreNumberFormat = activeCell.NumberFormat.ToS tring();

            xlDialogFormatNumber = ExcelApp.Dialogs[Excel.XlBuiltInDialog.xlDialogFormatNumber];
            xlDialogFormatNumber.Show();

            if (activeCell.NumberFormat != null) {
                chosenNumberFormat = activeCell.NumberFormat.ToS tring();
            }

            activeCell.NumberFormat = restoreNumberFormat;
        }
    } catch {
        // display message
    } finally {
        if (xlDialogFormatNumber != null) {
            Marshal.ReleaseComObject(xlDialogFormatNumber);
        }

        if (activeCell != null) {
            Marshal.ReleaseComObject(activeCell);
        }
    }

    return chosenNumberFormat;
}


Note that the ToS tring() methods have an extra space. It seems to be related to the word S tring (with a capital S).
Posted 26 Jul, 2012 07:00:22 Top
Eugene Astafiev


Guest


Hi Lennaert,

The common mistake in programming Office COM add-ins is: 

xlDialogFormatNumber = ExcelApp.Dialogs[Excel.XlBuiltInDialog.xlDialogFormatNumber];


You don't release underlying COM objects too. The Dialogs property returns an instance of the Dialogs class which should be released after. Please read more about this in the http://www.add-in-express.com/creating-addins-blog/2008/10/30/releasing-office-objects-net/ article.
Posted 26 Jul, 2012 07:20:34 Top

Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last