BlackBar

Hallo. I'm writing AddIn for Excel 2007 in C#, using VSTO 2005 SE. I'd like to have possibility to use something similar to templates - specially saved Excel objects (for example: a pattern of the table) for further use.
For example: I'll create an Excel table which represents information about person (Name, Surname etc.) and I'd like to paste this table in a Worksheet, allow user to fill it and then commit data to Database.
How can I do this without writing any code which will creates this table at runtime.



Re: Visual Studio Tools for Office How to save Excel object for further use?

Dennis Wallentin

Hi,

How can I do this without writing any code which will creates this table at runtime.

We can add data to a worksheet but we cannot update a database without writing some code (at least that I'm not aware of).
Why is it important to avoid coding






Re: Visual Studio Tools for Office How to save Excel object for further use?

BlackBar

I don't want to avoid coding at all. I don't want to write code, which will build this template object at runtime. I want to build it in Excel my self and then simply "paste" it in a Worksheet where it is necessary, as if it was copied from another document.





Re: Visual Studio Tools for Office How to save Excel object for further use?

Dennis Wallentin

HI BB,

What You can consider to do is to create an Excel template (file extension XLT) and whenever it's call from the add-in a copy of it will be generate. This template can be set up so it will call the db and retrieve the wanted data. However, updating the db can only be done by using code which should also include a check so no duplicates will occur in the db.






Re: Visual Studio Tools for Office How to save Excel object for further use?

BlackBar

Hi, Dennis.
Can you please give me an example of your solution (code sample). Actually, I don't understand how can I use this Excel template from my AddIn.



Re: Visual Studio Tools for Office How to save Excel object for further use?

Dennis Wallentin

Hi again,

No need to apologize for Your English I'm also a non-English person.

# 1The following link provide info on how to create templates (it's for 2000 but it's also valid for 2003 and 2007.
However in 2007 the file extension is xltx for templates: Create and maintain Excel templates


# 2 Create a data binding in the worksheet

1. Select the Data tab in Excel 2007

2. Choose one of the options for Get External Data

3. Save the file with the file extension xltx

# 3 VB code (I'm not good with C#) for creating a copy of the template:

Sub Open_Template()
Globals.ThisAddIn.Application.Workbooks.Open("c:\MyTemplate")
MessageBox.Show("A copy of MyTemplate has been created!")
End Sub






Re: Visual Studio Tools for Office How to save Excel object for further use?

BlackBar

Hi!
I tried to use your advise, but without results...


Code Snippet
try
{ Globals.ThisAddIn.Application.Workbooks.Open("C:\\Temp1.xltx", false, false, false, false, false, true, true, false, true, true, false, false, true, false);
}
catch (Exception e)
{
throw;
}

This code throws an exception:
{System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)
at MyExcelAddin.UI.lb3_click(IRibbonControl control) in C:\Documents and Settings\borissv\My Documents\Visual Studio 2005\Projects\MyExcelAddin\MyExcelAddin\UI.cs:line 363}

As I understand, this code should open new workbook with my template in it ! But unfortunately this isn't that what I need.
I need to draw this template in Active Sheet starting from Active Cell.
I tried to backup Clipboard and save it to file, and then restore it when it is necessary. I have made it, but the data in the clipboard doesn't contain formating information. So when I paste restored data from clipboard I paste only cell values...





Re: Visual Studio Tools for Office How to save Excel object for further use?

Dennis Wallentin

Hi BB,

It looks like a mistyping:

"C:\\Temp1.xltx",

should be "C:\Temp1.xltx"


But unfortunately this isn't that what I need.
I need to draw this template in Active Sheet starting from Active Cell.

Could You develop it further Do You need to copy a certain range (of cells) to be inserted in a certain range in the active sheet






Re: Visual Studio Tools for Office How to save Excel object for further use?

Cindy Meister

Hi Dennis

In C# the backslashes must be duplicated (or a string with backslashes has to be preceded by the @ sign). The backslash serves in C# as an "escape character": it signals that the following character should not be interpreted literally, but as a special instruction. \n for example generates a new line (Chr(10)); \r a carriage return (Chr(13))

Dennis Wallentin wrote:

Hi BB,

It looks like a mistyping:

"C:\\Temp1.xltx",

should be "C:\Temp1.xltx"


But unfortunately this isn't that what I need.
I need to draw this template in Active Sheet starting from Active Cell.

Could You develop it further Do You need to copy a certain range (of cells) to be inserted in a certain range in the active sheet






Re: Visual Studio Tools for Office How to save Excel object for further use?

Cindy Meister

BlackBar:

The error message you show indicates you may have the incorrect syntax for the Workbooks.Open method (the syntax for a different version, for example) or there may be a problem with the languages of Windows, VSTO and Excel not being in synch. See this Knowledge Base article

http://support.microsoft.com/kb/320369/en-us

BlackBar wrote:
Hi!
I tried to use your advise, but without results...


Code Snippet
try
{ Globals.ThisAddIn.Application.Workbooks.Open("C:\\Temp1.xltx", false, false, false, false, false, true, true, false, true, true, false, false, true, false);
}
catch (Exception e)
{
throw;
}

This code throws an exception:
{System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)
at MyExcelAddin.UI.lb3_click(IRibbonControl control) in C:\Documents and Settings\borissv\My Documents\Visual Studio 2005\Projects\MyExcelAddin\MyExcelAddin\UI.cs:line 363}

As I understand, this code should open new workbook with my template in it ! But unfortunately this isn't that what I need.
I need to draw this template in Active Sheet starting from Active Cell.
I tried to backup Clipboard and save it to file, and then restore it when it is necessary. I have made it, but the data in the clipboard doesn't contain formating information. So when I paste restored data from clipboard I paste only cell values...





Re: Visual Studio Tools for Office How to save Excel object for further use?

Dennis Wallentin

Cindy,

Thanks for the C# -lesson It's look like I, sooner or later, will be forced to take up my classes in C# in Delphi .






Re: Visual Studio Tools for Office How to save Excel object for further use?

Cindy Meister

Dennis Wallentin wrote:

Thanks for the C# -lesson It's look like I, sooner or later, will be forced to take up my classes in C# in Delphi .

heh-heh. Let me know when you start the Delphi classes and I'll join you!






Re: Visual Studio Tools for Office How to save Excel object for further use?

BlackBar

Dennis Wallentin wrote:

Could You develop it further Do You need to copy a certain range (of cells) to be inserted in a certain range in the active sheet



Not just copy but save Range of cells with all formating, validation, cell values... And restore this range and put it when it is necessary in a worksheet.





Re: Visual Studio Tools for Office How to save Excel object for further use?

Dennis Wallentin

Hi BB,

If I understand it correctly then the best approach would be to use a standard workbook (that can only contain 1 worksheet). This file can either be created at design time or at run time. It can store the wanted formattings, values etc and when needed copy & paste between it and other worksheets. It's alos possible to run it in hidden mode. Let us know what You need help with.

Hi Cindy,

It will be in plain Swedish







Re: Visual Studio Tools for Office How to save Excel object for further use?

BlackBar

Hi!

Can you provide an example of opening this template at runtime and coping it on active sheet !

Thank you! Smile