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, Sep 30 '12 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, Sep 30 '12 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, Sep 30 '12 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, Sep 30 '12 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, Sep 30 '12 at 19:22

score 2 · Accepted Answer · edited May 23 '17 at 12:07

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!

Alright. I'll try this method after the packer game. GO PACK! — Kaleet, Sep 30 '12 at 20:33
That crazyness within GetColumnLetter worked! I've posted my modifications to the code. — Kaleet, Oct 01 '12 at 15:41

score 0 · Answer 2 · answered Sep 30 '12 at 19:07

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 '12 at 19:07

GSerg

76,472
17
159
346

Okay hang on can you do UBound in C#? – Kaleet Sep 30 '12 at 19:27
Well it sort of worked, I used GetUpperBound and LowerBound, except it went down not next row over. But good start none the less. – Kaleet Sep 30 '12 at 19:37
@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. – GSerg Sep 30 '12 at 20:50
I really wished I could make this miracle work, I really did. But the compiler didn't like the , 1 bit. – Kaleet Oct 01 '12 at 15:44
@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 http://msdn.microsoft.com/en-us/library/ms178843.aspx). – GSerg Oct 01 '12 at 16:26

RichardTheKiwi · Answer 3 · 2012-09-30T19:18:02.857

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 '12 at 19:18

answered Sep 30 '12 at 19:12

RichardTheKiwi

105,798
26
196
262

1

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? – JMK Sep 30 '12 at 19:15

Excel values to Range of variable columns

3 Answers3