Solved

How to Load Multiple Array Elements As A Group With One Line Of Code

Posted on 2011-02-12
12
568 Views
Last Modified: 2012-05-11
Is it possible in Access VBA to create multidimensional array elements and load them at once?

For example, instead of loading array elements at a time:
MyArr (0,1) = "Joe"
MyArr (0,2) = "Jones"
MyArr (0,3) = "225 Baldwin St"
Myarr (0,4) = "Princeton"
MyArr (0,5) = "Calif"
MyArr(0,6) = "98765"

Is there a more compact way to write this code on one line?

Thanks
0
Comment
Question by:Davisron867
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
where are the data coming from?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
look at GetRows() function

Use DAO GetRows Method to Fill Variant Arrays
http://support.microsoft.com/kb/129856

http://msdn.microsoft.com/en-us/library/ff194427.aspx
0
 
LVL 84
Comment Utility
As cap said, GetRows will work if it suits your needs.

If not, then I don't know of any way to load a multidimensional array in a single command. You could perhaps use a UserDefined Type, but you'd still have to fill that UDT and then put it into your array.
0
 

Author Comment

by:Davisron867
Comment Utility
Microsoft-Visual-Basic.pdf

Hi Capricorn1. Pleasure to converse with you.

I attached an example of the code, which links or imports tables from a list of network locations

Thanks
0
 

Author Comment

by:Davisron867
Comment Utility
(followup comment)
Sorry LSM...didn't see your comment until after my post, but its a pleasure to share my question with you as well.

The reason for my question is that my final dataset had an error last week, so I improved the commenting and am trying to simplify the code to make it easier to read and troubleshoot.
0
 

Author Comment

by:Davisron867
Comment Utility
(followup comment)
The code I'm specifically referring to is

'LOAD ARRAY===========================================
ReDim aFileSpecs(7, 3)

'Set FileNames in Array
aFileSpecs(1, 1) = NewestFile(sStratDir, "*.xls")
aFileSpecs(2, 1) = NewestFile(sAcctsDir, "*.xls")
aFileSpecs(3, 1) = NewestFile(sLEDir, "*.xls")
aFileSpecs(4, 1) = NewestFile(sVenDir, "*.xls")
aFileSpecs(5, 1) = NewestFile(sSourceDir, "*.xls")
aFileSpecs(6, 1) = NewestFile(sNDPDir, "*.xls")
aFileSpecs(7, 1) = NewestFile(sALODir, "*.xls")

'Set FilePaths in Array
aFileSpecs(1, 2) = sStratDir & aFileSpecs(1, 1)
aFileSpecs(2, 2) = sAcctsDir & aFileSpecs(2, 1)
aFileSpecs(3, 2) = sLEDir & aFileSpecs(3, 1)
aFileSpecs(4, 2) = sVenDir & aFileSpecs(4, 1)
aFileSpecs(5, 2) = sSourceDir & aFileSpecs(5, 1)
aFileSpecs(6, 2) = sNDPDir & aFileSpecs(6, 1)
aFileSpecs(7, 2) = sALODir & aFileSpecs(7, 1)

'Set FileType Flag in Array
aFileSpecs(1, 3) = "Strats"
aFileSpecs(2, 3) = "Accts"
aFileSpecs(3, 3) = "LE"
aFileSpecs(4, 3) = "Vendors"
aFileSpecs(5, 3) = "SourceList"
aFileSpecs(6, 3) = "NDP"
aFileSpecs(7, 3) = "ALO"

'EMPTY ARRAY
For i = LBound(aFileSpecs) To UBound(aFileSpecs)
'Delete First Row (if empty) of PS Query output files
   Call DeleteFirstRow(aFileSpecs(i, 2), aFileSpecs(i, 3))
'Delete existing table link in Access
   DoCmd.DeleteObject acTable, aFileSpecs(i, 3)
'Link New Table to Access
   DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, aFileSpecs(i, 3), aFileSpecs(i, 2), True
Next i
End Sub
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84
Comment Utility
So you are getting an error?

Unless you specify differently, arrarys are zero-based - that is, they start at 0 and increment form there. So an array with three elements would be accessed like this:

MyArray(0) '/first
MyArray(1) '/second
MyArray(2) '/third

Note that you CAN set the lower bound of an array to 1, but I don't see where you've done that in this code specifically.

So you would do this:

aFileSpecs(0, 1) = NewestFile(sStratDir, "*.xls")
aFileSpecs(1, 1) = NewestFile(sAcctsDir, "*.xls")
aFileSpecs(2, 1) = NewestFile(sLEDir, "*.xls")
aFileSpecs(3, 1) = NewestFile(sVenDir, "*.xls")
aFileSpecs(4, 1) = NewestFile(sSourceDir, "*.xls")
aFileSpecs(5, 1) = NewestFile(sNDPDir, "*.xls")
aFileSpecs(6, 1) = NewestFile(sALODir, "*.xls")

0
 

Author Comment

by:Davisron867
Comment Utility
LSM:

Thanks for your response.

There is an Option Base 1 Statement in the Module declarations. I'm not getting any run-time errors...i simply had a issue where I was making updates to the code and suddenly the data stopped tying-out. When I went through the code, I realized that mistakes can be made when its hard to read, so I was just trying to organize it better and make it more compact and efficient.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
If you declare myarr as variant, you can do this:

myarr = Array(Array("Joe", "Jones", "225 Baldwin St", "Princeton", "Calif", "98765"))

it works - watch myarr in debug and you'll see this makes

myarr(0,1) = "Joe"
myarr(0,2) = "Jones"

etc...

Dave
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 250 total points
Comment Utility
I "mistyped".  You do it like this (create an array of arrays - and can work with them like 2-dim arrays, just the referencing is a bit difference - see the debug.print statement, below:

Sub loadArray()
Dim myarr() As Variant

    myarr = Array(Array("Joe", "Jones", "225 Baldwin St", "Princeton", "Calif", "98765"))
   
    For i = 0 To 5
        Debug.Print myarr(0)(i)
    Next i
   
   
End Sub
0
 

Author Comment

by:Davisron867
Comment Utility
Thanks guys.

I actually meant to have dlmille's answer show as the Accepted Solution and Capricorn1's answer be the Assisted Solution, but no big deal.

Both the GetRows method and the Array of Arrays structure are perfect for this task.
0
 

Author Comment

by:Davisron867
Comment Utility
FYI...
Tushar Mehta's overview of the Array of Arrays Structure -  http://www.tushar-mehta.com/publish_train/book_vba/08_variants.htm

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now