Excel values to Range of variable columns

Question

I've searched all over but no one has had the same problem that I have.

Basically, The user clicks a cell within Excel, opens my form, fills in values, clicks insert. The program that adds those variables to an Array which then Excel reads and places.

This is my first Office App, so I'm flying purely blind here but this is my pseudo code.

My Solution Edited:

        static string GetColumnLetter(int columnNumber)
    {
        var dividend = columnNumber;
        var columnName = String.Empty;

        while (dividend > 0)
        {
            var modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar(65 + modulo) + columnName;
            dividend = ((dividend - modulo) / 26);
        }

        return columnName;

    }



        // Insert into Excel
    public static void ExcelInsert(string mFunction, string mColor, int mQty, string mFau, string mPrice)
    {
        var values = new List<string>
                         {
                             mFunction,
                             mColor,
                             mQty.ToString(),
                             mFau,
                             mPrice
                         }.ToArray();

        var rowNumber = Globals.ThisAddIn.Application.ActiveCell.Row;
        var columnNumber = Globals.ThisAddIn.Application.ActiveCell.Column;
        var columnLetter = GetColumnLetter(columnNumber);

        for (var i = 0; i < values.Count(); i++ )
        {
            var range = Globals.ThisAddIn.Application.Range[String.Format("{0}{1}", columnLetter, rowNumber)];
            range.Value = values[i];
            columnNumber++;
            columnLetter = GetColumnLetter(columnNumber);
        }
    }

So your users insert data into a Windows form, and you then want to place these values onto certain cells on an Excel Spreadsheet? — JMK
– JMK, Commented Sep 30, 2012 at 19:01
Same cells every time? For example, do you want the data in the first textbox the user enters into to go into A3, the next into B6 etc? — JMK
– JMK, Commented Sep 30, 2012 at 19:06
Well thats the trick, Its working on my friend's business proposal sheet and they'll need this to work all over the place. The activecell will always be new. — Kaleet
– Kaleet, Commented Sep 30, 2012 at 19:17
OK, so to clarify you are doing this from a C# .Net Windows Form, and you want to data enter into the form, and append to the bottom of an Excel Worksheet? — JMK
– JMK, Commented Sep 30, 2012 at 19:18
There is a C#.NET WinForm for which data is entered, then when they click "Insert". @ActiveCell the information is inserted. The Cell could be A5, B2, G5, etc. Adding to a single cell isnt the problem, its adding to the activecell then the next columns until end of array thats giving me a headache. — Kaleet
– Kaleet, Commented Sep 30, 2012 at 19:22

Community · Accepted Answer · 2017-05-23 12:07:57Z

2

I would try getting the location of the active cell, and going right, adding each value one at a time as you go like so:

using Excel = Microsoft.Office.Interop.Excel;

void MyMethod()
{
    //Replace '7' with the number of fields on your Windows Form
    int numberOfFields = 7;

    string[] array = new string[numberOfFields];

    array[0] = textBoxOneValue;
    array[1] = textBoxTwoValue;
    array[2] = textBoxThreeValue;
    array[3] = textBoxFourValue;
    array[4] = textBoxFiveValue;
    array[5] = textBoxSixValue;
    array[6] = textBoxSevenValue;

    Excel.Application application = new Excel.Application();
    Excel.Workbook workbook = application.Workbooks.Open(@"C:\whatever.xlsx");
    Excel.Worksheet worksheet = workbook.ActiveSheet;

    Excel.Range activeCell = application.ActiveCell;

    int rowNumber = activeCell.Row;
    int columnNumber = activeCell.Column;

    string columnLetter = GetColumnLetter(columnNumber);

    for(int i = 0; i < numberOfFields; i++)
    {
        Excel.Range range = worksheet.get_Range(String.Format("{0}{1}", columnLetter, rowNumber));
        range.Value = array[i];
        columnNumber++;
        columnLetter = GetColumnLetter(columnNumber);
    }
}

string GetColumnLetter()
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    }

    return columnName;

}

Credit of course to Graham for the GetColumnLetter method, which is about the most genius thing I have ever seen!

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Sep 30, 2012 at 19:42

JMK

28.2k56 gold badges177 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kaleet Over a year ago

Alright. I'll try this method after the packer game. GO PACK!

Kaleet Over a year ago

That crazyness within GetColumnLetter worked! I've posted my modifications to the code.

GSerg · Accepted Answer · 2012-09-30 19:07:48Z

0

You don't need a loop for that.

If arr is an array, then

ActiveCell.Resize(, UBound(arr) - LBound(arr) + 1).Value = arr

answered Sep 30, 2012 at 19:07

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

5 Comments

Kaleet Over a year ago

Okay hang on can you do UBound in C#?

Kaleet Over a year ago

Well it sort of worked, I used GetUpperBound and LowerBound, except it went down not next row over. But good start none the less.

GSerg Over a year ago

@Kaleet I thought you were to use VBA. If it went down then you missed the comma in the call to Resize, you're supposed to provide the second parameter, not the first.

Kaleet Over a year ago

I really wished I could make this miracle work, I really did. But the compiler didn't like the , 1 bit.

GSerg Over a year ago

@Kaleet That was the VBA way of saying, parameter is missing. In c# you need to, depending on your VS version, either pass the missing value: Resize(missing, arr.Length) or use named parameters: Resize(ColumnSize: arr.Length) (see msdn.microsoft.com/en-us/library/ms178843.aspx).

RichardTheKiwi · Accepted Answer · 2012-09-30 19:18:02Z

0

Here's a classic way to iterate through an array

Dim x(3) As String ' your array
x(0) = "abc"
x(1) = "def"
x(2) = "ghi"
x(3) = "jkl"

Dim r As Range ' range to paste
Set r = ActiveCell

For Each s In x
    r.Value = s
    Set r = r.Offset(,1) ' keep going right
Next

The following may help if you have nothing in position 0

Dim x(4) As String ' this can actually contain 5 items, from 0 to 4
x(1) = "abc"
x(2) = "def"
x(3) = "ghi"
x(4) = "jkl"

Dim r As Range ' range to paste
Set r = ActiveCell

For i = 1 To UBound(x) ' skip 0
    r.Value = x(i)
    Set r = r.Offset(, 1) ' keep going right
Next

edited Sep 30, 2012 at 19:18

answered Sep 30, 2012 at 19:12

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

1 Comment

JMK Over a year ago

This question has a C# tag, which makes me think the OP wants to interop with Excel via .Net, do you think this isn't the case?

Collectives™ on Stack Overflow

Excel values to Range of variable columns

3 Answers 3

2 Comments

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related