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: 81
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: 10,168
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?
Go to the top of the page
post Nov 29 2017, 08:30 AM

Posts: 1,442
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
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th December 2018 - 12:43 AM