X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Fast Access To Excel, Access 2007    
post Nov 29 2017, 06:23 AM

Posts: 76
Joined: 17-December 15

I have a db which imports an Excel spreadsheet to an Access table. To check that nothing has been lost I have code that gets the total of a column in the spreadsheet and compares it with the total of the corresponding column in the Access table. My problem is that the spreadsheet is very large, and obtaining this check total from Excel takes much longer than I'd like (users begin to think the system has hung).

The VBA code I use is quite simple:

With xlApp.Application

Set r = .Range("D:D")
curCheckTurnover = .WorksheetFunction.Sum(r )

End With

Is there any way to do this faster?
Go to the top of the page
Jeff B.
post Nov 29 2017, 07:41 AM

UtterAccess VIP
Posts: 9,932
Joined: 30-April 10
From: Pacific NorthWet

Is there a reason you aren't leaving the data in Excel and using Access to, well, access it?


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Nov 29 2017, 08:30 AM

Posts: 1,356
Joined: 7-April 10
From: Detroit, MI

It may be taking longer than necessary because you are adding the entire column in your code (there are a lot of rows in Excel). Perhaps you need to determine the last row and only add the values to there.

LastRow = appExcel.Range("D1").End(xlDown).Row

Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th February 2018 - 03:05 PM