Saving multi-dimensional array to file and reloading at a later time

I have an excel (Excel 2010) macro that creates a multi-dimensional array from multiple excel files to generate individual reports.

Rather than reloading the array every time the reports are being tested/vetted, is there a way to save the data from the multi-dimensional array into a file  and reload the array from this file rather than recreating the array from the original data files every time?  If so, how would this be done using Excel?
uadAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Here is a macro that demonstrates how to write to and retrieve the data back into an array. This array is smaller than yours but the concept is the same.

Sub Create()
Dim lng1 As Long
Dim lng2 As Long
Dim lng3 As Long
Dim lngEntry As Long
Dim MyArray() As Long
Dim strData As String
Dim strInput() As String
Dim FF As Integer

' Create test data
ReDim MyArray(6, 5, 4) As Long
For lng1 = 0 To 6
    For lng2 = 0 To 5
        For lng3 = 0 To 4
            MyArray(lng1, lng2, lng3) = lng1 + lng2 + lng3
        Next
    Next
Next

' Display some random values
Debug.Print "Output"
Debug.Print MyArray(1, 4, 3)
Debug.Print MyArray(2, 2, 2)
Debug.Print MyArray(6, 5, 4)
Debug.Print MyArray(3, 3, 3)

' Save the data to a CSV file
FF = FreeFile
Open "C:\temp\SavedData.txt" For Output As #FF
For lng1 = 0 To 6
    For lng2 = 0 To 5
        For lng3 = 0 To 4
            If lng1 = 6 And lng2 = 5 And lng3 = 4 Then
                strData = strData & MyArray(lng1, lng2, lng3)
            Else
                strData = strData & MyArray(lng1, lng2, lng3) & ","
            End If
        Next
    Next
Next
Print #FF, strData
Close

' Clear the array
ReDim MyArray(6, 5, 4) As Long

' Load the array
Open "C:\temp\SavedData.txt" For Input As #FF
strData = Input$(LOF(FF), FF)
Close

strInput = Split(strData, ",")
For lng1 = 0 To 6
    For lng2 = 0 To 5
        For lng3 = 0 To 4
            MyArray(lng1, lng2, lng3) = strInput(lngEntry)
            lngEntry = lngEntry + 1
        Next
    Next
Next


' Display the same values
Debug.Print "Input"
Debug.Print MyArray(1, 4, 3)
Debug.Print MyArray(2, 2, 2)
Debug.Print MyArray(6, 5, 4)
Debug.Print MyArray(3, 3, 3)

End Sub

Open in new window

0
 
omgangIT ManagerCommented:
First thing that comes to mind it to output the array contents to a delimited text file.
How many dimensions is the array?  Are you able to provide some sample data?
OM Gang
0
 
uadAuthor Commented:
The array is dimensions (6,000 , 55, 4).

Due to the nature of the data, unable to post a sample of the data.

Based on your suggestion of output to a delimited text file, are you suggesting a single text file for the depth - 4 for this example.  In other words 4 files each with the equivalent of 6000 rows and 55 columns?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
omgangIT ManagerCommented:
With the dimensions you indicate it's possible to use four text files as output but I'm not sure you're gaining a whole lot versus reloading the data from the original Excel worksheets each time.  Is there a possibility of using a database to save the array data to?   MS Access with a local .mdb file?

Are you able to describe how the dimensions are related?  From your last comment I'm picturing 6000 records (rows), 55 columns, and one or more of the columns can have up to 4 values.  Or do all 55 columns have 4 values?

OM Gang
0
 
uadAuthor Commented:
As what happens in most cases, this was supposed to be a one and done - but, that isn't how it turned out . . . yes, access would be the way to go if there was time permitting to recreate everything in access, which there isn't.

As to the format yes, most of the 55 elements per individual row, could have up to 4 separate values.  A simple want to grasp the array would be:  a person (1 to 6000) and item (1 to 55) and value for each item (e.g. 4 yrs)
0
 
jmcgOwnerCommented:
Alexey Kudinov claims to show

How to serialize objects in VBA

It's a very general method and the XML file generated may be larger and slower than the method you already have.
0
 
uadAuthor Commented:
Shared point - one gave me the concept to approach this issue, the other gave me basic code to follow
0
 
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.