Malick Mbaye's Blog

Cut an Paste Rows in Excel using Vsto C#

I bumped into this problem as I was trying to develop a Excel 2007 Addin, how to cut and paste a row line in Excel using Vsto ?
Like most vsto developer i usualy record a macro of doing what i wanna do and translate the vba  code to c#. This time the vba code was :

Rows(“13:13″).Select
Application.CutCopyMode = False
Selection.Copy
Rows(“14:14″).Select
ActiveSheet.Paste
Range(“B14″).Select

I tryed to translate it but i could’n get an object Selection with Copy() methode. After googling for hours i found  this.

Object template = Settings.Default.TemplatePath;
Workbook wb = Globals.ThisWorkbook.Application.Workbooks.Add(template);
Range r = (Range)wb.Application.ActiveCell[1, "A"];
const int xlShiftDown = -4121;
Range oRow = r.EntireRow;
oRow.Insert(xlShiftDown, XlInsertFormatOrigin.xlFormatFromRightOrBelow);

It works but nothow i wannt it to. Because the inserted line doesnt come the formulas from the original line. I finaly got the solution by doing this :

Object template = Settings.Default.TemplatePath;
Workbook wb = Globals.ThisWorkbook.Application.Workbooks.Add(template);
Range r = (Range)wb.Application.ActiveCell[1, "A"];
Range oRow = r.EntireRow;
oRow.Select();
object missing = Missing.Value;
oRow.Copy(missing);
Range rg = (Range)rg[2, "A"];
rg.Select();
rg.PasteSpecial(XlPasteType.xlPasteAll,
XlPasteSpecialOperation.xlPasteSpecialOperationNone,missing,missing);
wb.Application.CutCopyMode = XlCutCopyMode.xlCopy;

Thanks For your reading, i hope this will help you.

Set the focus on the generated word document (Windows(“Doc1.docx”).Activate)

Problem : I use showDialog() in my addin to call a winform witch generate a word document.So after creating my new word document  it is generated under the curent document that called the addin.
Solution :
The code generated with office 2007′s macro is :

Windows(“Doc1.docx”).Activate

The code i use to create the document is :

//declare a System.Reflection.Missing objet
Object M = Missing.Value;
// refere the the .dotx template
Object nomModele = Properties.Settings.Default.NomModel;
Word.Document _doc = Globals.ThisAddIn.Application.Documents.Add(ref nomModele, ref M, ref M, ref M);
//put the focus on the document
_doc.Activate();

This works only if i use the show() method of my winform insted of the ShowDialog(). If you wanna use the ShowDialog() you gotta put the following code in your ThisAddIn.cs to force window to activate the document.

public static bool flag = false;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
((Word.ApplicationEvents4_Event)this.Application).WindowActivate += new Microsoft.Office.Interop.Word.ApplicationEvents4_WindowActivateEventHandler(ThisAddIn_WindowActivate);
}
void ThisAddIn_WindowActivate(Microsoft.Office.Interop.Word.Document Doc, Microsoft.Office.Interop.Word.Window Wn)
{
if (flag)
{
object n = 1;
//the generated document is always 1
this.Application.Documents.get_Item(ref n).Activate();
}
flag = false;
}

so from now if you wanna create a document you activate it and set the bool flag to true.

Object M = Missing.Value;
// refere the the .dotx template
Object nomModele = Properties.Settings.Default.NomModel;
Word.Document _doc = Globals.ThisAddIn.Application.Documents.Add(ref nomModele, ref M, ref M, ref M);
//put the focus on the document
_doc.Activate();
//Set the flag to true
ThisAddIn.flag = true;

Add an empty line to a combobox

To add an empty line in a combobox after databinding it  you need to set the property SelectedIndex to -1 on the FormLoad event. The value -1 is the default selection of a ComboBox, and is the value you set if you want to clear a selection.

Linq to SQL Like Operator

This a simple way to use like expression in Linq to sql

var query = from c in ctx.Customers

where SqlMethods.Like(c.City, “L_n%”)

select c;

Thanks to Guy Burstein I  got this from  his blog

Call a document or application From winForm

System.Diagnostics.Process.Start(“DocsName.doc or.exe”);

Change Font style in a word bookmark

//declare your .dot or dotx file
Object nomModele = “c:\\ your file path”;
//declare a missing value
Object M = Missing.Value;
//declare your bookmark
Object Frais= “yourBookMarkname”;
//initiate your document
Microsoft.Office.Interop.Word.Document _doc =
Globals.ThisAddIn.Application.Documents.Add(ref nomModele, ref M, ref M, ref M);
//you have to initiate a range in order to talk to your book mark
Microsoft.Office.Interop.Word.Range rng2 = _doc.Bookmarks.get_Item(ref Frais).Range;
//the important part is here first you write your text
rng2.Text = “R”;
//then you set your font style  an so on …
rng2.Font.Name = “Wingdings 2″;

DataSet to Excel

hey all

This is my first post  i’m gonna try to explain how to generate a microsoft excel file from a dataset.