Solved

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

Posted on 2011-02-12
12
579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34879785
where are the data coming from?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 34879808
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 85
ID: 34880049
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Davisron867
ID: 34880057
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
ID: 34880165
(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
ID: 34880200
(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
 
LVL 85
ID: 34880238
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
ID: 34880303
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 42

Expert Comment

by:dlmille
ID: 34881120
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 42

Assisted Solution

by:dlmille
dlmille earned 250 total points
ID: 34881160
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
ID: 34881422
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
ID: 34881427
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 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