vba reading from excel

[yoritomo]

I am running a vba macro in excel reading through the list of data (3000) large and checking dates, if the current dat is the same as the previous date it needs to store the corresponding value, and so on until the date changes, then average the stored values and output it on the first one, and 0 the rest

Sadly this takes well over 20 mins to run, so I can across This website that shows a way of bulk reading data into vba, cutting down on hit times. When I ran just a quick test seemed to cut down the time dramatically, the problem I am having is in his code

'to read in
Dim A as Variant 'MUST be variant, no brackets
A = Range("SomeRange").Resize(10,20) 'reads 10x20 array starting at range SomeRange
'(NB I've used Resize above but you can specify a range of cells any way you want)

'to write back to sheet
Range("SomeRange").Resize(10,20) = A
'A can be any data type but MUST be two dimensional even if you are only writing one
'column or row - first dimension is used for rows, and the second for columns
'this can be slow - see third question below for workaround..

I have no idea how to get the data out of A, when I try to treat it like a array it gives me the subscript out of range error, and if I assign it as an array before hand it crashes with another error, just wondering if anyone knows how to extract the data from A, if not, does anyone know another good way to bulk read data into vba?

 

[Bat17]

It sounds to me like you could do this in an access query,
you should be able to link the table into access and the use a 'group by' query with averages to pull out your answers. This wont alter the original data but will give the output you need.

HTH

Peter

 

[yoritomo]

Ah, here-in my problem lies sadly, I have been programming for about 5 years now, but only in the last few months have i started using excel and access, and as such am still kinda learning as I go, how would i link the table to an access query? I thought the only way you could get them to talk was the microsoft query program

 

[Bat17]

Files>Get external Data>Link Tables...
This will bring up the dialog box, select Xls as file type and fine you file.

You could also just import the data rather than linking.

Peter

 

[yoritomo]

erm, hmm maybe its just me, but thats doesnt seem to do anything at all, it says it imports data but it doesnt give me any option of what it imports or anything, hmm it just occured to me as well that we might be talking at crossways, my data is originally stored in access, and queried into excel using ms query, the problem being that in ms query I cant find anyway to use group by clauses in it.
I need the data to end up in a refreshable query on an excel sheet to, it cant just be a one off snapshot, as it gets updated at randomtimes, with a random amount each day
What would be great would be if I could query the data in access using an access query, then export it to excel but have excel be able to refresh the query much like it does with ms query, no idea if its possible or not though
If you could shed any light there it would be great

 

[Bat17]

I think that you can do group by queries in MS Query OK but that you have to work directly in SQL view rather than query grid. Unfortunately I have broken it on this PC so cant do any tests for you.

Peter

 

[yoritomo]

Ah great I'll give that a whirl, thanks