Specified argument was out of the range of valid values.

Question

1.00/5 (1 vote)

See more:

C#

Workbook newBook = new Workbook();            
            Worksheet newSheet = newBook.Worksheets[0];            
            Workbook workbook = new Workbook();            
            workbook.LoadFromFile(filename);
            Worksheet sheet = workbook.Worksheets[0];

            foreach (CellRange range in sheet.Columns[0])
            {
                
                    CellRange sourceRange = sheet.Range[range.Row, 1, range.Row, columnCount];
                    CellRange destRange = newSheet.Range[i, 1, i, columnCount];
                    sheet.Copy(sourceRange, destRange, true);
                
            i++;
               
            }

What I have tried:

C#

Workbook newBook = new Workbook();            
            Worksheet newSheet = newBook.Worksheets[0];            
            Workbook workbook = new Workbook();            
            workbook.LoadFromFile(filename);
            Worksheet sheet = workbook.Worksheets[0];

            foreach (CellRange range in sheet.Columns[0])
            {
                
                    CellRange sourceRange = sheet.Range[range.Row, 1, range.Row, columnCount];//error
                    CellRange destRange = newSheet.Range[i, 1, i, columnCount];
                    sheet.Copy(sourceRange, destRange, true);
                
            i++;
               
            }

Posted 9-Jul-20 19:46pm

Member 14120682

Updated 9-Jul-20 21:31pm

Maciej Los

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-07-09T19:52:00

We can't tell - we have no access to your code while it is running, or to your Excel data, and you need both in order to find out why this occurs, much less fix it.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.
When you know where the problem is and what values you are using, you can start comparing that to the actual spreadsheet and work out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!

Patrice T · Answer 2 · 2020-07-09T20:32:00

Quote:
Specified argument was out of the range of valid values.

The error message also tells you the position of error, it is a good idea to share.
Basically, your code have some expectations on the data that the data don't met.
It is impossible to tell without running your code.
The only way is to watch your code perform with help of debugger.

Your code do not behave the way you expect, or you don't understand why !

There is an almost universal solution: Run your code on debugger step by step, inspect variables.
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't know what your code is supposed to do, it don't find bugs, it just help you to by showing you what is going on. When the code don't do what is expected, you are close to a bug.
To see what your code is doing: Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.

Debugger - Wikipedia, the free encyclopedia[^]

Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
Basic Debugging with Visual Studio 2010 - YouTube[^]

Debugging C# Code in Visual Studio - YouTube[^]

The debugger is here to only show you what your code is doing and your task is to compare with what it should do.

Maciej Los · Answer 3 · 2020-07-09T21:31:00

As per documentation: Worksheet.Range Property (Microsoft.Office.Tools.Excel) | Microsoft Docs[^]

Quote:
The Range property is intended to be used with the following parameters.

Remarks

Parameter Description
Cell1 The name of the range in A1-style notation in the language of the application. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they are ignored. You can use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the application. This parameter is required.
Cell2 The cell in the lower-right corner of the range. Can be a Microsoft.Office.Interop.Excel.Range that contains a single cell, an entire column, an entire row, or it can be a string that names a single cell in the language of the application. This parameter is optional.

Conclusion: you can pass only two parameters, instead of four!

Specified argument was out of the range of valid values.

3 solutions

Solution 1

Solution 2

Solution 3

Remarks

Add your solution here

Preview 0