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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.