Special insert for C # vsto Excel

Question

I am working on a C # vsto Excel application.

Whenever a user inserts something into an excel template from another excel sheet, he also inserts the cell format along with the cell data in the excel template. I want to avoid this. So I googled, and I came across the term "pasta".

Paste special will only paste content and will not change the format of the current sheet.

I want to enter insert special option in my vsto application.

I have a code here,

Application.OnKey("^v", "PasteSpecV"); 

but not working ... can someone help me with this?

Answer 1

• Download dll From http://globalmousekeyhook.codeplex.com/

• Add MouseKeyboardActivityMonitor.dll link

    private KeyboardHookListener k_keyListener; private void ThisWorkbook_Startup(object sender, System.EventArgs e) { k_keyListener = new KeyboardHookListener(new AppHooker()); k_keyListener.Enabled = true; k_keyListener.KeyDown += new KeyEventHandler(k_keyListener_KeyDown); } void k_keyListener_KeyDown(object sender, KeyEventArgs e) { if (Control.ModifierKeys == Keys.Control) if (e.KeyCode == Keys.V) { Worksheet actSht = ActiveSheet as Worksheet; Range rng = actSht.Application.Selection as Range; if (MessageBox.Show("You are about to paste values only. Do you want to continue?", "Paste Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { rng.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); } e.Handled = true; } } 

Answer 2

After many search methods and attempts and errors, I finally managed to make a "Paste Special" . The sheet I'm working on, I have Delacred as a Static Worksheet in the commonData strong class >

 class CommonData { public static Worksheet DATASHEET; } 

after that i used this worksheet in ThisWorkbook.cs

At the start of ThisWorkbook, I replaced PASTE (^ v) with the VBA function Paste_cell

  private void ThisWorkbook_Startup(object sender, System.EventArgs e) { // replacing paste by macro function Paste_cell CommonData.DATASHEET.Application.OnKey("^v", "Paste_cell"); } 

Open the excel sheet you are working on, press ALT + F11 , i.e. VBA macro editor.

Tools → Macros → Create a new macro, It will create module 1 in Project Explorer, Paste the following code into module1

 Sub Paste_cell() If MsgBox("You are about to Paste only Values and not the format, proceed?", vbQuestion + vbOKCancel, GSAPPNAME) = vbOK Then On Error Resume Next ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If End Sub 

Now, if you copy any value from any excel sheet, it will only insert the cell data and will not paste its Format.It will trigger the following message to alert the user. Therefore, the original format will not change.

Greetings :-)

Answer 3

I used the following code to copy and paste only values ​​and formatting using PasteSpecial in VSTO 2010

  Excel.Worksheet lastSheet = Application.ActiveSheet; Excel.Workbook wb = Application.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); for (int i = this.Worksheets.Count; i >= 1; i--) { Excel.Worksheet source = this.Worksheets[i]; source.Activate(); Application.Cells.Select(); Application.Selection.Copy(); Excel.Worksheet sheet = wb.Worksheets.Add(); sheet.Activate(); Application.Selection.PasteSpecial(Excel.XlPasteType.xlPasteValues); Application.Selection.PasteSpecial(Excel.XlPasteType.xlPasteFormats); sheet.Name = source.Name; sheet.Range["A1"].Select(); Clipboard.Clear(); } lastSheet.Activate(); lastSheet.Range["A1"].Select(); wb.SaveAs(fileName); wb.Close(); 

The line Clipboard.Clear () is the same as VBA CutCopyMode = False to deselect which was selected for copying.