Translation Excel to Access

V

Varne

Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub
 
J

June7

I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close
 
J

June7

I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close
 
J

June7

Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Varne said:
Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub
Click to expand...
Click to expand...
 
J

June7

Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Varne said:
Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub
Click to expand...
Click to expand...
 
V

Varne

Hi!

Array OK but I am not able to establish connection.

Compile error - user defined type not Defined

I tried adding Dim cn As New ADODB.Connection. No use.

Thanks.

M Varnendra

June7 said:
Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Varne said:
Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub
Click to expand...
Click to expand...
Click to expand...
 
V

Varne

Hi!

Array OK but I am not able to establish connection.

Compile error - user defined type not Defined

I tried adding Dim cn As New ADODB.Connection. No use.

Thanks.

M Varnendra

June7 said:
Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Varne said:
Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub
Click to expand...
Click to expand...
Click to expand...
 
V

Varne

Sorry for responding late. I have limited Internet Access.

I have managed to add ADO library to the object library. So connection works.

June7 said:
Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Varne said:
Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub
Click to expand...
Click to expand...
Click to expand...
 
V

Varne

Sorry for responding late. I have limited Internet Access.

I have managed to add ADO library to the object library. So connection works.

June7 said:
Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:
Code: 
Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Varne said:
Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify Claus code "Sub Array_Var_Column_Sort()" 27
Excel Target.column for Data Validation would like to target some cells within column 1
Copy a range data from 1 sheet to multiple selected sheets 1
Problems with Automating range of sum for Subtotal method in excel after access export 1
array data type issues - 11
My Never ending ARRAY code problems 18
Access Write array data to access table 0
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0

Top