From Access to VBA Array and then to Excel

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.
 
V

vanderghast

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
 
V

vanderghast

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
 
V

Varne

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
 
V

Varne

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
 
V

vanderghast

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


Vanderghast, Access MVP
 
V

vanderghast

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


Vanderghast, Access MVP
 
T

Tony Toews [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
 
T

Tony Toews [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
 
V

Varne

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.
 
V

Varne

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.
 

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

The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database 0
Appending Excel Files to Access 1
Access VBA Equivalent to Excel VBA 12
VB editor appears and then disappear 2
Excel VBA - counting cells by colors and names to it in two different rows. 0
Access #Error & Excel Pivot 0
Array basics! 3
Example Of VBA SQL Statement 0

Top