I'm currently trying to write data from an array of objects to a range in Excel using the following code, where objData
is just an array of strings:
private object m = System.Type.Missing; object[] objData = getDataIWantToWrite(); Range rn_Temp; rn_Temp = (Range)XlApp.get_Range(RangeName, m); rn_Temp = rn_Temp.get_Resize(objData.GetUpperBound(), 1); rn_Temp.value2 = objData;
This very nearly works, the problem being that the range gets filled but every cell gets the value of the first item in the objData
.
The inverse works, i.e.
private object m = System.Type.Missing; object[] objData = new object[x,y] Range rn_Temp; rn_Temp = (Range)XlApp.get_Range(RangeName, m); rn_Temp = rn_Temp.get_Resize(objData.GetUpperBound(), 1); objData = (object[])rn_Temp.value2;
would return an array containing all of the values from the worksheet, so I'm not sure why reading and assignment work differently.
Has anyone ever done this successfully? I'm currently writing the array cell by cell, but it needs to cope with lots (>50,000) of rows and this is therefore very time consuming.
Steps to Add a Range into an Array in VBA First, you need to declare a dynamic array using the variant data type. Next, you need to declare one more variable to store the count of the cells from the range and use that counter for the loop as well. After that, assign the range where you have value to the array.
To Fill a Dynamic ArrayOn the Tools menu, point to Macro and then click Macros. In the Macro dialog box, click fill_array, and then click Run.
The array may be 1 or 2 dimensional. To write a one dimensional array back to the worksheet, you must create a Range object, resize that range to the size of your array, and then write to the range. This code will write the values of Arr to range that is one row tall by UBound(Arr) columns wide, starting at range K1.
This is an excerpt from method of mine, which converts a DataTable
(the dt
variable) into an array and then writes the array into a Range
on a worksheet (wsh
var). You can also change the topRow
variable to whatever row you want the array of strings to be placed at.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count]; for (int r = 0; r < dt.Rows.Count; r++) { DataRow dr = dt.Rows[r]; for (int c = 0; c < dt.Columns.Count; c++) { arr[r, c] = dr[c]; } } Excel.Range c1 = (Excel.Range)wsh.Cells[topRow, 1]; Excel.Range c2 = (Excel.Range)wsh.Cells[topRow + dt.Rows.Count - 1, dt.Columns.Count]; Excel.Range range = wsh.get_Range(c1, c2); range.Value = arr;
Of course you do not need to use an intermediate DataTable
like I did, the code excerpt is just to demonstrate how an array can be written to worksheet in single call.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With