Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    35

    Unanswered: Exporting Arrays of Data

    I have an Access VBA program that performs calculations on subsets of data in my access database and creates large arrays (~8760 by 100 by 16 doubles).

    Currently I am outputting the data to .csv files using the FileSystemObject (fso), but the output is extremely slow. Is there a faster way to output my data?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    how about something like:
    Code:
    Sub Excel_Array()
     
        Dim exApp As Excel.Application
        Dim exWB As Excel.Workbook
        Dim exSheet As Excel.Worksheet
        Dim exRange As Excel.Range
        Dim TheArray(10, 10) As Integer
        Dim i As Integer
        Dim j As Integer
        
        Set exApp = New Excel.Application
            
        exApp.Visible = True
        Set exWB = exApp.Workbooks.Add
        
        Set exSheet = exWB.Worksheets(1)
        
        Set exRange = exSheet.Range("A1:J10")
     
        For i = 0 To 10
            For j = 0 To 10
                
                TheArray(i, j) = i * (j * 100)
                
            Next j
        Next i
        
        exRange.Value = TheArray
        
        Set exRange = Nothing
        Set exSheet = Nothing
        Set exWB = Nothing
        Set exApp = Nothing
     
    End Sub
    More Excel automation here:
    http://www.dbforums.com/showthread.p...=1#post6222281
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2006
    Posts
    35
    Hmmm...

    I never thought excel output would be faster than fso...

    I'll definitely give it a try!!

    Thanks!!

  4. #4
    Join Date
    May 2006
    Posts
    35
    Quick question Pootle:

    Since the array is 3D ( Dim TheArray(8760,16,100) as double ), I would like to span the second dimension across 16 sheets.

    Should I iterate through the array inserting individual elements:
    e.g.

    Code:
    For i = 1 To UBound(TheArray, 1)
        For j = 1 To UBound(TheArray, 2)
            For k = 1 To UBound(TheArray, 3)
                 Sheets(j).cell(i, k) = TheArray(i, j, k)
            Next k
        Next j
    Next i
    Or should I be repopulating new 2D arrays (Dim TheArray1(8760,100) as double), and dump them to sheets using range.value = TheArray1.

    Offhand, do you know which would be faster/more efficient?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yep - noticed the third dimension after I had written the code

    I presume by using fso you used a filestreamobject and iterated?

    Defo 1 million and 1 percent the latter option. Hmmm.... can you have an array of arrays?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Hmmm.... can you have an array of arrays?
    No.
    But you can have an array of types
    But you can't pass them as arguements to Excel
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Quote Originally Posted by pootle flump
    Hmmm.... can you have an array of arrays?
    No.
    But you can have an array of types
    But you can't pass them as arguements to Excel
    Yes
    No
    Yes
    No
    YES!
    http://msdn2.microsoft.com/en-us/library/hkhhsz9t.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - this time I won't post a link to VB.NET

    Array of arrays is actually fine - don't know how I got it so badly wrong:
    Code:
    Sub Excel_Array_3()
     
        Dim exApp As Excel.Application
        Dim exWB As Excel.Workbook
        Dim exSheet As Excel.Worksheet
        Dim exRange As Excel.Range
        Dim TheArray(10) As Variant
        Dim TheOtherArray(10, 10) As String
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        
        Set exApp = New Excel.Application
            
        exApp.Visible = True
        Set exWB = exApp.Workbooks.Add
        
        Set exSheet = exWB.Worksheets(1)
        
        Set exRange = exSheet.Range("A1:K11")
        
        For k = 0 To 10
            For i = 0 To 10
                For j = 0 To 10
                    TheOtherArray(i, j) = i * j
                Next j
            Next i
            TheArray(0) = TheOtherArray
        Next k
        
        exRange.Value = TheArray(0)
        
        Set exRange = Nothing
        Set exSheet = Nothing
        Set exWB = Nothing
        Set exApp = Nothing
     
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •