Write to Array from other closed Excel file
 

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

Write to Array from other closed Excel file
 

Hi Dave,

You can use ADO to get data from a closed workbook. There are some
limitations, but it should be much faster than opening the workbook and
retrieving values. Once you have the recordset open, you can use the
GetRows method of the RecordSet object to put the values into a
2-dimensional array.

http://www.erlandsendata.no/english/...mportwbado.php

--
Regards,

Jake Marx
www.longhead.com


Dave B wrote:
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

What is ADO?
 

Thanks for the reply. What is ADO a program?

dave

"Jake Marx" wrote in message
...
Hi Dave,

You can use ADO to get data from a closed workbook. There are some
limitations, but it should be much faster than opening the workbook and
retrieving values. Once you have the recordset open, you can use the
GetRows method of the RecordSet object to put the values into a
2-dimensional array.

http://www.erlandsendata.no/english/...mportwbado.php

--
Regards,

Jake Marx
www.longhead.com


Dave B wrote:
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

What is ADO?
 

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/de...startpage1.asp

--
Regards,

Jake Marx
www.longhead.com


Dave B wrote:
Thanks for the reply. What is ADO a program?

dave

"Jake Marx" wrote in message
...
Hi Dave,

You can use ADO to get data from a closed workbook. There are some
limitations, but it should be much faster than opening the workbook
and retrieving values. Once you have the recordset open, you can
use the GetRows method of the RecordSet object to put the values
into a 2-dimensional array.

http://www.erlandsendata.no/english/...mportwbado.php

--
Regards,

Jake Marx
www.longhead.com


Dave B wrote:
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

Thanks -It Works ! Write to Array from other closed Excel file
 

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


"Jake Marx" wrote in message
...
Hi Dave,

You can use ADO to get data from a closed workbook. There are some
limitations, but it should be much faster than opening the workbook and
retrieving values. Once you have the recordset open, you can use the
GetRows method of the RecordSet object to put the values into a
2-dimensional array.

http://www.erlandsendata.no/english/...mportwbado.php

--
Regards,

Jake Marx
www.longhead.com


Dave B wrote:
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

Thanks -It Works ! Write to Array from other closed Excel file
 

Hi Dave,

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

--
Regards,

Jake Marx
www.longhead.com

Dave B wrote:
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


"Jake Marx" wrote in message
...
Hi Dave,

You can use ADO to get data from a closed workbook. There are some
limitations, but it should be much faster than opening the workbook
and retrieving values. Once you have the recordset open, you can
use the GetRows method of the RecordSet object to put the values
into a 2-dimensional array.

http://www.erlandsendata.no/english/...mportwbado.php

--
Regards,

Jake Marx
www.longhead.com


Dave B wrote:
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