Write to Array from other closed Excel file

  • Thread starter Thread starter Dave B
  • Start date Start date
D

Dave B

Hello,

Excel 2000

In an effort to speed up a report generating Excel file, I am experimenting
with NOT opening the Excel database file which is huge and takes 20 seconds
to open.

I need vb code that will get data from specified cells in an unopened Excel
file (database) and then write to an array (created in vb) in my current
file. I want to do this entirely in vb (I don't want to write a formula to
a range and then put in an array). Is this possible? Any examples?

Thanks, you guys are a great resource.

Dave
 
Hi Dave,

ADO (ActiveX Data Objects) is a data access library that allows you to
retrieve/update data from external sources (text files, Excel files, SQL
Server, Access, etc) without opening the host application. You basically
set up an ADO Connection, retrieve the data using a Command or Recordset
object, and the data set is stored in a Recordset object, which is basically
a collection of rows and fields (columns) that you can step through. The
Recordset object has a method named "GetRows" which will allow you to dump
the contents of the Recordset into an array.

To use this library from within Excel, you must set a reference to it (Tools
| References, Microsoft ActiveX Data Objects x.x Library).

Here's more info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/adostartpage1.asp
 
Thanks Jake. This has got me going in the right direction. I'm trying to
speed up a user Excel report generator that has to get data from a huge
Excel database. This way, I don't have to open the database file. This is
still slower than I'd like but I haven't tried optimizing it yet. Any ideas
to make it faster would be appreciated !!

dave
 
Hi Dave,

Great - glad to help. If you post the relevant code here, maybe someone can
help you to optimize it.
 
Back
Top