Friday, March 02, 2007

Transferring array to Excel range

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

6 comments:

  1. Hi,

    Probably 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

    ReplyDelete
  2. Anonymous6:26 PM

    Powershell and Excel

    Can 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/

    ReplyDelete
  3. Anonymous10:32 AM

    REMEMBER to trim each Array value to 255 characters or less. Otherwise, the assignment fails.

    ReplyDelete
  4. Hey, How to convert a 2D array to a 2D excel Range?

    ReplyDelete
  5. Anonymous2:18 PM

    For an algorithm that will handle and compress data on different dimensions in multi dimensional arrays is Excel try:

    http://store.payloadz.com/details/933953-Software-Developer-Excel-VBA-Multi-Dimensional-Array-Algorithm.html

    ReplyDelete
  6. Hi, Thanks for this tips. Nice - it saves my time in populating each row in sheet.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group