Read many cell values from closed workbook

dsxtreme21

New Member
Joined
Apr 15, 2009
Messages
9
Hi all--

I've recently discovered the usefulness of VB in excel and have managed to write some basic macros that enhance many of my workbooks. BUT, I've bumped into a problem I can't seem to solve. Please help!

I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory. I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?

I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Problem is, I'm so new that I don't even know where to begin pasting the code (objects vs. modules, etc) in the VBA editor.

thanks in advance to anyone who'll spend the time walking me through this!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory.

I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?

I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Greetings,

The link you provided shows an example of using ExecuteExcel4Macro to grab the value of a cell from a closed wb. While this is a handy feature indeed, when looping thru a bigger range, you may find this a bit slow. In essence, while snatching info from a closed wb can be faster than opening the wb, the wb is still loaded into memory ea time it is being read from. As you can imagine, calling the function 2500 times could slow things a bit.

Anyways, ADO would be one way to go, but I am awfully spotty on that, so how about we look at a couple of simple solutions:

These are based upon the two workbooks being in the same folder. "New Microsoft Excel Worksheet.xls" gets the code and is the destination wb, and "New Microsoft Excel Worksheet (2).xls" is the source. We'll get the values from "Sheet1" cells B2:I40, and stick these in "Sheet3" of the destination wb.

To simply create an array formula, then overwrite the range w/just the values:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> GetRangeOfVals()<br><SPAN style="color:#00007F">Dim</SPAN> wksDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> rngDest <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> strFull <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ThisWorkbook.Worksheets("Sheet3")<br>    <SPAN style="color:#00007F">Set</SPAN> rngDest = wksDest.Range("B2:I40")<br>    <br>    strPath = ThisWorkbook.Path & Application.PathSeparator<br>    strFull = "='" & strPath & "[New Microsoft Excel Worksheet (2).xls]Sheet1'!R2C2:R40C9"<br>    <br>    rngDest.FormulaArray = strFull<br>    rngDest.Value = rngDest.Value<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Or, is you don't mind programatically opening the wb momentarily, we could use Evaluate.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> EvalRangeOfVals()<br><SPAN style="color:#00007F">Dim</SPAN> wksDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> rngDest <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> wbSource <SPAN style="color:#00007F">As</SPAN> Workbook<br><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ThisWorkbook.Worksheets("Sheet3")<br>    <SPAN style="color:#00007F">Set</SPAN> rngDest = wksDest.Range("B2:I40")<br>    <br>    strPath = ThisWorkbook.Path & Application.PathSeparator<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wbSource = Workbooks.Open(strPath & "New Microsoft Excel Worksheet (2).xls", , <SPAN style="color:#00007F">True</SPAN>)<br>    <br>    rngDest.Value = Evaluate("='" & strPath & "[New Microsoft Excel Worksheet (2).xls]Sheet1'!B2:I40").Value<br>    <br>    wbSource.Close <SPAN style="color:#00007F">False</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope this helps,

Mark
 
Last edited:

dsxtreme21

New Member
Joined
Apr 15, 2009
Messages
9
Mark,

Thanks! It seems that this is exactly what I'm looking for and quite fast.

A few questions:

1. I seem to get an error when the source workbook is open too. Why and what, if anything, can be done to prevent this?

2. How exactly to I control which cells are retrieved and where they are ultimately placed? Do both ranges have to be the same?

Again, thank you for your time!

-Dan
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
1. I seem to get an error when the source workbook is open too. Why and what, if anything, can be done to prevent this?

I imagine you are talking about the first code, not the second... Not tested, but this should work. Basically if the workbook is open, the formula would not include the path.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetRangeOfVals()<br><SPAN style="color:#00007F">Dim</SPAN> wksDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> rngDest <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> strFull <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#00007F">Const</SPAN> WB_NAME <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "New Microsoft Excel Worksheet (2).xls"<br><br><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ThisWorkbook.Worksheets("Sheet3")<br>    <SPAN style="color:#00007F">Set</SPAN> rngDest = wksDest.Range("B2:I40")<br>    <br>    strPath = ThisWorkbook.Path & Application.PathSeparator<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Workbook_IsOpen(WB_NAME) <SPAN style="color:#00007F">Then</SPAN><br>        strFull = "[" & WB_NAME & "]Sheet1'!R2C2:R40C9"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        strFull = "='" & strPath & "[" & WB_NAME & "]Sheet1'!R2C2:R40C9"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    rngDest.FormulaArray = strFull<br>    rngDest.Value = rngDest.Value<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> Workbook_IsOpen(wbName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks(wbName)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> Workbook_IsOpen = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Again, not tested, so shout out if it goobers...

2. How exactly to I control which cells are retrieved and where they are ultimately placed? Do both ranges have to be the same?


In either of these we are specifying a source range. The Evaluate method returns an array of values, but requires the source wb to be open. The first sub first plasters in an array formula and just overwrites the range with the values. Think of it like Copy/PasteSPecial(values).

If be the "same" you mean does the source and destination have to be on the same place in a sheet - no. If you were getting A1:B10, it could go to D11:E20 or wherever.

The source and destination ranges must be equally sized of course.

Mark
 
Last edited:

dsxtreme21

New Member
Joined
Apr 15, 2009
Messages
9

ADVERTISEMENT

Mark,

hmmm...I'm still struggling grabbing the right cells. I want to "copy" E9:K5000 and paste to C10:H5001. Could you suggest the values I'd need to change in the code?
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Give me:
  1. The source sheet name
  2. The soource workbook name
  3. the destination sheet name
  4. the destination workbbok name
  5. IF they are to be in different folders, the full path to the source wb
Mark
 
Last edited:

dsxtreme21

New Member
Joined
Apr 15, 2009
Messages
9

ADVERTISEMENT

Here is the code, is there somthing else you'd like me to give you? Thank you so much!



Option Explicit

Sub GetRangeOfVals()
Dim wksDest As Worksheet
Dim rngDest As Range
Dim strPath As String
Dim strFull As String

Set wksDest = ThisWorkbook.Worksheets("new revenue")
Set rngDest = wksDest.Range("C10:H5001")

strPath = ThisWorkbook.path & Application.PathSeparator
strFull = "='" & strPath & "[Complete Transaction History - OPM - D. Stephens N. Osborn - 2009.xls]Revenue Detail'!R2C2:R40C9"

rngDest.FormulaArray = strFull
rngDest.Value = rngDest.Value

End Sub
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Okay, I see a problem. Your original post states a source range of 5 columns by 5000 rows.

Your destination range of C10:H5001 is 6 columns by 4992 rows.

Your source range of R2C2:R40C9 is 8 columns by 39 rows.

In R1C1 reference, the 'R' refers to the row (ie R1 is row 1) and the C is the column (ie R1C1:R10C3 would be the same as A1:C10)

That may be enough, but if not, just list the actual range from Revenue Detail in A1 style, for instance: "C2:H5001 is the source range"

Mark
 

dsxtreme21

New Member
Joined
Apr 15, 2009
Messages
9
Source Sheet Name: Revenue Detail
Source Workbook Name: Complete Transaction History - OPM - D. Stephens N. Osborn - 2009
Destination Sheet Name: new revenue
Destination Workbook Name: Monthly Summaries - OPM - D. Stephens N. Osborn - 2009
5. Always same folder
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Source Sheet Name: Revenue Detail
Source Workbook Name: Complete Transaction History - OPM - D. Stephens N. Osborn - 2009
Destination Sheet Name: new revenue
Destination Workbook Name: Monthly Summaries - OPM - D. Stephens N. Osborn - 2009
5. Always same folder

Thank you, but I still need an accurate description of the source range, like "the source range is C2:H5001" or whatever it actually is. :)

Mark
 

Watch MrExcel Video

Forum statistics

Threads
1,122,186
Messages
5,594,745
Members
413,929
Latest member
Hypatia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top