Link to home
Start Free TrialLog in
Avatar of Davisron867
Davisron867Flag for United States of America

asked on

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

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

where are the data coming from?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Davisron867

ASKER

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
(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.
(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
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")

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.
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
FYI...
Tushar Mehta's overview of the Array of Arrays Structure -  http://www.tushar-mehta.com/publish_train/book_vba/08_variants.htm