From Access to VBA Array and then to Excel

  • Thread starter Thread starter Varne
  • Start date Start date
V

Varne

Hi!

I want to send a column of an Access table to a VBA Array and then from
there to Excel. Could someone help me introducing the codes to me.

Thank You.
 
Open a DAO recordset with only the required field.

Make a call to the DAO recordset method GetRows, that you will capture into
your variant (array). Note that the indexes (original field, original
record) starts at 0, not at 1


See the GetRows method, in the help file for more details.


Vanderghast, Access MVP
 
Open a DAO recordset with only the required field.

Make a call to the DAO recordset method GetRows, that you will capture into
your variant (array). Note that the indexes (original field, original
record) starts at 0, not at 1


See the GetRows method, in the help file for more details.


Vanderghast, Access MVP
 
Hi Van

I have a look at GetRows help but let me detail my problem a bit more.

I am going to write the codes in the Access VBE. After reading the Access
table records into VBA Array I want to write those into Excel. Could you show
me the codes please?

Thank You.
M Varnendra
 
Hi Van

I have a look at GetRows help but let me detail my problem a bit more.

I am going to write the codes in the Access VBE. After reading the Access
table records into VBA Array I want to write those into Excel. Could you show
me the codes please?

Thank You.
M Varnendra
 
Have you tried DoCmd.TransferSpreadsheet ? You won't need to pass by an
intermediate storage.


Vanderghast, Access MVP
 
Have you tried DoCmd.TransferSpreadsheet ? You won't need to pass by an
intermediate storage.


Vanderghast, Access MVP
 
Varne said:
VBA writes between 10m to 30m times in a second within its array. SQL?
Click to expand...

No idea. But keep in mind you're dealing with Excel as well.

I found that writing cell by cell to Excel while fast for each cell
was excruciatingly slow overall. But using the copyrecordsets was very
fast.

Tony
 
Varne said:
VBA writes between 10m to 30m times in a second within its array. SQL?
Click to expand...

No idea. But keep in mind you're dealing with Excel as well.

I found that writing cell by cell to Excel while fast for each cell
was excruciatingly slow overall. But using the copyrecordsets was very
fast.

Tony
 
Say I 2m Access records with 5 fields to VBA Array in a second. Then process
it in another second. The report will not be of 2M rows. say it is of 1000
rows 5 fields. VBA writing speed on Excel is minimum 2500 cells per second so
2 seconds. The report will be ready in 5 to 6 seconds.
 
Say I 2m Access records with 5 fields to VBA Array in a second. Then process
it in another second. The report will not be of 2M rows. say it is of 1000
rows 5 fields. VBA writing speed on Excel is minimum 2500 cells per second so
2 seconds. The report will be ready in 5 to 6 seconds.
 
Back
Top