|
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
|
|
|
|
|
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"
|
|
|
|
|
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...
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
Harry
no plan,
no signature
|
|
|
|
|
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.
Harry
no plan,
no signature
|
|
|
|
|
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.
|
|
|
|
|
|
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.
|
|
|
|
|
|
Here I am again,
trying to copy and insert my excel-cells...
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 sometimes the color... )
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
|
|
|
|
|
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.
objRange = objSheet.get_Range("A4", "C14");
objRange.Select();
objRange.Copy();
Microsoft.Office.Interop.Excel.Range myCell1 = objSheet.get_Range("A16",
System.Reflection.Missing.Value);
myCell1.Activate();
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.
|
|
|
|
|
Thanks,
...but this was not of help
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
nbsp; nbsp;
Programming Excel is no fun. I think I'll try using a makro...
Harry
no plan,
no signature
|
|
|
|
|
Muchas gracias hombre!
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
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 and still the C#-code does not work well, but I'm quite fine with this makro-solution.
Now, I am relieved.
Thanks a lot!
Harry
no plan,
no signature
|
|
|
|
|
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.
objRange = objSheet.get_Range("A6", "AK6");
objRange.Select();
objRange.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
objRange = objSheet.get_Range("A10", "Y10");
objRange.Select();
objRange.Copy(Type.Missing);
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.
|
|
|
|
|
Hello Kwagga,
thanks for reply and the still attempt to help
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
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
Hello,
Is it possible to close Vista/Windows7 Gadget's Flyout through C# code ?
Best Regards,
Supriya.
|
|
|
|
|
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.
|
|
|
|
|
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..
|
|
|
|
|
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() + "'";
|
|
|
|
|
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.
|
|
|
|
|
i have 4 records in table what should b my range value?
|
|
|
|
|