vb to link to closed file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave B - 30 Sep 2003 20:30 GMT

I reposted due to my last message was deleted or something?

Excel 2000

Hello,

I need to read cell values from a closed Excel file into an array which is
created in my vb code. Example:
For i = 1 to 5
Myarray(i) = MyClosedExcelFile.Range("A" & i)
Next i

I don't want to paste formulas to a cell and then read into the array - it
needs to go directly into the array.

This needs to be accomplished without opening the file because the file is
so large that when opened, it slows down the user's computer and takes too
long for the macro to complete.

Any help advice appreciated.

dave

Reply to this Message

Bill Manville - 02 Oct 2003 09:43 GMT

> For i = 1 to 5
> Myarray(i) = MyClosedExcelFile.Range("A" & i)
> Next i
>
> I don't want to paste formulas to a cell and then read into the array - it
> needs to go directly into the array.

As far as I know that is not possible.
I would create the formulas in cells and then load the array from there.
You should find that is quicker than doing one cell at a time anyway

Dim MyArray As Variant
With Sheets("Temp").Range("A1").Resize(5)
.Formula = "='C:\MyClosed\[ExcelFile.xls]Sheet1'!A1"
MyArray = .Value
.ClearContents
End With
MsgBox "Second element is " & MyArray(2,1)

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Dave B - 03 Oct 2003 15:02 GMT

Thanks Bill.

The reason I am trying to get data without opening the file is because the
file is an Excel workbook being used as a database. It has over 40,000 rows
and Columns go through FX. When some users try to run my file (which opens
the database file) their computer either freezes or my file takes 5 minutes
to run. We want it to run in less than 30 seconds. On my computer it takes
1 minute. Linking to the file seems very slow too but I will try your code.

Using ADO (another Newsgroup member recommended) I can get the data without
opening the file but the time remains approximately the same. I think this
will alleviate the user's computer from becoming slow because I don't have
to open the extremely large Excel file.

Any ideas of how to speed up my file will be greatly appreciated. I do as
much as I can within vb using arrays to group data and calculate fields
before placing them in exact locations on a report. No matter what I do I
am being pressured to make it faster but I think I am limited by how fast
Excel can process things.

Again, thanks for your input - I see you answer a lot of questions and I
respect your opinion and use some of the code you have recommended from
other posts.
dave

> > For i = 1 to 5
> > Myarray(i) = MyClosedExcelFile.Range("A" & i)
[quoted text clipped - 18 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

Quandan - Marcel Kreijne - 23 Dec 2003 00:29 GMT

Dave,

You might try the function on John Walkenbach's site for reading values from
closed files:

http://j-walk.com/ss/excel/tips/tip82.htm

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl

> Thanks Bill.
>
[quoted text clipped - 44 lines]
> > MVP - Microsoft Excel, Oxford, England
> > No email replies please - respond to newsgroup

Reply to this Message