Solved Handling data from a spreadsheet (1 Viewer)

bob fitz

AWF VIP
Local time
Today, 06:35
Joined
May 23, 2011
Messages
4,717
Curious Bob, how is the speed?
From memory, using the VBA code that I wrote, it took about 60 seconds to write 14,706 test records into the single price table producing 58,824 new records. But the process is expected to be "ongoing" with thousands of new records added every day. The proposed solutions, using queries, from Chris and Arne, produce the same result in the blink of an eye. 😳
I'm so glad I asked the question here and as always, so very grateful to everybody that has contributed to the discussion. 😁
I've learnt so much from forums like this one. That's why I try to help others here when I can, although I have to say that my efforts are often superfluous as there are so many knowledgeable members here.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:35
Joined
Apr 27, 2015
Messages
6,286
my efforts are often superfluous as there are so many knowledgeable members here.
I know how you feel, but every once in a while us superfluous members get our moment!

It is amazing how much quicker batch queries are which is why I use them as much as possible. One of the main reasons I offered my lame contribution is that I could follow this thread in hopes to pick up something useful. As usual, I was not disappointed...
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:35
Joined
Apr 27, 2015
Messages
6,286
When I Googled the Choose() function, it looked a lot like the Switch() function, right down the the "all options are evaluated" caution...

So I did a GDB on the difference and it turns out there are a few:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:35
Joined
Feb 19, 2013
Messages
16,553
choose is a vba function not specifically excel - choose an option starting from 1. Only issue can be if there are gaps in the numbers you want to choose from - which is where Switch can come in
 

Users who are viewing this thread

Top Bottom