Re: exception trying to insert a range to an Excelsheet - C# Discussion Boards

Re: Mifare RFID reader with GNET Plus protocol

Muhammad Yousuf Maher16-Jul-14 3:04

Muhammad Yousuf Maher

16-Jul-14 3:04

Hi GerritdL,

Any success with the C# program. I am stuck with PCR310U and not able to get UID.

Regards,

exception trying to insert a range to an Excelsheet

Harry6662-Jul-09 1:23

Harry666

2-Jul-09 1:23

Hello there,

I am programming C# using MS Visual Studio Professional Edition 2008 and MS Office Excel 2007.
My Problem is that I want to mark a range, copy and insert it at the same position shifting existing cells down. I can do this with the Excel GUI - no problem. But programmatically I get a System.Runtime.InteropServices.COMException, telling me that the Insert-method of the Range-object could not be executed (German text:"Die Insert Methode des Range-Objektes konnte nicht ausgeführt werden.").

The range I want to copy has got some special formats (font, color, some merged cells), and I need these for each entry I have to insert.

I am trying this code
<pre>
Excel.Worksheet sheet = (Excel.Worksheet)wb.Sheets["mySheet"]; // wb is the actual workbook
Excel.Range range = sheet.get_Range(sheet.Cells[16, 1], sheet.Cells[18, 37]);// this is "A16:AK18"
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, range);
</pre>

Here come the questions:
Why can I not insert the range? What are possible reasons?
How can I insert the desired range?

Thanks in anticipation!
Harry

no plan,
no signature

Re: exception trying to insert a range to an Excelsheet

padmanabhan N2-Jul-09 1:44

padmanabhan N

2-Jul-09 1:44

Harry666 wrote:
Excel.Range range = sheet.get_Range(sheet.Cells[16, 1], sheet.Cells[18, 37]);// this is "A16:AK18"

you can directly use like this:

Excel.Range range = sheet.get_Range("A" +16, "AK" +18);// this is "A16:AK18"

Padmanabhan
My Articles:
Articles[^]
My latest Article:
Word Automation[^]

Re: exception trying to insert a range to an Excelsheet

Harry6662-Jul-09 5:04

Harry666

2-Jul-09 5:04

Is it good for any purpose to use the letters instead of numbers,
besides getting the range out of a string instead of the Cells[] Array?
I'm asking, because I have to count and calculate with the rows - well, not the columns... Sniff | :^)

I want to say: in both cases I use the get_Range()-method. Is there any difference in the range which I get using another param?

Harry

no plan,
no signature

Re: exception trying to insert a range to an Excelsheet

MumbleB2-Jul-09 1:47

MumbleB

2-Jul-09 1:47

I think you have to select the range first. so, before the range.Insert try and insert the following statement to selct the range that you just specified range.Select(). Hope this helps, if not let us know.

Excellence is doing ordinary things extraordinarily well.

Re: exception trying to insert a range to an Excelsheet

MumbleB2-Jul-09 1:57

MumbleB

2-Jul-09 1:57

I just tested the below code using VS2005 and it works fine.

objRange = objSheet.get_Range("A16", "AK18");
objRange.Select();
objRange.Insert(XlInsertShiftDirection.xlShiftDown, objRange);

Excellence is doing ordinary things extraordinarily well.

Re: exception trying to insert a range to an Excelsheet

Harry6662-Jul-09 3:33

Harry666

2-Jul-09 3:33

Thanks for the reply!

Yes, I tried to select my range. Actually, I did select it, but the insert still throws that Exception.
Btw: I read an article (Sorry, I forgot where that was), where the author wrote and explained why You do not need to select (or activate) a range and tell the program what to do with "ActualRange"... instead of
myRange.Select()
ActualRange.doSomething()
it would be the same (even shorter) to do like this:
myRange.doSomething()

Again, it is not the Select() that misses. I think that it's got s.th. to do with protection and rights or so.

Sigh | :sigh:

Harry

no plan,
no signature

Re: exception trying to insert a range to an Excelsheet

Harry6662-Jul-09 4:00

Harry666

2-Jul-09 4:00

Partial success...

I've got several worksheets in my workbook. In some of them I have to do this: mark, copy and insert a special range of the sheet into the same sheet.

I tried to...

Excel.Worksheet sheetKL = (Excel.Worksheet)myWorkbook.Sheets["KL"];
Excel.Range range = ((Excel.Range)sheetKL.Cells[10, 1]).EntireRow;
range.Select();

and the Select-method throws an Exception

after this I tried to...

Excel.Worksheet sheetKL = (Excel.Worksheet)myWorkbook.Sheets["KL"];
sheetKL.Select(true); //select the sheet
Excel.Range range = ((Excel.Range)sheetKL.Cells[10, 1]).EntireRow; //create the range
range.Select(); //select the range
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, range);

but then, still, the Insert-method throws the Exception.

WTF | :WTF:

Harry

no plan,
no signature

Re: exception trying to insert a range to an Excelsheet

MumbleB2-Jul-09 9:22

MumbleB

2-Jul-09 9:22

Sorry about that Harry. I am home in bed now and will definately look into this in the morning. I have had a tough time with Excel when I wrote my first Excel app but got it to work just fine. I'll see what I can do with it and post back.

Excellence is doing ordinary things extraordinarily well.

Re: exception trying to insert a range to an Excelsheet

Harry6662-Jul-09 13:58

Harry666

2-Jul-09 13:58

my fault...
there was a save/close thing before which I did not correct... D'Oh! | :doh:

:°)

But still I don't get what I want.
This is: mark and copy and entire line, and insert it at the same line. (like you can do this using the excel-gui)

but unfortunately with...

range = sheet.get_Range("A"+10, "Y"+10).EntireRow;
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, range);
still doesn't work, whether I put
range.Select() // or even
range.Activate() // befor the insert

...I do not get what I want. Confused | :confused:

and -it's really late now - there is this f---king exception back again
Dead | X|

no plan,
no signature

Re: exception trying to insert a range to an Excelsheet

MumbleB2-Jul-09 22:14

MumbleB

2-Jul-09 22:14

Here you go mate. Excel programming is a major pain. Believe you me. Below the code that you want to use. The Origin is what is missing. The best way of getting things in perspective is to write a MACRO in Excel and check the code behind it. That should always give you a good idea of what you need to do.

objRange = objSheet.get_Range("A6", "AK8");
objRange.Select();
objRange.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

Excellence is doing ordinary things extraordinarily well.

Re: exception trying to insert a range to an Excelsheet

Harry6664-Jul-09 0:57

Harry666

4-Jul-09 0:57

Thanks a lot! Thumbs Up | :thumbsup:

I did not quite unserstand what's meant with the origin. I thought it must be the range itself - when I want to copy and insert it again.
I still don't know exactly what this means:
XlInsertFormatOrigin.xlFormatFromLeftOrAbove
... I think I can get along with that Smile | :)

At least, the exception does no more appear Wink | ;)

btw: I'm still not sure whether I really need to Select() the range or not, because I'm calling it by its name... but thank you anyway!

Cheers
Harry

no plan,
no signature

still trying to insert a range to an Excelsheet

Harry6664-Jul-09 4:14

Harry666

4-Jul-09 4:14

Here I am again,

trying to copy and insert my excel-cells... Mad | :mad:

Well, what I want to copy is the style, the fonts, size, colors, the way they are merged, borders (boxes) around them, and so on. I do NOT need the content of these rows, I DO need the shape, format and so on.
Using the GUI, the Excel Application itself, I mark the row (with the mouse), copy and paste (or insert) it.
For this reason I think, doing so must be correct:

Excel.Range range = mySheet.getRange(("A"+10, "Z"+10)).EntireRow;   //grab the line
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //copy and paste (-> insert)

or afaik even ...
Excel.Range range2 = mySheet.getRange(("A"+11, "Z"+11)).EntireRow;   //mark the next line
range.Copy(range2); //instead of insert
... should do almost the same.

In fact, no. It does not. It's only copying the cell's contents (or   OMG | :OMG:

sometimes the color... WTF | :WTF:

)
So, is there anything else to care about, when I do not need the contents of the cells but rather their lookalikes (like font, color, cellheight,width,...)?
...And what is it?

Thanks in anticipation
Harry

no plan,
no signature

Re: still trying to insert a range to an Excelsheet

MumbleB4-Jul-09 8:31

MumbleB

4-Jul-09 8:31

Hi Harry. If you tried the method I suggested previously, record the steps in a Macro in Excel it would have shown you exactly what you need to do. See below from Excel Macro.

Range("A1:E11").Select<br />
Selection.Copy<br />
Range("A16").Select<br />
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _<br />
    SkipBlanks:=False, Transpose:=False<br />

Now, this translates to the below in C#. VBA is not so difficult to understand if you make a little effort.

//Select Range to Copy
objRange = objSheet.get_Range("A4", "C14");
//Activate Selection
objRange.Select();
//Copy Selection
objRange.Copy();
//Select the Starting cell to copy to
Microsoft.Office.Interop.Excel.Range myCell1 = objSheet.get_Range("A16",
    System.Reflection.Missing.Value);
//Activate the Cell
myCell1.Activate();
//Paste with the formats
objRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
    Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
    false, false);

Next time please try using the Macros in Excel. They are good place to start as a guide.

Note, I have not tested the code but it should work.

Happy coding!!

Excellence is doing ordinary things extraordinarily well.

Re: still trying to insert a range to an Excelsheet

Harry6665-Jul-09 4:38

Harry666

5-Jul-09 4:38

Thanks,

...but this was not of help Frown | :(

I already tried to record a makro, but with insert it just looked like...
Rows("10:10").Select
      Selection.Copy
      Selection.Insert Shift:=xlDown

Using Copy+paste it looked like...
Rows("10:10").Select
      Application.CutCopyMode = False
      Selection.Copy
      Rows("11:11").Select
      ActiveSheet.Paste

There is no PasteSpecial or such. (I use MS Office Excel 2007)
And the method Range.Copy() does have a parameter (Object Destination)
but I tried that as well and I get only to copy the contents.
Btw: the PasteSpecial()-method threw an exception while using xlPasteFormats,
telling me my ranges do not fit. Well, I know that they don't fit. I want the second row to like like the first, I want to make them fit.

My C# code looks like this
Excel.Range range = sheet.get_Range("A" + 10 , "Y" + 10 ).EntireRow;
range.Select();
range.Copy(Type.Missing);
range2 = sheetKL.get_Range("A" + 11 + i, "Y" + 11 + i).EntireRow;
range2.Activate();
range.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false, false); // I still don't understand the last param, I tried all combinations of true/false
Frown | :(

nbsp;

Programming Excel is no fun. I think I'll try using a makro...
Harry

no plan,
no signature

Using a makro

Harry6665-Jul-09 6:04

Harry666

5-Jul-09 6:04

Muchas gracias hombre! Thumbs Up | :thumbsup:

You gave me a good hint. This C#-code really made me mad! (I still don't know how to solve my problem in C#). But I told you that I'm gonna use a makro and call this by...

myWorkbook.Modules.Application.Run("AddLine", arg1, arg2, arg3, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,     Type.Missing, Type.Missing, Type.Missing);

and this is the simple makro:
Public Function AddLine(arg1 As String, arg2 As Integer, arg3 As Integer)
      Sheets(arg1).Select
      Rows(arg2 & ":" & arg3).Select
      Selection.Copy
      Selection.Insert Shift:=xlDown
End Function

so I'm able to select any worksheet with its name (arg1) and explicitly which rows to copy   Smile | :)

As a programmer I really would like to know how to do this in C#, but it took me a whole week and cost me my last hairs Sigh | :sigh:

and still the C#-code does not work well, but I'm quite fine with this makro-solution.
Now, I am relieved. Cool | :cool:

Thanks a lot! Rose | [Rose]

Harry

no plan,
no signature

Re: still trying to insert a range to an Excelsheet

MumbleB6-Jul-09 19:16

MumbleB

6-Jul-09 19:16

Hi Harry. I am happy you managed to find a solution by using a Macro. HOwever, the copy method I provided is correct and it works. I think I know why your method doesn't work. You needed to omit the following code and only select the starting cell where you want to start copying to.
1) you need to do the Insert to make the blank cells available.
2) Go to the sheet with the data that you want to copy.
3) Go back to the sheet you want to copy to and select the starting cell which I think from the above is "A11".
4) Now you do the paste special formats.

So, I would have written the code as below:
YOu have two past options, the first to copy only the formats and the other to copy both formats and data in the cells.

Below is to copy the data in the cells with the formats.

//Select the Range where you want to insert the data into
objRange = objSheet.get_Range("A6", "AK6");
objRange.Select();
//Insert Blank Line
objRange.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
//Now go and select the data with the formatting that you want to copy.
objRange = objSheet.get_Range("A10", "Y10");
objRange.Select();
objRange.Copy(Type.Missing);
//Go back and select the first cell where you want to copy the data to
objRange = objSheet.get_Range("A6", Type.Missing);
objRange.Activate();
objRange.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone,
    false, false);

If you only want to copy the formats of the cells then you change the XlPasteType.xlPasteAll to XlPasteType.xlPasteFormats

When I did my first Excel App I had the same issues like you, pulled my hair out for a whole week. But, like I said, using the Macros to get an idea of what the code should look like helps. as for the false, false, that is just for "skipblanks" and "transpose" which is set to false unless you want to do those oporations.

Anyway, give the above a try, it should work. I tested it this morning and it works 100%

Excellence is doing ordinary things extraordinarily well.

Re: still trying to insert a range to an Excelsheet

Harry6669-Jul-09 3:32

Harry666

9-Jul-09 3:32

Hello Kwagga,
thanks for reply and the still attempt to help Wink | ;)

I tried again what you posted above (both: xlPasteAll & xlPasteFormats)...
Well, it still does not fit. e.g. the height of the cells.
(did I miss or forget s.th.? - I don't know)
I know that I still could read and copy the height as well,
but the more important question - I think - would be:
Which of both is the quicker alternative?
* using the VB-Makro which is called out of my C#-code - which does well (it takes its time)
or
* using the Microsoft.Office.Interop.Excel thing - which doesn't do as I suppose it to do (but is quicker?)

I think I keep on using the makro

Greets and Thanks,
Harry

no plan,
no signature

Dowloading file, web aplication and C#

hoshyarD2-Jul-09 0:34

hoshyarD

2-Jul-09 0:34

How the clients can download files from my webserver?
I´m using C# and .net 2.0 in my webaplication.

can some wone help me with some tips

please!

Re: Dowloading file, web aplication and C#

Manas Bhardwaj2-Jul-09 0:36

Manas Bhardwaj

2-Jul-09 0:36

First of all this ASP.Net question and ASP.Net forum would have been a better place to ask.
In between, Google gave me all these answers[^].

Manas Bhardwaj
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Vista/Win7 Gadget's Flyout.

Supriya Tonape2-Jul-09 0:30

Supriya Tonape

2-Jul-09 0:30

Hello,
Is it possible to close Vista/Windows7 Gadget's Flyout through C# code ?
Best Regards,
Supriya.

Re: Vista/Win7 Gadget's Flyout.

musefan2-Jul-09 1:58

musefan

2-Jul-09 1:58

Do you mean the sidebar that you get on the desktop?

If so, then that is a process that you could end... There maybe other ways to access it through code but I don't know about that and I don't really feel like doing your work for you... on this day

Life goes very fast. Tomorrow, today is already yesterday.

Re: Vista/Win7 Gadget's Flyout.

Supriya Tonape2-Jul-09 6:38

Supriya Tonape

2-Jul-09 6:38

no not sidebar.. but I want to close flyout of Gadget. Some gadgets have flyouts.. for eg. sign in window of any messenger sort of gadget will open in Gagdet's flyout..

Index was out of range. Must be non-negative and less than the size of the collection

haleemasher2-Jul-09 0:22

haleemasher

2-Jul-09 0:22

i face an error Index was out of range. Must be non-negative and less than the size of the collection in this code plz help

cmd.CommandText = "DELETE FROM Coupon_Info WHERE RecievingDate='" + GridView1.DataKeys[e.RowIndex].Values[0].ToString() + "'";

Re: Index was out of range. Must be non-negative and less than the size of the collection

N a v a n e e t h2-Jul-09 0:28

N a v a n e e t h

2-Jul-09 0:28

haleemasher wrote:
GridView1.DataKeys[e.RowIndex].Values[0].ToString()

Before you access items from an array, make sure the index you are specifying is in between the array lower and upper limit. Eric Lippert has a post[^] which says Fix your code so that it never triggers a boneheaded exception – an "index out of range" exception should never happen in production code.

Also I'd suggest you to read about SQL injection attacks because your code is open to injection attacks.

Smile | :)

Navaneeth

How to use google | Ask smart questions

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.