UtterAccess.com
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    
 
   
Dougie
post Nov 29 2017, 06:23 AM
Post#1



Posts: 73
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 )
.Application.Activeworkbook.Close
.Quit

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
Post#2


UtterAccess VIP
Posts: 9,880
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?

--------------------
Regards

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
 
River59
post Nov 29 2017, 08:30 AM
Post#3



Posts: 1,341
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    10th December 2017 - 09:25 PM