If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
vb to link to closed file
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 |
#2
|
|||
|
|||
vb to link to closed file
Dave B wrote:
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 |
#3
|
|||
|
|||
Trying ADO... vb to link to closed file
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 "Bill Manville" wrote in message ... Dave B wrote: 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 |
#4
|
|||
|
|||
Trying ADO... vb to link to closed file
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 "Dave B" Not.Available schreef in bericht ... 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 "Bill Manville" wrote in message ... Dave B wrote: 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 |
Thread Tools | |
Display Modes | |
|
|