Is there any method to transfer the contents of the array to an Excel Range.. Most often programmers used to loop thru the array and put it to the Excel (of course with another loop). Here is a simple code that will transfer the array contents to Excel. Many thanks to Sharmila Purushotaman for this thoughful article
Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Range("A1:A10").Select
Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub
' Keywords : ARRAY to Excel, Transferring array to range
Posts
Hi,
ReplyDeleteProbably not the right place to ask, but how can you convert this to Powershell ?
$sheet = $book.Worksheets.Item(1)
$sheet.Name = "TestInputArray"
$msExcel.Visible = $true
$myarray = @(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
$sheet.Cells(2,1).resize(10,1).value = $msExcel.WorksheetFunction.Transpose($myarray)
Any idea's are greatly appreciated.
Kind regards,
Koen
Powershell and Excel
ReplyDeleteCan you try the following links
http://blogs.msdn.com/krishkp/archive/2008/03/06/powershell-script-to-access-an-excel-file.aspx
http://techstarts.wordpress.com/2008/05/05/powershell-and-excel/
REMEMBER to trim each Array value to 255 characters or less. Otherwise, the assignment fails.
ReplyDeleteHey, How to convert a 2D array to a 2D excel Range?
ReplyDeleteFor an algorithm that will handle and compress data on different dimensions in multi dimensional arrays is Excel try:
ReplyDeletehttp://store.payloadz.com/details/933953-Software-Developer-Excel-VBA-Multi-Dimensional-Array-Algorithm.html
Hi, Thanks for this tips. Nice - it saves my time in populating each row in sheet.
ReplyDelete